MySQL 5.1 dump files won't restore on MySQL 5.0
Posted on Dec 19, 2008 by Paul White
Most developers when making a backup of MySQL
will leave all the settings to default as its usually safe to leave more advanced settings alone if you don't know what they do. But if you are running the latest version of MySQL
DB server 5.1, the chances are not all your servers are running on this new version. This was the case for me. I had my workstation at home running MySQL
5.1, and the VPS
I was going to migrate my data to was running on MySQL
5.0. Upon making the SQL dump file needed to transfer my data, I left the default settings as is. In the past this had served me well. But when doing the restore on the destination machine, I got a weird error with the syntax in my SQL dump file. Something about BTREE.
It turns out the MySQL
5.1 by default will but your INDEX parameters in the wrong order for your create table statements. The solution is to make sure when you are making the SQL dump file to click on the advanced Options tab, then check the Compatibility mode box. Then when you restore, everything will go as planned.
No Comments have been submitted