1973
MariaDBERRORNotableCharacter SetHIGH confidence
Character set '%s' cannot be used in conjunction with collation '%s' to sort in order
Production Risk
Low — query is rejected.
What this means
The specified character set and collation are incompatible. A collation can only be used with the character set it belongs to.
Why it happens
- 1Using a utf8mb4 collation with a latin1 column or vice versa.
- 2Specifying a collation that does not belong to the column's character set.
How to reproduce
trigger — this will error
trigger — this will error
SELECT * FROM t ORDER BY name COLLATE utf8mb4_unicode_ci; -- name is latin1
expected output
ERROR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'latin1'.
Fix 1
Convert the column to the correct character set
Convert the column to the correct character set
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Why this works
Converting the column ensures the character set and collation are compatible.
Fix 2
Use a collation that matches the column's character set
Use a collation that matches the column's character set
SELECT * FROM t ORDER BY name COLLATE latin1_swedish_ci;
Why this works
Match the collation to the column's actual character set.
Sources
Official documentation ↗
MySQL 8.0 — 1973 ER_CTYPE_NO_UNICODE
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev