Error 3988: Conversion from collation utf8mb4_general_ci into latin1_swedish_ci impossible for parameter

[justin-wilxite]

Issue description

I'm encountering this error when attempting to insert or update a latin1 column with a text parameter that is utf8 encoded and which contains unsupported characters.

Error 3988: Conversion from collation utf8mb4_general_ci into latin1_swedish_ci impossible for parameter

If I run the same query from the mysql command line, or using the PHP PDO client, there is no error and the unsupported characters are replaced with ?s. I'd prefer to get that behaviour instead of the error.

This is specific to MySQL 8.

Example code

db, err := sql.Open(dbDriver, dbUser+":"+dbPass+"@tcp("+dbHost+")/"+dbName+"?parseTime=true")
_, err = db.Exec("UPDATE notes SET text=? WHERE id=72", "cżcż")
if err != nil {
	log.Fatal(err)
}

Configuration

Driver version (or git SHA): 1.6.0

Go version: 1.17.5

Server version: MySQL 8.0.23

Server OS: CentOS

[methane]

If I run the same query from the mysql command line, or using the PHP PDO client, there is no error and the unsupported characters are replaced with ?s.

Failed to reproduce.

mysql> create table t2 (s varchar(255) character set latin1);
Query OK, 0 rows affected (0.01 sec)

mysql> update t2 set s="cżcż" where i=1;
ERROR 1366 (HY000): Incorrect string value: '\xC5\xBCc\xC5\xBC' for column 's' at row 1

[justin-wilxite]

If I run the same query from the mysql command line, or using the PHP PDO client, there is no error and the unsupported characters are replaced with ?s.

Failed to reproduce.

mysql> create table t2 (s varchar(255) character set latin1);
Query OK, 0 rows affected (0.01 sec)

mysql> update t2 set s="cżcż" where i=1;
ERROR 1366 (HY000): Incorrect string value: '\xC5\xBCc\xC5\xBC' for column 's' at row 1

@methane You will get that error with strict mode enabled, but when it is disabled it will just produce a warning instead and you can reproduce the behaviour I mentioned.

mysql> update t2 set s="cżcż";
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 2

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (t2) without an explicit primary key with pxc_strict_mode = PERMISSIVE |
| Warning | 1366 | Incorrect string value: '\xC5\xBCc\xC5\xBC' for column 's' at row 1                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| s    |
+------+
| c?c? |
+------+
1 row in set (0.00 sec)

My issue is that even with strict mode disabled, I am getting an error - specifically, error 3988 - when I do an insert or update query with the Go sql driver.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict

[methane]

I just used the default MySQL. That's why "complete steps to reproduce" is important.

You should provide whole step, and confirm the steps can reproduce your problem before reporting issue.

Now you said "even with strict mode disabled", but you didn't say the step to disable the "strict mode".

I can not confirm the issue. I don't want to waste my time by trying to reproduce your issue without enough information.

[methane]

I will reopen this issue if you can provide the complete steps to reproduce with Docker MySQL.

[shubham-zs]

Currently, i am also facing this type of issue, but in my case its giving "Error 3988: Conversion from collation utf8_general_ci into utf8mb4_0900_ai_ci impossible for parameter". I am working on Golang and db is MySQL. Can anybody help?

[kouhuang]

Hi, do you fix it now?i am also facing this type of issue

[shubham-zs]

Hi @kouhuang, I kept the encoding as it is ut8mb4 only and in my case i had to store emojis along with the text in the database so i used base64 encoding and decoding and it worked. I was able to store the emojis in that way.

[kvirund]

Do you have any clues on the root cause? I am getting the same error and it is super weird. I can't really understand the actual condition when it is failing. I have a Java code that connects to a MySQL server and uses batch execution of multiple INSERT statements. And that is what is failing:

Caused by: java.sql.BatchUpdateException: Conversion from collation utf8mb4_0900_ai_ci into latin1_swedish_ci impossible for parameter
  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
  at com.mysql.cj.util.Util.getInstance(Util.java:167)
  at com.mysql.cj.util.Util.getInstance(Util.java:174)
  at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
  at com.mysql.cj.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:385)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435)
  at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
  ... 16 more
Caused by: java.sql.SQLException: Conversion from collation utf8mb4_0900_ai_ci into latin1_swedish_ci impossible for parameter
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:634)
  at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:418)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
  at com.mysql.cj.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:357)
  ... 18 more

