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 sql_mode
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 MYSQL40
.
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 TYPE=
with ENGINE=
.
Save your updated .sql
file and now your import should work.
Update Note:
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 TYPE=
to ENGINE=
should do the trick regardless of the actual type being used.
Great walk-through. The only difference in my situation was find-and-replace all instances of ‘TYPE=InnoDB’ with ‘ENGINE-InnoDB’. Worked like a charm. Thank you!
Hey, I have exactly the same errors but it appears there is no “TYPE=MyISAM” in my .sql file hence I cant make any replacements. Its showing cant find the text “TYPE=MyISAM”
Please help
Hi Ryan,
It sounds like more troubleshooting is necessary. I’d start with Matt’s comment above and see if that helps.
If you do get it sorted, I’d be interested to hear more about the problem and how you solved it.
Best of luck.
Hey Sal,
Same issue. Note yet resolved.. Am trying several other options
Hi,
Se screenshot from the link shared
http://prnt.sc/dkui5m
I am completely restless now after days of trying to resolve this seemingly simple issue….
Hi Ryan,
Since
TYPE=MyISAM
doesn’t appear in SQL file, I’d try searching forinstead and see if there is any value being set.
Does anything come up for “TYPE=” (not necessarily “TYPE=ISAM”)?
Mine was “InnoDB” instead of “ISAM” for example. I just search/replaced “TYPE=InnoDB” for “ENGINE=InnoDB” and everything worked fine.
P.S. Thanks Sal, I ran into this *exact* problem (except the ISAMInnoDB part) and got it solved thanks to this blog post.
Great, worked!
Thanks
Oh man, thank you soo much!!! u save my day
Thank you so much!
Thanks. You just made my night.. otherwise I wouldn’t have slept.
Thanks
Thank you so much. You save my lots of trouble.
Thanks you so much, Saves my day sir.
Thanks very very much !!! It helped a lot !
Pedrp
Work for me and solved my life..
Very thanks..
I was going crazy trying to find a solution for this and this helped! You made my day. Thank You
#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 ‘phpMyAdmin SQL Dump
— version 2.8.0.1
— http://www.phpmyadmin.net
—
— Host:’ at line 1
I am getting this error please do try to solve it.
Thanks
Hi kuldeep,
If I understand you correctly, it sounds like you have a general problem with your SQL syntax.
This post is about a specific situation (`#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’`), that I was able to resolve and wanted to document for future reference.
You might want to try posting your question to the Database Administrator Stack Exchange and tag your question with
mysql
. When you do that it may be helpful to post the first few lines of your SQL file.Best of luck.