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

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

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 16 (8 by maintainers)

Most upvoted comments

See also

methane on Apr 18, 2023

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 https://github.com/go-sql-driver/mysql/issues/745#issuecomment-1563166217 for detail.

Additionally, see links I wrote in https://github.com/go-sql-driver/mysql/issues/1298#issuecomment-1512343162. It is very important.

methane on Jun 16, 2023

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

methane on Apr 18, 2023