I thought it is something with the data I am inserting but my tests didn't prove that: the error was happening when I was inserting 10 rows. So I changed my program to insert only the first row, then the first two rows, and so on. It failed when I tried to insert the first 4 rows. So I thought that is something with the data in the fourth row. But then I tried to insert just that fourth row by itself and it went through just fine. I.e. it is not about the data in the fourth row but something else. And I can't understand what is that "else".

[kvirund]

The INSERT statement I am using:

INSERT INTO ALL_TYPES_SMALL_VALUES_TGT_8DBFCACB_66F7_41EB_B611 (column_bit1, column_bit12, column_integer, column_integer_unsigned, column_integer_zerofill, column_smallint, column_smallint_unsigned, column_smallint_zerofill, column_tinyint, column_tinyint_unsigned, column_tinyint_zerofill, column_mediumint, column_mediumint_unsigned, column_mediumint_zerofill, column_bigint, column_bigint_unsigned, column_bigint_zerofill, column_decimal, column_decimal_unsigned, column_decimal_zerofill, column_float, column_float_unsigned, column_float_zerofill, column_double, column_double_unsigned, column_double_zerofill, column_date, column_datetime, column_timestamp, column_time, column_year, column_char, column_national_char, column_binary, `column_varchar(3887)`, `column_varbinary(8814)`, column_tinyblob, column_mediumblob, column_longblob, column_blob, column_tinytext, column_mediumtext, column_longtext, column_text, column_enum, column_set, column_geometry, column_point, column_linestring, column_polygon, column_multipoint, column_multilinestring, column_multipolygon, column_geometrycollection, column_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), ST_GeomFromText(?), CONVERT(? USING utf8))

I thought it might be because of that CONVERT(? USING utf8) for JSON column but I tested it alone and it works fine.

[methane]

This is not Q&A forum. Do not ask about error happened in your environment.
Your question is not relating to this project, and even to Go.

[kvirund]

I am not asking about the error that happened in my environment. I am asking about the error reported in this issue. And with the hope that it may help with identifying the root cause I provided details of how it happened in my case. So far it doesn't seem like the problem was solved.

My issue happened with MySQL which is, as far as I understand, this project is about. The error comes from the database but not from the Go itself. So I can't see a reason why my environment should matter in that case.

[methane]

Root cause is obvious: wrong configuration.
And it is not relating to this project at all.

[methane]

See also

• https://github.com/go-sql-driver/mysql#collation
• https://dev.mysql.com/doc/refman/8.0/en/charset.html

[Manask322]

there can also be charset mismatch between client config and mysql, make sure client is using same charset while connecting to DB

[methane]

there can also be charset mismatch between client config and mysql, make sure client is using same charset while connecting to DB

It is what I meant. User need to specify correct collation.

Original issue that @justin-wilxite reported is: database used latin1 but client used utf8.
They need to use latin1 for connection, or alter column/table/database to use utf8.

Issue reported by @kvirund is same.

Issue reported by @shubham-zs is bit different. Connection encoding was utf8mb3_general_ci too.
But server default collation is utf8mb4_ by default for compatibility. But his database used utf8mb4_0900_ai_ci that is MySQL 8.0 default.

In all cases, chose the right connection collation fix the issue.

FWIW, next go-mysql (v1.8.0) will make configuration bit easier, especially for @shubham-zs case.

User don't need to know much about the default collation of server. They need to know only the default charset of the database. They can specify only charset like charset=utf8mb4. go-mysql v1.8.0 will send SET NAMES utf8mb4 automatically for every new connection. MySQL server will chose default collation of the charset for the connection collation.

See https://github.com/go-sql-driver/mysql#charset and #745 (comment) for detail.

Additionally, see links I wrote in #1298 (comment). It is very important.

[methane]

FWIW, next go-mysql (v1.8.0) will make configuration bit easier, especially for @shubham-zs case.

Oh, I was wrong. You can specify only charset even with current v1.7. It was not recommended in the README, but it was not bad and I recommend it for many users who don't care about collation. They should use server's default collation of the charset and specify only charset=utf8mb4 do it exactly.

What changed in v1.8 is setting both of charset and collation. go-mysql v1.8 will send SET NAMES charset COLLATE collation automatically for every connection.