I Solved It: MySQL #1064 Error in WordPress Migration
“We must do it today.”
Aaron told me about the terrible situation that we have to migrate BunBun, one of the most popular makeup blogs in Singapore, from current hosting service to a new one, because it’s going to hit bandwidth quota this weekend then got whole site shut down.
BunBun is using WordPress for content management. There are a great number of tutorials when searching “wordpress migration” on Google, and they’re quite similar.
Simply, in 3 steps: backup files and database, upload files and database, update nameservers.
What’s the problem?
This happened when Aaron tried to import BunBun’s database to new hosting through phpMyAdmin. It stuck at 2 SQL files, wp_postmeta.sql and wp_posts.sql, and showed error messages as below:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="phpmyadmin.css.php?server=1&token=1354bfaefdab399a4fcfb6b821fdefe5&js_frame=right&nocache=5390314592" /> <link rel="stylesheet" type="text/css" href="print.css" media="print" /> <link rel="stylesheet" type="text/css" href="./themes/pmahomme/jquery/jquery-ui-1.8.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="./js/cross_framing_protection.js?ts=1344778698" type="text/javascript"></script> <script src="./js/jquery/jquery-1.4.4.js?ts=1344778[...]
MySQL said: Documentation
#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 '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w' at line 1
What the f**k!
Aaron said he had no idea about what the hell was going on there, and he tried exporting and importing these 2 files for 3 times. Then finally, the wp_postmeta table was imported successfully… but NOT for the wp_posts table.
Okay. I went back to check phpMyAdmin in old hosting, and found the wp_posts.sql file is about 120 megabytes. And due to the unreliable database connection in old hosting, the exporting always stopped around 10+ megabytes so we got a corrupted file, which lead to the #1064 SQL syntx error mentioned above. It also explained why wp_postmeta.sql file went through successfully in Aaron’s 3rd try.
By the way, I also noticed that phpMyAdmin in new hosting has a file size limit for importing of 50MB maximum.
What should I do?
Here I came out my solution: split wp_posts.sql file into 5~10 pieces and export one by one.
Luckily phpMyAdmin has this function to dump some rows only while exporting.
– In Export Method, select Custom;
– Then in Rows, select Dump some row(s).
The original SQL file was 8848 rows, and I like to split it into 9 pieces, so first I entered 1000 for Number of rows:, and 1 for Row to begin at:, then Go and download; repeat 9 times using 1000 and 1001 for 2nd piece; 1000 and 2001, 1000 and 3001…until 1000 and 8001 to download them all as 9 different named files.
Nevertheless, I got another issue while importing them one by one: if any of these files is corrupted after uploading, I have to manually clean up those incomplete rows added in the final wp_posts.sql file!
I know I need to ensure these files are complete before importing. So I use Notepad++ to open them one by one. The difference between corrupted and complete files is:
Data in corrupted files end with something like:
</html>
And data in complete files end with something like:
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
If it’s corrupted, go back to phpMyAdmin in old hosting and export again. Later I found the 1000-row piece starting from 7001 couldn’t be exported successfully after trying many times.
All right. I split this piece into another 5 smaller pieces. So it became 200-row small piece starting from 7001, 7201, 7401, 7601, 7801. It worked well.
That’s it.
After importing database, I changed the nameservers to new hosting. Then, BunBun migration was well done, and I thought I must record down this solution to MySQL error #1064 for future reference. Here it is.
Hey,
Thanks for the solution man. I had the same problem. But what I did was, opened the .sql file in notepad and copied all the text in it. Paste it directly into the SQL section and pressed Go.
And that worked!! :)
Hi,
Try This
1. Download mySQL database using “backups” located besides “File Manager” in cpanel
2. Import database using myphpadmin
It should work
Regards
Atul, this worked fine for me too. In fact a db of 1.3 million rows…never thought it would be this simple
Atul, this worked fine for me too.
You can also try exporting the database using custom export in phpmyadmin
1. Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement
2. Increase “Maximal length of created query”
3. Try importing in phpmyadmin
this should work too
Thanks a lot man you saved my day
Warm Regards
Ayush
I will follow your method. Its working fine , thanks for share
Simply ,, u r write :D
Thanks ^_^
This problem almost got me sick. I contacted my host but they had no solution. And voom! This page helped me. Thanks alot Princeawful.