MySQL 5.1 dump files won't restore on MySQL 5.0


MySQL 5.1 dump files won't restore on MySQL 5.0

Posted on Dec 19, 2008 by Paul White

MySQL DB serverMost 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.

Compatibility mode checked MySQL backup


Permalink
2492 Visitors
5004 Views

Categories associated with MySQL 5.1 dump files won't restore on MySQL 5.0

Discussion

No Comments have been submitted
name
Email Needed to confirm comment, but not made public.
Website
 
Type Code
Security Check
 
When you Post your Comment, you'll be sent a confirmation link. Once you click this link your thoughts will be made public.. Posts that are considered spam will be deleted, Please keep your thoughts and links relavent to this Article