EC
1267
MariaDBERRORNotableCharacter SetsHIGH confidence

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.

trigger — this will error
trigger — this will error
SELECT * FROM users u
JOIN sessions s ON u.username = s.username;
-- users.username: utf8mb4_general_ci
-- sessions.username: utf8mb4_unicode_ci

expected output

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

Fix 1

Use COLLATE to coerce one side of the comparison

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

Use COLLATE to coerce one side of the comparison
SELECT * FROM users u
JOIN sessions s ON u.username = s.username COLLATE utf8mb4_unicode_ci;

Why this works

COLLATE forces the expression to be evaluated using the specified collation. Choose the more precise collation (unicode_ci is generally preferred over general_ci).

Fix 2

Standardise collation across both columns

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

Standardise collation across both columns
ALTER TABLE sessions MODIFY username VARCHAR(255)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Why this works

Making both columns use the same collation eliminates the mismatch. Set a consistent default at the database level to prevent recurrence.

Fix 3

Set a consistent database default collation

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

Set a consistent database default collation
ALTER DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Why this works

The database collation is the default for new tables and columns. Changing it does not retroactively change existing columns.

What not to do

✕ Mix utf8mb4_general_ci and utf8mb4_unicode_ci randomly

Leads to inconsistent sort orders and comparison results depending on which collation wins.

Version notes
MariaDB 10.6+

utf8mb4_uca1400_ai_ci is available and is the recommended modern collation for new databases.

Sources

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors