# 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](../../platform/hosts/backups.md) first, and during a
> quiet window: the `ALTER TABLE` statements rewrite every table and lock it while they run.

## Convert a database and all its tables

Connect over [SSH](../../platform/hosts/ssh.md) and convert in two parts - the database default,
then each table. This small script does both for one database:

```bash
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;"
done
```

`utf8mb4_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:

```bash
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.

## Related

- [Import and export a MySQL database](import-export-database.md)
- [Configure MySQL](configure.md)
- [Database problems](../../troubleshooting/database-issues.md)
- [Backups and restore](../../platform/hosts/backups.md)