Moving a latin1 database to utf8 without losing the accents

An invoice came back with the customer’s surname printed as three question marks. The same name rendered correctly on the admin screen two clicks away, out of the same column of the same table. Nothing was corrupt — the two pages disagreed about what the bytes in that column meant, and only one of them could be right.

The symptom

Of 52,318 customer rows, 4,127 held at least one byte outside ASCII: every accented surname the site had ever taken. Small enough to look like an edge case, large enough that fixing it by hand is not on the table.

mysql> SELECT COUNT(*) FROM customers;
52318

mysql> SELECT COUNT(*) FROM customers
    -> WHERE name <> CONVERT(name USING ascii);
4127

mysql> SHOW CREATE TABLE customersG
  `name` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The admin list issues no SET NAMES, so its connection is latin1 — as it was when those rows were written — and the bytes come back exactly as they went in, which a page declaring UTF-8 renders correctly. The invoice generator is two years newer, sets its connection to utf8, and gets the same bytes converted a second time on the way out. The PDF library has no glyph for the result.

Why it happens

Three settings decide what a character is between the form field and the page, and they live in three different places: the charset on the column, the charset of the client connection, and the Content-Type header. Output was switched to UTF-8 years ago and the application started sending UTF-8 bytes at the same time. The column was never touched, and neither was the connection default.

That combination does not fail loudly. Told the incoming bytes are latin1, MySQL stores them without complaint — two bytes of a UTF-8 character are simply two latin1 characters — and the data survives a full round trip looking perfect, until something reads it over a connection that has been told the truth.

A minimal reproduction

HEX() is the only tool that settles the argument. Every other way of looking at the value passes it through another layer of interpretation.

mysql> CREATE TABLE t (s VARCHAR(32)) DEFAULT CHARSET=latin1;

mysql> SET NAMES latin1;
mysql> INSERT INTO t VALUES ('Lefèvre');   -- the terminal sends UTF-8

mysql> SELECT HEX(s) FROM t;
4C6566C3A8767265                       -- stored untouched, 8 bytes

mysql> SET NAMES utf8;
mysql> SELECT HEX(s) FROM t;
4C6566C383C2A8767265                   -- converted on the way out, 10 bytes

The stored bytes never changed. C3 A8 is UTF-8 for è; read as two latin1 characters and re-encoded for a utf8 client it becomes C3 83 C2 A8. The mojibake is produced on the way out rather than stored, which is exactly why it is recoverable.

The fix

The order is the whole trick. Every failed attempt at this migration converts the column while the connection is still lying, which encodes bytes that were already UTF-8 a second time and produces damage far harder to reverse than the original problem.

Dump the bytes untranslated

Telling mysqldump the connection is latin1 makes it ask for the data in the charset it is stored as, so nothing is converted and the file holds the original bytes. The declarations in that file are then rewritten and reloaded over a connection that is finally telling the truth.

$ mysqldump --default-character-set=latin1 --single-transaction 
    --routines -u root -p shop > shop-raw.sql

$ sed -i 's/CHARSET=latin1/CHARSET=utf8/g;
          s/COLLATE=latin1_swedish_ci/COLLATE=utf8_general_ci/g;
          s/SET NAMES latin1/SET NAMES utf8/' shop-raw.sql

$ mysql --default-character-set=utf8 -u root -p shop_utf8 < shop-raw.sql

The SET NAMES line matters as much as the table declarations: mysqldump writes one at the top of the file, and if it still says latin1 the reload recreates the original mistake in a database that is now correctly declared. Loading into a second database keeps the whole thing reversible — the snapshot the dump was taken from holds the only copy of the old bytes.

The ALTER that will not run

The reload stops partway through on an error with no obvious connection to character sets. InnoDB allows 767 bytes for an index key prefix, and a character that cost one byte now costs up to three.

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

-- the offending key: (20 + 255) characters, now 825 bytes
UNIQUE KEY uniq_source_email (source, email)

-- a single VARCHAR(255) survives at 765 bytes, with two to spare

The instinct is to shorten the index with a prefix — email(180) — which is fine on an ordinary index. On a unique key it redefines what unique means: two addresses differing only after the 180th character become a duplicate-key error at some later date. Shortening the column to what the data actually needs is the honest fix, and 190 characters covers every address in the table.

The application side

Every connection now has to state its charset, and the call to do it is not the obvious one. mysql_set_charset() tells the client library as well as the server, and the client library is what mysql_real_escape_string() consults when deciding which bytes need escaping. Issuing SET NAMES as a query changes the server’s view and leaves the client’s stale.

$link = mysql_connect($host, $user, $pass);
mysql_select_db('shop', $link);

// not mysql_query('SET NAMES utf8', $link) — that leaves the client
// library believing the connection is still latin1
mysql_set_charset('utf8', $link);

Then the two scripts that measured text. Both counted with strlen(), which counts bytes, and both had been right for as long as every character was one. The teaser truncation now cuts halfway through an accent; the length check now rejects an ordinary name at 250 characters.

strlen('Lefèvre');              // 8  — bytes
mb_strlen('Lefèvre', 'UTF-8');  // 7  — characters

mb_substr($teaser, 0, 160, 'UTF-8');

Verifying it worked

Three checks. The first is HEX() over rows known to be wrong: the bytes should be identical to what came out of the old database, because nothing was supposed to convert them.

$ mysql -N shop_utf8 -e "SELECT HEX(name) FROM customers WHERE id = 4417"
4C6566C3A8767265

$ for t in customers orders order_lines invoices addresses; do
>   mysql -N shop_utf8 -e "SELECT '$t', COUNT(*) FROM $t"
> done > after.txt

$ diff before.txt after.txt
$ echo $?
0

The row count per table is the boring check that catches the expensive failure: a reload that stopped on an error partway through leaves a database that looks fine until somebody asks for an old order.

The third is the error log for the week afterwards. Two scripts nobody had thought about were still opening their own connections with no charset call, and both surfaced as “Incorrect string value” warnings within four days. There is no way to find those by inspection; you find them by watching.

What this costs

The downtime is not divisible. Dump, rewrite and reload have to happen with nothing writing, because a row inserted after the dump is a row that does not exist afterwards — an hour behind a maintenance page, at whatever time of night has the fewest orders in it.

The data directory grew by about a fifth. Three-byte characters are only part of it; index pages grow too, and a CHAR(2) country column that was two bytes is now six whether or not it will ever hold anything but ASCII. Check free disk before starting rather than after.

The permanent cost is a rule: every connection sets its charset explicitly, in the one shared file that opens them, and nothing opens a raw one anywhere else — not the cron scripts, not the one-off importers, not whatever gets written next year. The migration is finished in an evening; the rule is the part that has to survive, and nothing enforces it. As with an index that only works when the pattern is anchored, the failure mode stays invisible right up until it is not.