Recently I had to move a WordPress database from a site running an old version of MySQL, specifically
5.6.33, and it lead to some problems.
First Problem: Unknown collation utf8mb4_unicode_520_ci
#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'
This was the first error message I received when trying to import the database. It turns out this can be avoided by modifying the export file to use
MYSQL40 (for additional optional reading see sql_mode MYSQL40).
When exporting, in this case through phpMyAdmin, use Export Method: Custom.
Then under Format-specific options: choose
This resolves the Unknown collation error.
Second Problem: Error in your SQL syntax
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM AUTO_INCREMENT=20125' at line 19
Unfortunately, once the first issue was resolved, I found another issue. The
TYPE keyword was removed in MySQL 5.5.
The older TYPE option that was synonymous with ENGINE was removed in MySQL 5.5. When upgrading to MySQL 5.5 or later, you must convert existing applications that rely on TYPE to use ENGINE instead.
— from MySQL Documentation on TYPE/ENGINE
To address this issue, you’ll need to open your
.sql file in a text editor and find-and-replace all instances of
Save your updated
.sql file and now your import should work.
Thanks to both Matt and Leland‘s comments below, I’ve updated this post. Previously, I suggested replacing
TYPE=MyISAM however they both pointed out they were dealing with databases using
TYPE=InnoDB. They were still able to apply the fix by updating the SQL to
ENGINE=InnoDB. Based on this, it sounds like updating
ENGINE= should do the trick regardless of the actual type being used.