Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses to compare two strings: illegal mix of collations

ErrorERROR 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.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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');
  2. 2Match what you see, then jump to its fix

    1. 1Both 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. 2One table reports latin1 (or another set) while the rest report utf8mb4.A table or whole database still uses an old character setFix
    3. 3One 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.