MySQL 'Illegal mix of collations' (Error 1267): The Fallout of a Half-Finished utf8mb4 Migration

The query worked in staging. It works when you run it against one customer. Then in production, on a specific join between two specific tables, it dies with an error that reads like the database is refusing on principle: an illegal mix of collations. Not a slow query, not a wrong result, a hard failure. And it only happens sometimes, on some joins, which is the part that makes it feel haunted.

It is not haunted. Somewhere in your history, someone started migrating the database to utf8mb4 so it could store emoji and the full range of Unicode, and the migration was never finished. Some tables and columns got converted. Others still sit on the old utf8 (which is really utf8mb3) or latin1. The moment a query compares a converted column against an unconverted one, MySQL cannot decide which collation rules apply, and rather than guess it stops.

The quick fix everyone reaches for, casting one side of the comparison with COLLATE, makes the error go away for that one query. It also quietly tells you that your schema is inconsistent, and that inconsistency is going to keep surfacing as errors and, worse, as silently unused indexes. This article covers both: the one-line unblock so you can ship, and the actual repair so it stops coming back.

The exact error

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
and (utf8mb3_general_ci,IMPLICIT) for operation '='

Read the two parenthesised parts, because they are the diagnosis handed to you for free. The first is one operand's collation, the second is the other's. Here one side is utf8mb4_unicode_ci and the other is utf8mb3_general_ci. The word IMPLICIT means the collation came from the column definition, not from an explicit COLLATE clause. So the message is literally telling you: these two columns are defined with incompatible collations, and the = in your join is where they met.

Why MySQL refuses instead of just sorting oddly

A charset is the set of characters a column can store. A collation is the set of rules for comparing and sorting them: whether a equals A, how accented characters rank, which byte sequences count as equal. Every string value in MySQL carries both.

When you compare two strings, MySQL has to pick one collation to do the comparison under, because the answer to "is X equal to Y" genuinely differs between collations. It uses a coercibility system to decide which operand's collation wins. A literal you type is more coercible than a column; a column with an explicit COLLATE is less coercible than one without. When both operands are columns with different implicit collations and neither is more coercible than the other, there is no principled way to choose. MySQL will not silently pick one and risk giving you wrong equality results, so it raises 1267 and stops.

This is why it feels intermittent. The error is a property of the pair of columns in a comparison, not of any one table. Table A joined to table B fails; table A joined to table C, which happens to share A's collation, is fine. Same query shape, different tables, different outcome. Nothing is random; you are just hitting different pairs.

Step 1: Unblock the query so you can ship

You have a production query failing right now, and the full schema conversion is not a five-minute job. Force both sides of the comparison to a common collation with an explicit COLLATE:

SELECT u.id, c.name
FROM users u
JOIN customers c
  ON u.email COLLATE utf8mb4_unicode_ci = c.email COLLATE utf8mb4_unicode_ci;

The explicit COLLATE makes both operands agree, so 1267 goes away. Understand exactly what you have done, though: you have papered over one crack. There is a real cost, covered below, and this is a bandage, not the repair. If you cannot change the query text (an ORM generates it), you can sometimes force it at the connection level, but that is fragile; the durable answer is fixing the schema.

Step 2: Find every table and column that is out of line

Before you convert anything, get the full picture. This query lists every column whose collation is not your target, so you know the real size of the job:

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 collation_name NOT LIKE 'utf8mb4%'
ORDER BY table_name, ordinal_position;

And the table-level defaults, which matter because a table's default collation is what new columns inherit:

SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_collation NOT LIKE 'utf8mb4%';

Now you know whether this is three legacy tables or the whole database. That determines whether you can do it in one maintenance window or need to plan.

Step 3: Convert the schema to one collation

Pick a single target and apply it everywhere. On MySQL 8 the modern default is utf8mb4_0900_ai_ci; on MariaDB, utf8mb4_unicode_ci is the safe, widely compatible choice. Whichever you pick, use it for every table. Convert a table and all its string columns in one statement:

ALTER TABLE customers
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CONVERT TO CHARACTER SET changes both the table default and every character column in it, and it re-encodes the stored data, which is what you want when moving off latin1. Two cautions that are easy to learn the hard way:

