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