Two symptoms bring this job to us. Someone pastes an emoji into a support ticket and the field saves empty or throws Incorrect string value: '\xF0\x9F\x98\x80'. Or a customer named Müller shows up in the admin as M??ller, or Müller, and nobody can say when it started. Both point at the same thing: a database whose declared character set does not match the bytes inside it, or does not have room for the characters the application now receives.
This is a solvable afternoon-to-a-few-days job on most estates. It is also one of the easiest migrations to make permanently worse, because the wrong ALTER re-interprets bytes instead of converting them and the damage is not obvious for weeks. The order below exists to prevent that.
What the three encodings actually are
- latin1 (
latin1_swedish_ci) was MySQL's default for a very long time. One byte per character, no room for anything outside Western European. - utf8 in MySQL is not UTF-8. It is
utf8mb3: a maximum of three bytes per character, which covers the Basic Multilingual Plane and excludes emoji and some CJK characters. It is deprecated in MySQL 8.0 and you should be leaving it. - utf8mb4 is real UTF-8, up to four bytes. It is the only correct target. In MySQL 8.0 and 8.4 it is the server default.
If you are already planning a 5.7-to-8.0 or an 8.0-to-8.4 upgrade, do the charset work as its own change, before or after — not in the same window. Two one-way changes at once means an ambiguous rollback.
Step 1: find out what the bytes really are
The column's declared charset tells you what MySQL thinks. It is frequently wrong, because a PHP app that connected without setting a charset wrote UTF-8 bytes into latin1 columns for years and everything looked fine as long as the same broken assumption applied on the way out. This is the well-known state people call "double-encoded" or "latin1-in-latin1" data, and it determines your entire migration path.
Start with the inventory:
SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = 'yourdb' AND character_set_name IS NOT NULL
ORDER BY character_set_name, table_name;
Then test the actual bytes on a table that holds non-ASCII text — customer names are ideal:
SELECT name, HEX(name) FROM customers
WHERE name NOT REGEXP '^[ -~]*$' LIMIT 20;
Read the hex. Müller stored as correct latin1 is 4D FC 6C ... — one byte, FC, for ü. Stored as UTF-8 bytes sitting in a latin1 column it is 4D C3 BC 6C ... — the two-byte sequence C3 BC. That distinction decides everything:
- Bytes match the declared charset (true latin1 data): a straight
CONVERT TO CHARACTER SET utf8mb4is correct. MySQL transcodes the bytes. - Bytes are already UTF-8 inside a latin1 column:
CONVERT TOwill transcode them again and you getüpermanently. You need the binary route: change the column toVARBINARY/BLOBfirst, then to the utf8mb4 type. That preserves the bytes and just re-labels them.
A mixed database — some tables converted years ago, some never — is common. Check per table, not once.
Step 2: rehearse on a restored backup
Restore last night's dump onto a scratch server and run the whole conversion there. You are measuring two things: how long the ALTERs take on real data volumes, and whether the output is right. Spot-check the same rows you hexed in step 1, and have someone who reads the affected language look at a page of real names. Mojibake is much easier for a human to spot than a checksum.
If you do not have a restore drill in place, this is a good moment to start one — the rehearsal doubles as the test.
Step 3: the conversion itself
For true latin1 data, per table:
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
For UTF-8-bytes-in-latin1, per column, in two steps:
ALTER TABLE customers MODIFY name VARBINARY(191);
ALTER TABLE customers MODIFY name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Set the database default too, so new tables inherit it:
ALTER DATABASE yourdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
These are rewriting ALTERs. On a large table that means a long lock with the default algorithm — the same problem as any big schema change, and the same answer: pt-online-schema-change or gh-ost against a table with a primary key, rehearsed first. Small tables can go in a maintenance window; convert the biggest ones online.
Step 4: the traps
Index length. utf8mb4 reserves four bytes per character, so a 255-character indexed VARCHAR needs 1020 bytes. On old tables still using the COMPACT row format with a 767-byte index limit, the ALTER fails with Specified key was too long. Three ways out: convert the table to DYNAMIC row format (ROW_FORMAT=DYNAMIC, the default since 5.7, which allows 3072 bytes with innodb_large_prefix), shorten the indexed column to 191 characters (the old WordPress trick), or index a prefix: KEY (email(191)). Prefer the row-format fix; the 191 convention is a workaround for a limit you no longer have.
Storage and memory. VARCHAR only stores the bytes it needs, so disk growth is modest. Temporary tables and sort buffers, however, allocate for the maximum width — a query sorting a VARCHAR(255) utf8mb4 column reserves 1020 bytes per row. If you have absurdly wide columns that only ever hold short strings, narrow them while you are in here.
Collation choice. On MySQL 8, utf8mb4_0900_ai_ci is the modern default: faster, Unicode 9.0 aware, accent- and case-insensitive. utf8mb4_general_ci is the legacy one. The catch is that a join between a 0900 column and a general_ci column raises Illegal mix of collations and will not use an index. So pick one collation and apply it to the entire database, including columns you were going to skip. Mixed collations cause more incidents than the wrong collation does.
MySQL 8.4 note. utf8mb4_0900_ai_ci stays valid in 8.4; what changes around it is the utf8mb3 deprecation path getting louder. If you are on 8.4 or heading there, converting off utf8mb3 now removes one item from that upgrade's list.
Step 5: the application side
The database is half of it. The connection has a charset too, and a utf8mb4 database talking to a latin1 connection reintroduces the original bug immediately.
- PDO:
new PDO('mysql:host=...;dbname=...;charset=utf8mb4', ...). The DSNcharsetparameter is the correct place; do not rely on runningSET NAMESyourself. - mysqli:
$mysqli->set_charset('utf8mb4');right after connecting. - Laravel / Symfony: set
charsetandcollationin the database config toutf8mb4and your chosen collation, and check the framework's own migration defaults. - Set
character_set_serverandcollation_serverinmy.cnfso a client that forgets still lands on utf8mb4. - Check anything that talks to MySQL outside the application: cron scripts, report exporters, an old Perl job, the BI tool. Each has its own connection charset.
- Serve pages as UTF-8 (
<meta charset="utf-8">and theContent-Typeheader), and confirm any CSV export or email template is not re-encoding on the way out.
And validate on input. PHP's mb_check_encoding($value, 'UTF-8') at the boundary stops the next bad-bytes incident cheaply.
Step 6: verify, then keep the backup
After the cutover, re-run the HEX() spot checks against production, search for the telltale sequences (Ã followed by a punctuation-range byte is the signature of double encoding), and check the places non-ASCII text ends up: search results, PDF generation, the CSV export nobody owns, email subject lines. Keep the pre-conversion backup for longer than usual — a month, not a week. Encoding damage is often reported late, and the only reliable fix is the original bytes.
When this is not worth doing
If the application is genuinely single-language ASCII — an internal tool with no names, no free text, no user-facing input — and there is no MySQL 8 upgrade in front of you, latin1 is not a security problem and the conversion can wait. Say so and spend the hours somewhere that moves a number. But if you accept names, addresses, or anything a customer types, you will hit this eventually, and it is cheaper before the data is mixed than after.
If your hex output in step 1 came back ambiguous, or the mangling is already several layers deep from a previous attempted fix, that is the case where an hour of a senior engineer's time is worth more than a weekend of guessing. Send us the schema and a few hex samples and we will tell you which path applies.