A routine 8.0 upgrade goes fine for a week. Then a report that has run every morning for three years starts throwing:

1
2
3

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

The join is orders.customer_ref = customers.external_id, two VARCHAR(64) columns that compared fine on 5.7. Whoever is on call finds the Stack Overflow answer in about ninety seconds: append COLLATE utf8mb4_0900_ai_ci to one side, the error clears, the report runs, the incident closes. What nobody clocks is that the query that used to finish in 40ms now takes nine seconds, because the COLLATE clause wrapped customers.external_id in a conversion and the index on it is out of play. The loud failure got traded for a quiet one.

That COLLATE clause is the obvious fix and the trap: it clears the error by forcing the conversion that kills the index. Where the two collations came from, and why neither is wrong, is the rest of the story.

Two eras of collation, and nobody broke a rule

Charset is the encoding, which bytes stand for which characters. Collation is the comparison and sort rules layered on top: whether a equals A, whether é equals e, what order strings come back in. Every text column carries both, inherited from its table, which inherits from the database, which falls back to the server default when nobody said otherwise.

That default moved. MySQL 8.0.1 changed collation_server and collation_database from latin1_swedish_ci to utf8mb4_0900_ai_ci (per the 8.0.1 release notes, April 2017), and the default collation for the utf8mb4 charset itself changed from utf8mb4_general_ci, the 5.7 default, to utf8mb4_0900_ai_ci. So a table created with CHARSET=utf8mb4 and no explicit collation got utf8mb4_general_ci in 2021 and utf8mb4_0900_ai_ci in 2024. Same DDL, same intent, different result, because a version boundary sat between the two runs. The drift is the absence of a decision, resolved twice by two different defaults.

It is sitting in plain sight in the catalog:

1
2
3
4
5
6

SELECT CHARACTER_SET_NAME, COLLATION_NAME, COUNT(*) AS cols
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
  AND DATA_TYPE IN ('varchar', 'char', 'text', 'enum')
GROUP BY CHARACTER_SET_NAME, COLLATION_NAME
ORDER BY cols DESC;

Two rows back where you expected one is the whole diagnosis. COLLATION_NAME is a column nobody reads, human or model. An assistant generating the join reads COLUMN_NAME and DATA_TYPE off the same view and never glances at the collation; the mismatch is machine-readable and goes unread on both ends.

The conversion is a function on your indexed column

When two columns share a charset and differ only in collation, and both take their collation implicitly from the schema, MySQL refuses to pick a winner. It raises Illegal mix of collations and stops. Loud, obstructive, honest.

When the charsets differ, though, MySQL does not stop. Picture a utf8mb4 column on one side and a latin1 or utf8mb3 column that predates the utf8mb4 migration on the other. utf8mb4 is a superset of both, so MySQL converts the narrower side up to utf8mb4 to make the comparison legal, and does it without a word. That conversion is convert(customers.external_id using utf8mb4) wrapped around the column and evaluated per row. A function around an indexed column is the textbook non-SARGable predicate: the B-tree is sorted by the raw latin1 bytes, the planner needs the utf8mb4 form of every value to compare, and it cannot get that from the index without computing it first. So it computes it, row by row, across the table. No error. Clean result count. Two hundred times the latency.

The COLLATE band-aid from the opener reproduces this on purpose. ... COLLATE utf8mb4_0900_ai_ci on customers.external_id is a function on the column, and whichever side you collate is the side that loses its index. The loud version got silenced by hand-writing the silent one.

The connection carries a collation too

The seam isn’t only in the schema. Every session has its own collation_connection, set by the driver at connect time, and it drifts the same way. Connector/J is the usual offender: before 8.0.13, in 2018, a connection asking for utf8mb4 came up on utf8mb4_general_ci even against a server default of utf8mb4_0900_ai_ci, so the session and the columns disagreed straight out of the pool.

The same coercibility rule spares your indexed lookups: a bound ? parameter yields to the column, so WHERE col = ? keeps its index whatever the connection is set to. Variables are where it bites. A session variable or a stored-procedure local carries the connection collation at column-level coercibility, so comparing one to a 0900_ai_ci column throws the same 1267 from the opener, now out of application code with no ALTER in sight. That one is a driver fix, not a migration: pin connectionCollation to match the schema, or upgrade past the versions that guessed.

Converging changes what “equal” means

The durable fix is to converge: one charset (utf8mb4, never legacy utf8/utf8mb3) and one collation per database, set at the database level so every new table inherits it instead of guessing. The modern MySQL 8 default utf8mb4_0900_ai_ci is the natural pick unless you genuinely want accent- and case-sensitive comparison, in which case utf8mb4_0900_as_cs is the sibling to standardize on instead. Matching collation on both sides of every join is the thing that keeps the index in play. That is the point of converging, not the tidiness of a uniform catalog.

The convergence itself is ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, which rewrites the whole table. On anything large that’s gh-ost or pt-online-schema-change territory, not a maintenance-window ALTER. The rewrite is the cheap part.

The expensive part is that collation defines equality, and changing the collation changes which rows are equal. utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive: 'café' = 'cafe', 'Smith' = 'SMITH'. Under utf8mb4_bin or an _as_cs collation those are distinct values. Converge a column that carried a UNIQUE index under the stricter collation, and any two rows that were distinct only by accent or case now collide.

Postgres has the same class of bug wearing a different mask, and there the index does not slow down, it goes wrong. Postgres leans on the operating system’s libc locale for text collation, so its sort order is only as stable as the C library underneath it. glibc 2.28 (shipped 2018, in RHEL 8 and Debian 10) rewrote collation for nearly every locale, and Crunchy Data documented the fallout in 2019: a B-tree built under the old ordering becomes silently corrupt under the new one, so WHERE misses rows the index insists aren’t there and UNIQUE quietly stops being unique. The trigger is an OS upgrade nobody connected to the database. Same “nobody broke a rule” shape, worse blast radius. The modern defense is the ICU collation provider with versioned collations, which at least warns you when the version underneath drifts.

Run the information_schema query against your busiest database before you need to. Two rows where you expected one is a latency cliff someone eventually hits at 40x, files under “the database is slow,” and never traces back to a server default that changed in 2017.