MySQL refuses to compare two strings: illegal mix of collations
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='Short answer
Error 1267 means two string values in one comparison have different collations and MySQL cannot pick a winner. Find the two collations in the error text. Then add a COLLATE clause to one side for a quick fix, or convert the mismatched column so both columns share one collation for a lasting fix.
Fix it fast
1Find out which cause you have
The error names both collations and where each came from (IMPLICIT is a column, COERCIBLE is a literal). Then list the real collation of every column in the query.
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL AND COLUMN_NAME IN ('name');2Match what you see, then jump to its fix
- 1
Both sides of the error say IMPLICIT. Typical after joining a table created on an older server with one created on MySQL 8.Two columns have different collationsFix - 2
One table reports latin1 (or another set) while the rest report utf8mb4.A table or whole database still uses an old character setFix - 3
One side of the error says COERCIBLE or SYSCONST, or the failing query compares a column to a string passed by the app, a stored procedure variable or CONCAT().A literal, variable or function result uses the connection collationFix
- 1
What does the error look like?
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
Cause 1 of 3
Two columns have different collations
1Check
Both columns have the same coercibility (2), both are Unicode, so MySQL stops with an error. Look at the two collation names from the first step.
SHOW FULL COLUMNS FROM orders;2Fix
Quick fix: put COLLATE on one side of the comparison. Lasting fix: convert the column to the collation the rest of your schema uses. Restate NOT NULL, DEFAULT and COMMENT in MODIFY, or they are dropped.
-- quick, per query
SELECT * FROM a JOIN b ON a.name COLLATE utf8mb4_0900_ai_ci = b.name;
-- lasting, per column
ALTER TABLE a MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;3Verify
Run the original query again. Indexes on a column wrapped in COLLATE may not be used, so check EXPLAIN.
EXPLAIN SELECT * FROM a JOIN b ON a.name = b.name;Confirmed by dev.mysql.com
Cause 2 of 3
A table or whole database still uses an old character set
1Check
List the table's character set and collation.
SELECT TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE();2Fix
Convert the table. CONVERT TO changes every character column and converts the stored values, so use it only when the data really is in the old character set. Take a backup first.
ALTER TABLE legacy_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;3Verify
Check the table collation again.
SHOW CREATE TABLE legacy_users;Confirmed by dev.mysql.com
Cause 3 of 3
A literal, variable or function result uses the connection collation
1Check
A literal takes the connection collation, which comes from SET NAMES. A system constant such as USER() has its own. Check what the session is using.
SELECT @@character_set_connection, @@collation_connection;2Fix
Make the client connection use the same character set as your tables, or add COLLATE to the literal.
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';
-- or per value
SELECT * FROM a WHERE name = 'Zoe' COLLATE utf8mb4_0900_ai_ci;3Verify
Repeat the failing query from the same connection.
SELECT COERCIBILITY('A'), COERCIBILITY(VERSION());Confirmed by dev.mysql.com
How do I stop it coming back?
- Pick one character set and collation for the schema and set it when you create each database.
- After a server upgrade, compare the INFORMATION_SCHEMA collations of old and new tables before joining them.
- Configure the app driver to send the same character set as the tables.
- Avoid COLLATE in WHERE on big tables as a permanent fix; convert the column instead.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix