How to fix database character set and collation
If text shows up garbled after an import or migration - accented letters turning into stray symbols,
or emoji disappearing - the database is usually in an older character set such as latin1 or
three-byte utf8. Converting it to utf8mb4 (full Unicode, including emoji) fixes this.
Warning
Run this against a copy or a fresh backup first, and during a
quiet window: the ALTER TABLE statements rewrite every table and lock it while they run.
Before you start
-
SSH access to the host with the system user that owns the application - see SSH access.
-
The database name, from the host's Credentials tab.
-
A fresh backup and a quiet moment, because every table is rewritten and locked while it runs.
Convert a database and all its tables
Connect over SSH and convert in two parts - the database default, then each table. This small script does both for one database:
DB="prod_db"
CHARSET="utf8mb4"
COLLATION="utf8mb4_unicode_ci"
mysql -e "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLLATION;"
mysql -N -s -e "SHOW TABLES" "$DB" | while read TABLE; do
echo "Converting $DB.$TABLE"
mysql "$DB" -e "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLLATION;"
doneutf8mb4_unicode_ci is a good general-purpose collation. Some applications expect a specific one
(for example utf8mb4_general_ci or utf8mb4_0900_ai_ci) - match what your application's
documentation asks for.
Verify
Check that the database reports the new character set:
mysql -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name='prod_db';"Then reload the site and confirm the previously garbled text now displays correctly.
If it did not work
If the database reports utf8mb4 but text is still garbled, the application connects with a
different character set. Look for a charset or collation setting in its own configuration, for
WordPress DB_CHARSET in wp-config.php, and set it to utf8mb4. Data that was already imported
with the wrong character set has to be imported again from the source dump, with
--default-character-set=utf8mb4. See
Database connection and performance problems, or
contact support.