Convert non-UTF-8 MySQL database to UTF-8

Summary

Database upgrades may fail due to a wrong character set usage. This article explains how to convert a non-UTF-8 MySQL database into the recommended UTF-8 database.

Solution

1. Let's suppose the database is using latin1 character set and latin1_general_cs collation:

   1 CREATE DATABASE dbname CHARACTER SET latin1 COLLATE latin1_general_cs;

2. Make sure that Fisheye / Crucible is not running;

3. Connect to the database being used by Fisheye / Crucible;

4. Create a database dump:

   1 mysqldump --opt -u root -p<secret> dbname > dbname.sql

5. Open the resulting dbname.sql file and replace all occurrences of latin1_general_cs by utf8_bin and all occurrences of latin1 by utf8, through find / replace, then save changes.

6. Restore dbname.sql. There are two possibilities here:

   1. Restore into a new database name, if you want to keep the original database untouched. This consists in:

      1. Creating a new database, this time using the correct character set and collation:

         1 CREATE DATABASE newdbname CHARACTER SET utf8 COLLATE utf8_bin;

      2. Restoring the dump:

         1 mysql -u root -p<secret> newdbname < dbname.sql

   2. Restore into the existing database name, if you're sure that the manual changes in dbname.sql through find / replace are correct. This consists in:

      1. Deleting the current database dbname:

         1 DROP DATABASE dbname;

      2. Creating it again, this time using the correct character set and collation:

         1 CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;

      3. Restoring the dump:

         1 mysql -u root -p<secret> dbname < dbname.sql

7. Restart MySQL service

8. Start Fisheye / Crucible