𝗠𝗶𝗴𝗿𝗮𝘁𝗶𝗻𝗴 𝗠𝗮𝗴𝗲𝗻𝘁𝗼 𝟮 𝗳𝗿𝗼𝗺 𝘂𝘁𝗳𝟴 𝘁𝗼 𝘂𝘁𝗳𝟴𝗺𝗯𝟰
A customer used an emoji in their name. The database cut the name short right after the emoji. No error appeared in the logs.
The problem is MySQL utf8. It is not real UTF-8. It is a three-byte limit.
Emojis and many modern symbols need four bytes. If you use the old utf8, MySQL truncates your data. It happens silently if your SQL mode is not strict. You lose data without knowing it.
The solution is utf8mb4. It supports four bytes per character.
You cannot simply run a conversion command on a large Magento store. You will likely hit an error. The error says your key is too long.
This happens because MySQL calculates index size based on the maximum possible bytes. A VARCHAR(255) column uses 765 bytes in utf8. In utf8mb4, that same column needs 1020 bytes. This exceeds the old 767-byte limit.
How to fix this:
- Use MySQL 8.0 or MySQL 5.7 with innodb_large_prefix enabled. This increases your limit to 3072 bytes.
- If you use an old server, reduce indexed VARCHAR columns to 191 characters. 191 characters times 4 bytes equals 764 bytes. This fits the limit.
A successful migration requires three steps:
- Update the database and tables. Use ALTER TABLE CONVERT TO CHARACTER SET utf8mb4. This rewrites existing data.
- Update server defaults in your my.cnf file. This ensures new tables use the correct settings.
- Update the application connection charset. If the connection stays on utf8, the data will corrupt before it reaches the database.
Watch out for these risks:
- Table locks. CONVERT TO rewrites the whole table. Large tables will lock up. Use tools like pt-online-schema-change to keep your store online.
- Collation mismatches. All joined tables must use the same collation. Use utf8mb4_unicode_ci across your entire schema.
If you still use utf8, your store is not Unicode-safe. You are just waiting for the wrong character to break your data.