Currently, it seems anyway, a lot of the underlining technologies that I use everyday to build websites (ie, PHP and MySQL) are quickly upgrading and making big changes. These changes are exciting and I look forward to utilizing them as soon as I can but sometimes it is hard to just “flip the switch” on a major update so I tend to migrate each site over one by one to make sure everything is still working and performing well. To this end, I am now running a hybrid setup where I use MySQL 5.6 on some servers and MariaDB 5.5 on others. Migrating between the two mostly works just fine but lately I have started encountering an issue with collation and character sets as 5.5 does not support some of the settings that 5.6 does. Here are my notes on how to fix this issue:
- Dump a backup of the database that needs to be migrated with MySQL Workbench, mysqldump, phpMyAdmin, or a similar tool. This should give you a nice long text document filled with sql statements.
- Open this document in notepad, vim, MySQL Workbench or any other text editor that you use and find/replace the following values:
- Replace DEFAULT CHARSET=utf8mb4 with DEFAULT CHARSET=utf8
- Replace COLLATE=utf8mb4_unicode_520_ci with COLLATE=utf8_general_ci
- Replace COLLATE utf8mb4_unicode_ci with COLLATE utf8_general_ci
- Replace COLLATE=utf8mb4_unicode_ci with COLLATE=utf8_general_ci
- You might also have variations on these statements that need to be replaced (ie, DEFAULT CHARSET = utf8mb4
- Save the new file and then execute it. Watch for errors as some tools will add spaces around the equal sight or have other slight variations that you find/replace will not match.