DB-유

Converting a MySQL Database from utf8 to utf8mb4

· mysql, charset, migration

Being Korean, I run into this early: MySQL's utf8 is not real UTF-8. It stores at most three bytes per character, so emoji, many CJK extension characters and a lot of symbols don't fit. Someone pastes one into a comment field and you get ERROR 1366 Incorrect string value or, worse, silently truncated data. The fix is utf8mb4 (since 5.5.3), and on 5.7 I see no good reason to use anything else.

The conversion touches every table, so size your maintenance window to your data. Before starting, take a fresh backup (mysqldump --single-transaction), be on 5.7 (or 5.6 with innodb_file_format=Barracuda and innodb_large_prefix=1, since index length limits are what kill conversions on 5.6 defaults), and collect the application connection strings, because the client side has to change too.

Find what needs converting

SELECT table_schema, table_name, table_collation
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_collation NOT LIKE 'utf8mb4%';

SELECT table_name, column_name, character_set_name, column_type
FROM information_schema.columns
WHERE table_schema = 'shop' AND character_set_name = 'utf8';

Pay special attention to VARCHAR(255) columns that are part of an index, and to TEXT columns close to their maximum size.

Server defaults

Set the defaults so new tables and connections get utf8mb4 automatically. In /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

init_connect is a safety net for clients that do not set their charset; it is not executed for users with SUPER. Restart the server afterwards.

For 5.6 also add, unless already present:

innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

Index length limits

With utf8mb4 a VARCHAR(255) can be 1020 bytes. An InnoDB index prefix is limited to 767 bytes with the COMPACT or REDUNDANT row format, and 3072 bytes with DYNAMIC or COMPRESSED. On 5.7 new tables default to DYNAMIC, but tables created on older versions may still be COMPACT. Check and fix them first:

SELECT table_name, row_format FROM information_schema.tables
WHERE table_schema = 'shop' AND row_format IN ('Compact','Redundant');

ALTER TABLE customers ROW_FORMAT=DYNAMIC;

Alternatively shorten indexed columns to VARCHAR(191) (191 × 4 = 764 bytes), which is what many frameworks did for years. I prefer changing the row format; it keeps the schema honest, and I don't want magic 191s in every migration.

Convert the database and the tables

Change the default for the schema:

ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then convert each table. CONVERT TO CHARACTER SET changes both the table default and every character column, and rebuilds the table:

ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE orders    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

To generate the statements for a whole schema:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_type = 'BASE TABLE';

Save the output to a file and run it with mysql shop < convert.sql. On a large production table use pt-online-schema-change from Percona Toolkit so the table stays writable:

pt-online-schema-change --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" \
  D=shop,t=orders --execute

One caution: CONVERT TO also widens TEXT types when needed (a TEXT may become MEDIUMTEXT) because the byte capacity would otherwise shrink. Review the resulting schema.

Don't forget the applications

The server side is only half of it, and it's the half people remember. Each client must talk utf8mb4 on the wire:

  • PHP PDO: new PDO('mysql:host=db01;dbname=shop;charset=utf8mb4', ...)
  • PHP mysqli: $mysqli->set_charset('utf8mb4');
  • Python (MySQLdb / PyMySQL): charset='utf8mb4' in the connection arguments
  • JDBC Connector/J: characterEncoding=UTF-8 (the driver maps this to utf8mb4 on 5.5.3+)
  • WordPress: set DB_CHARSET to utf8mb4 in wp-config.php

Test with an emoji

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
INSERT INTO comments (body) VALUES ('Test 😀 𠜎');
SELECT body, HEX(body) FROM comments ORDER BY id DESC LIMIT 1;

The hex output for the emoji must start with F09F9880. If you see 3F (a question mark) the connection charset is still utf8 or latin1 somewhere.

Things that bit me

  • ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes – Table still uses COMPACT row format or innodb_large_prefix is off. See the index length section.
  • ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1 after conversion – The column is fine but the connection is not; run SHOW SESSION VARIABLES LIKE 'character_set_client' from the application's connection.
  • ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. – Same root cause as 1071, reported during ALTER TABLE on 5.6.
  • Data that was previously stored as double-encoded garbage (é instead of é) does not become correct by conversion. That is a separate problem caused by a latin1 connection writing UTF-8 bytes, and needs a CONVERT(BINARY CONVERT(col USING latin1) USING utf8mb4) style repair per column.

Make utf8mb4_unicode_ci the default on every new server and you only ever do this migration once.