MariaDB Encoding and Collation Errors

How to diagnose and fix character-set problems in MariaDB: the utf8 3-byte trap that loses emoji, the Illegal mix of collations error (1267) in JOINs, risky ALTER TABLE CONVERT TO CHARACTER SET operations, and connection-charset mismatches that produce mojibake.

Quick Reference

• In MariaDB, utf8 is an alias for utf8mb3 (maximum 3 bytes per character). utf8mb3 CANNOT store emoji or any code point above U+FFFF. ALWAYS use utf8mb4 for any text that can hold user-generated content.
• Illegal mix of collations (error 1267) means two strings in a comparison or JOIN have different collations and neither can be coerced to the other. Fix with an explicit COLLATE clause or by ALTERing the columns to the same collation.
• utf8mb4_uca1400_ai_ci is the modern default collation from MariaDB 11.5 onward. Before 11.5 the default was utf8mb4_general_ci. Mixing these two in a JOIN triggers error 1267.
• ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 rewrites EVERY row and can grow indexed columns past the 767-byte index-prefix limit of the COMPACT row format. Use ROW_FORMAT=DYNAMIC (limit 3072 bytes) before converting.
• Set the connection charset with SET NAMES utf8mb4. A connection charset that does not match the column charset produces mojibake (garbled text) even when the table is correct.
• Incorrect string value (error 1366) on INSERT means a 4-byte character was sent to a utf8mb3 (or latin1) column. The fix is to convert the column to utf8mb4, not to strip the character.
• MariaDB JSON is a LONGTEXT alias, not native binary. Use CHECK (JSON_VALID(col)) for structure; use functional indexes on virtual columns for index access. JSON columns inherit the table charset and should be utf8mb4.
• NEVER use a _bin collation for case-insensitive search: _bin compares raw bytes, so 'A' never matches 'a'.

Error Identification