Collation Drift: The Silent Index Killer

TL;DR

A MySQL schema that lived through the 5.7-to-8.0 upgrade carries two eras of collation: old VARCHAR columns on utf8mb4_general_ci, newer ones on utf8mb4_0900_ai_ci. Join across that seam and MySQL either refuses outright or silently wraps one column in a per-row charset conversion that takes its index out of play. Converging fixes it, but the same conversion changes which rows count as equal, so the migration itself can fail on duplicate keys.

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 co…