1999
MySQLERRORNotableEncodingHIGH confidence
Collation and character set mismatch
Production Risk
Low — DDL or DML is rejected; no data is modified.
What this means
The specified collation is not valid for the given character set. Each collation belongs to exactly one character set, and combining a collation with an incompatible character set is an error.
Why it happens
- 1Using a utf8mb4 collation with the latin1 character set.
- 2Mixing CHARACTER SET and COLLATE clauses that belong to different character sets.
- 3Setting a session collation that does not match the session character set.
How to reproduce
trigger — this will error
trigger — this will error
CREATE TABLE t (c VARCHAR(100) CHARACTER SET latin1 COLLATE utf8mb4_unicode_ci);
expected output
ERROR 1999 (HY000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'.
Fix 1
Use a collation that belongs to the specified character set
Use a collation that belongs to the specified character set
CREATE TABLE t (c VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci);
Why this works
latin1_swedish_ci is a valid collation for the latin1 character set.
Fix 2
Change the character set to match the collation
Change the character set to match the collation
CREATE TABLE t (c VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci);
Why this works
utf8mb4_unicode_ci is a valid collation for utf8mb4.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 1999 ER_COLLATION_CHARSET_MISMATCH
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev