MariaDB utf8mb4 Migration: Index Length Limits and the Double-Encoding Trap

The ticket was polite and devastating: a customer's invoice PDF rendered his name as José, and it had rendered it that way for two years. The name had arrived over the wire correctly as UTF-8, passed through a connection layer that believed it was latin1, and been stored — byte for mislabeled byte — into a column declared utf8. Which is not UTF-8. In MariaDB, as in MySQL, the charset named utf8 is an alias for utf8mb3: a three-byte subset that cannot store emoji, most CJK extensions, or any character outside the Basic Multilingual Plane, and whose existence has lured a generation of schemas into thinking they were Unicode-complete. Our database held thousands of rows of mangled text and rejected the rest outright — every support ticket containing an emoji had been dying with ERROR 1366 (22007): Incorrect string value, logged and ignored, since the mobile app shipped.

The migration to utf8mb4 took us six weeks for one database and is the single highest-value cleanup I have done on that platform. It is also a minefield of index-length limits, row formats, connection-level recodings, and already-corrupted data that a naive ALTER will faithfully preserve. This is the playbook, in the order that kept us out of trouble.

Why is MariaDB's utf8 not real UTF-8?

MariaDB's utf8 charset encodes at most three bytes per character, which covers the Basic Multilingual Plane and nothing beyond it — real UTF-8 needs up to four bytes, and that is what utf8mb4 provides. The practical consequences split in two. Characters outside the BMP — emoji, rare ideographs, musical notation, most historic scripts — are rejected under STRICT_TRANS_TABLES with ERROR 1366, or silently truncated at the offending character when strict mode is off, which is worse: we found product reviews that ended mid-sentence at the first emoji, cut years ago, unrecoverable. Characters inside the BMP store fine, which is why the lie survives so long — everything works until someone pastes a rocket emoji into a review. Check what you are actually running, because the defaults differ by era and by who installed the box:

-- server-level defaults
SELECT @@character_set_server, @@collation_server,
       @@character_set_database, @@collation_database;

-- the truth per table: utf8 here means utf8mb3
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

-- connection-level recoding, the usual culprit
SELECT @@character_set_client, @@character_set_connection,
       @@character_set_results;

For the migration itself, target utf8mb4 with the utf8mb4_unicode_ci collation family — or utf8mb4_uca1400_ai_ci on MariaDB 10.10 and newer, which implements the modern Unicode Collation Algorithm and sorts accented characters the way users actually expect. Collation choice is a one-way door per column, so pick deliberately; changing collation later is another table rebuild, and comparison semantics change under your WHERE clauses the day you flip it.

Why does the first ALTER die with ERROR 1071?

Because switching a column from utf8mb3 to utf8mb4 increases its worst-case byte length by a third, and InnoDB's index prefix limits are measured in bytes — a VARCHAR(255) utf8mb4 column can need 1,020 bytes per index entry, and on an old-style InnoDB setup the limit is 767 bytes. The failure is ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes, and it stops the migration cold on exactly the columns you most want indexed. The fix is the InnoDB large-prefix machinery: the table must use the Barracuda file format with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, which raises the limit to 3,072 bytes. On any remotely modern MariaDB this is already the default — innodb_file_format defaulted to Barracuda and innodb_default_row_format to dynamic long ago — but legacy databases upgraded since the 5.x era carry COMPACT row formats forward, and those are the tables that fail:

-- find tables that will hit the 767-byte wall
SELECT TABLE_NAME, ROW_FORMAT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app' AND ROW_FORMAT IN ('Compact', 'Redundant');

-- fix the row format first, then the charset
ALTER TABLE customers ROW_FORMAT = DYNAMIC;
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Two secondary limits lurk behind the first. Index-only prefixes like INDEX(name(191)) are the classic workaround when 3,072 bytes is still not enough — 191 characters times 4 bytes is 764, which fit the old limit, and the convention has stuck — but prefix indexes cannot serve uniqueness or covering reads, so treat them as a last resort, not a plan. And CONVERT TO CHARACTER SET rewrites the whole table, so on anything large you are in online-DDL territory: the locking behavior is the same minefield described in the ALTER TABLE locking notes from this series, and on our 90-million-row events table we ran the conversion through an online schema change tool during a traffic trough rather than let a native ALTER hold the metadata lock.

How do you fix data that is already double-encoded?

CONVERT TO CHARACTER SET changes the column's declared charset and transcodes what it believes the bytes to be — which means it preserves double-encoded mojibake perfectly, because the bytes are, as far as the column knows, legitimate utf8mb3 text. José survives the migration untouched. The corrupted rows are the ones where UTF-8 bytes were interpreted as latin1 and stored: the fix is to reinterpret the bytes back through the wrong encoding, which in SQL is the two-step cast CONVERT(BINARY(CONVERT(col USING latin1)) USING utf8mb4) — read the column as latin1 to recover the original byte stream, treat it as raw bytes, then reinterpret those bytes as utf8mb4. Test it on a copy first and scope it with a heuristic — we targeted rows where the byte length suspiciously exceeded the character count in ways consistent with latin1 mislabeling, validated a sample by eye, and fixed 41,000 rows in batches of 5,000 to keep replication lag flat. Some rows will not be repairable: anything stored through multiple wrong encodings, or truncated by the old charset, is gone, and the honest move is to log those primary keys to a table for support rather than guess.

The connection layer is the last place migrations go to die. If any client connects without SET NAMES utf8mb4 — or with a driver default that says otherwise — you recode correctly-stored data into mojibake on the way in, and the corruption resumes the day you declare victory. We audited every service's connection string, set character-set-server and collation-server in the config for new databases, and added the charset assertions to the staging checklist from the upgrade field notes, because charset drift and version drift travel together. Teams still straddling MySQL should note the divergence details in migrating from MySQL: gotchas — the utf8mb4 collation names do not line up one-to-one across the two.

Where MonPG fits

The signals worth trending through a charset migration are the rejection and drift counters: ERROR 1366 rates as your measure of what users are currently losing, connections negotiated at non-utf8mb4 charsets after the cutover, and the per-table collation inventory so utf8mb3 tables shrink to zero instead of quietly regenerating from a missed default. Full disclosure, as in every article of this series: I work on MonPG, which monitors PostgreSQL in production today and does not monitor MariaDB yet. MariaDB support is coming soon and in active development — the /mariadb-monitoring page tracks where it stands — and connection-level configuration drift is on the list of signals it is being built around. Until that ships, the information_schema queries above run quarterly are your kit. If PostgreSQL is also in your fleet, that monitoring is live today — see the PostgreSQL overview, or browse more field notes on the blog.