WordPress + backupBuddy error re collation not found utf8mb4_unicode_520_ci

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