EC
1253
MySQLERRORNotableCharacter SetsHIGH confidence

COLLATION does not apply to CHARACTER SET

Production Risk

Low — DDL fails; no data affected.

What this means

ER_COLLATION_CHARSET_MISMATCH (1253, SQLSTATE 42000) is raised when a collation is specified that does not belong to the given character set. Each collation is associated with exactly one character set.

Why it happens
  1. 1Using utf8mb4_unicode_ci with CHARACTER SET latin1
  2. 2Specifying a collation from a different character set than the column or database default
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t1 (
  name VARCHAR(100) CHARACTER SET latin1 COLLATE utf8mb4_unicode_ci
);  -- ERROR 1253

expected output

ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'

Fix

Use a collation that matches the character set

Use a collation that matches the character set
CREATE TABLE t1 (
  name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

Why this works

Each collation applies to exactly one character set. Use SHOW COLLATION to list valid collations per charset.

Sources
Official documentation ↗

MySQL 8.0 — 1253 ER_COLLATION_CHARSET_MISMATCH

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

← All MySQL errors