Illegal mix of collations

What this means

Error 1267 (SQLSTATE HY000) is raised when a comparison or concatenation combines string values with incompatible collations. Collation governs how strings are compared and sorted; mixing collations (e.g., utf8mb4_general_ci vs utf8mb4_unicode_ci) in one expression is ambiguous.

Why it happens

1. 1Columns from two tables have the same character set but different collations
2. 2A literal string has a different collation than the column it is compared against
3. 3A function or stored procedure returns a string with a different collation than expected
4. 4Database, table, and column collations are inconsistent (created at different times with different defaults)

How to reproduce

Joining two tables whose columns have different collations.

SELECT * FROM users u
JOIN sessions s ON u.username = s.username;
-- users.username: utf8mb4_general_ci
-- sessions.username: utf8mb4_unicode_ci

Fix 1: Use COLLATE to coerce one side of the comparison

As a quick fix or when you cannot alter the table schema.

SELECT * FROM users u
JOIN sessions s ON u.username = s.username COLLATE utf8mb4_unicode_ci;

Fix 2: Standardise collation across both columns

As the permanent fix — align collations at the schema level.

ALTER TABLE sessions MODIFY username VARCHAR(255)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Fix 3: Set a consistent database default collation

When creating a new database or when all tables can be migrated.

ALTER DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

What not to do

Version notes

Sources

Confidence assessment

See also