MySQL — Illegal mix of collations in a JOIN or WHERE

Fix MySQL Illegal mix of collations errors between utf8mb4_general_ci and utf8mb4_0900_ai_ci, understand when the error fires versus silent index loss, and unify charsets.

intermediatedebuggingmysqlcollationcharset

Symptoms

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) on JOIN or comparison
The query worked before a table was recreated or a column added with a different default collation
Other queries silently stop using indexes on the mixed comparison, slowing without erroring

Possible causes

MySQL 8.0 defaults to utf8mb4_0900_ai_ci while older tables and imported schemas use utf8mb4_general_ci
A new column inherited the server default collation while the rest of the table uses another
String literals or connection charset convert with a coercibility that conflicts with column collations
Temporary tables, views, or generated columns pick a collation different from their sources

Diagnosis steps

Inspect the collations of both sides

mysql -e "SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema = 'shop';" | grep -E "utf8"

Interpretation

Side-by-side collations for the joined or compared columns reveal the exact pair that conflicts.

Check server and connection defaults

mysql -e "SHOW VARIABLES LIKE 'collation%'; SHOW VARIABLES LIKE 'character_set%';"

Interpretation

collation_server and collation_connection explain where new objects got their collation; mismatches here seed future errors.

Confirm index usability on the join

mysql -e "EXPLAIN SELECT * FROM a JOIN b ON a.k = b.k;"

Interpretation

A collation-converting comparison shows as range or scan instead of eq_ref/ref — the silent performance variant of the same disease.

Root cause

Comparing or joining strings with incompatible collations has no defined ordering, so MySQL raises ERROR 1267. When collations are compatible enough to convert, the comparison may instead disable index use on one side, making the same misconfiguration present either as an error or as a silent slowdown.

Fix

Unify the schema: convert mismatched tables and columns to one collation (CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci), align the connection collation, and set explicit COLLATE in literals only as a transitional bridge. Standardize migrations to declare charset and collation explicitly.

Risk note

Changing collation rewrites and locks tables and can change unique-key semantics (case or accent insensitivity). Test sort order and uniqueness expectations in staging.

Verify the fix

After conversion, information_schema shows one collation across the schema, the failing query succeeds, and EXPLAIN regains eq_ref or ref on the join.

Symptom walkthrough

The error prints the colliding pair:

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

This exact pair is the MySQL 8.0 upgrade story: new default 0900_ai_ci meets tables created under 5.7 with general_ci. The dangerous sibling is silence: when collations can convert, the join still runs but the index on one side becomes unusable — EXPLAIN shows a range or full scan where eq_ref used to be. Audit for both presentations.

Fix in practice

Convert to one collation schema-wide, per table:

ALTER TABLE orders
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Align the connection for literals and prepared statements:

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Transitional bridge when conversion must wait (single query fix):

SELECT * FROM a JOIN b
  ON a.k = b.k COLLATE utf8mb4_general_ci;  -- explicit, indexed side kept

And make migrations state charset and collation explicitly, so new tables never inherit whatever the server default happens to be this year.