• This rewrites the whole table and takes a lock for the duration on a plain ALTER. On a large table in production, run it through an online schema-change tool or during a maintenance window, the same discipline any big ALTER needs. My note on migrations that run twice applies here: write the conversion so re-running it is safe.
• utf8mb4 uses up to 4 bytes per character, so an indexed VARCHAR column can exceed an index length limit that it fit under in utf8mb3. If a conversion fails on index length, shorten the indexed prefix or the column, do not abandon the conversion.

Step 4: Fix the defaults so it cannot happen again

Converting existing tables is not enough. The next table someone creates will inherit the server or database default, and if that default is still latin1 or utf8mb3, you have reopened the door. Set the database default:

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And set the server default in the config so new databases and connections agree, then restart:

# my.cnf, under [mysqld]
character-set-server = utf8mb4
collation-server    = utf8mb4_unicode_ci

The connection charset matters too. A client that connects with latin1 reintroduces a mismatch at query time even against a perfectly converted schema, because string literals you send arrive tagged with the connection collation. Make sure your application's driver connects with utf8mb4. If you are running ERPNext or another Frappe app, this is the same reason the framework insists on utf8mb4 throughout; a stray latin1 connection setting causes exactly this class of bug.

Verifying it is actually fixed

Re-run the discovery query from step 2. When the schema is consistent it returns zero rows:

SELECT COUNT(*) AS stragglers
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND collation_name IS NOT NULL
  AND collation_name NOT LIKE 'utf8mb4%';

You want stragglers = 0. Then run the join that used to fail without the explicit COLLATE casts, because the whole point was to stop needing them:

SELECT u.id, c.name
FROM users u
JOIN customers c ON u.email = c.email
LIMIT 1;

If that returns rows with no 1267, the columns now share a collation on their own. Finally, confirm the join is actually using its index and not silently falling back to a scan:

EXPLAIN SELECT u.id, c.name
FROM users u JOIN customers c ON u.email = c.email;

Look for the join column showing a ref access using the email index rather than ALL. That last check is the one people skip, and it is the one that catches the silent version of this bug.

What people get wrong

Treating the COLLATE cast as the fix. It is an unblock, not a fix. Every casted comparison is a place your schema is still inconsistent, and there is a hidden performance cost: casting a column with COLLATE in a join or WHERE can prevent MySQL from using the index on that column, because the indexed values are stored under a different collation than the one you are comparing under. You can turn a fast indexed lookup into a full table scan while congratulating yourself for fixing the error. The scan does not error, so you may not notice until the table is large. This is the same trap as the slow SELECT that looks like a disk problem: the query "works", it is just secretly doing far more work than it should.

Converting to utf8 instead of utf8mb4. MySQL's utf8 is an alias for utf8mb3, which stores at most 3 bytes per character and cannot hold emoji or a large part of Unicode. If you standardise on utf8 you have merely made the mismatch consistent while keeping the original storage limitation. Always target utf8mb4.

Mixing utf8mb4_general_ci and utf8mb4_unicode_ci. Both are utf8mb4, so they will not error the same way, but they are different collations and comparing them can still raise 1267 in some operations, and they sort differently. Pick one collation, not just one charset, and use it everywhere.

Converting the tables but forgetting the connection. A flawless schema plus a client that connects as latin1 gives you the error back at runtime, because your literals and parameters arrive with the wrong collation. The fix is not complete until the application's database connection also speaks utf8mb4.

When it is still broken

• A generated or virtual column keeps the old collation. CONVERT TO CHARACTER SET can miss columns defined with an explicit collation in their DDL. Re-run the discovery query; if a column survived, alter it explicitly with MODIFY column_name VARCHAR(...) COLLATE utf8mb4_unicode_ci.
• A view still fails. Views bake in the collation of their defining query at creation time. After converting the underlying tables, drop and recreate the view so it picks up the new collations.
• Stored procedures or functions raise it. Routine parameters and local variables have their own collation from the routine definition. Recreate the routine after the schema conversion, or add explicit COLLATE in the routine body.
• It only fails against one replica. Check that the replica's server-level collation-server and connection defaults match the primary. A replica configured with a different default can produce the error on the same data, which is really a config drift problem, not a data problem.

The through-line: 1267 is not a query bug, it is your schema telling you it is inconsistent, at the one spot where the inconsistency became impossible to ignore. Cast to ship today, but standardise the whole database on utf8mb4 and one collation, or you will meet this error again on the next join you did not test.