Scenario:
- Product site is on a hosting plan with MySQL 5.6.28
- Test site is on a separate hosting plan with MySQL 5.5.43
- On product site used BackupBuddy to create full backup
- Running ImportBuddy to restore onto the test site (was fine previously) at database step now returns:
- Unknown collation: ‘utf8mb4_unicode_520_ci’
- Opened a previous BackupBuddy zip and accessed the .SQL files at (or similar):
- \wp-content\uploads\backupbuddy_temp\qkn9tw8jo6\
- Search for text utf8mb4_unicode_520 and found it in a single SQL file (there is 1 file per table)
- I had installed a new plugin and it had created a new table. This .sql file was for that new table
- Comparing the other .SQL files to this one I notice in the create table statements the collate operator for each column:
- older table files: “COLLATE utf8mb4_unicode_ci NOT NULL”
- new table file: “COLLATE utf8mb4_unicode_520_ci NOT NULL”
- In myPHPAdmin for the MySQL instance on the TEST (target) server there is no collation option for “utf8mb4_unicode_520_ci”
Solution:
- I run a fresh BackupBuddy full backup and download it to somewhere safe
- In myPHPAdmin for production MySQL instance I run:
-
alter table <some_table> convert to character set utf8 collate utf8_unicode_ci; - BackupBuddy full backup again, move this across to the target site, run uploadbuddy.php and its all fine