When a Database’s Encoding Label Is Wrong

Modernizing an older database rarely comes down to changing a configuration switch from Latin-1 to UTF-8.

Take an application that has run for fifteen years across several server migrations. You inspect the schema and see columns declared as latin1. But looking at the actual text, you find users have been entering em dashes, smart quotes, accented names, and the occasional emoji for a decade. Modern browsers send UTF-8 by default. If the connection and table collation were left at older defaults, MySQL happily stores those incoming multibyte sequences as if each individual byte were an independent Latin-1 character.

This is the classic double-encoding trap, often visible as mojibake. A single em dash (—) is three bytes in UTF-8 (0xE2 0x80 0x94), but stored in Latin-1 or Win-1252 it looks like the three separate characters —. As long as the legacy application reads the bytes back through the same misconfigured connection and sends them to a browser expecting UTF-8, the round trip quietly survives. The bytes went in, the same bytes came out, and nobody noticed because nobody inspected the raw tables directly.

The moment you try to clean things up by running ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4, the illusion breaks. The database believes its own metadata. It assumes those three bytes are three real Latin-1 characters and helpfully converts each one into its multi-byte UTF-8 counterpart. Your three-byte em dash becomes an unreadable seven-byte mess.

Serialized configuration blobs make this dangerous. PHP’s serialize() and similar formats record explicit byte lengths. A string like s:12:"user’s guide"; expects twelve bytes; if a naive database conversion expands the three-byte curly apostrophe, unserialize() fails and drops the entire settings record on the floor.

Treating character encoding as data repair instead of a schema flag avoids the trap:

• Look at the bytes, not the label. Don’t trust SHOW CREATE TABLE. Dump raw hex values for fields with names, accents, or punctuation to determine what encoding the stored bytes actually represent.
• Change the declaration without transcoding the bytes. If a table declared as Latin-1 already holds valid UTF-8 bytes, you don’t want transcoding; you want the schema to tell the truth. Converting a column to its binary equivalent (such as BLOB) and then to utf8mb4 updates the metadata label without mutating the underlying byte values.
• Recalculate serialized lengths. Any change that alters byte counts will break serialized payloads. If bytes must change, parse the serialized structure, recalculate the explicit lengths, and re-serialize.
• Compare fields before and after. Export a raw snapshot before the migration. Compare every row and field across both versions, checking raw byte sequences and decoded characters, particularly around non-ASCII boundaries.

A column collation is a promise about what the stored bytes mean. When that promise is already wrong, telling the database to convert them makes the damage permanent; fix the bytes and the metadata together.

—jhunterj