mysql_dump not restored correctly 1064: USING BTREE + fix

Nov 10, 2011 · 1 minute read
Category: mysql

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

When migrating one server to another you often hit bumps in the road. Todays was transferring a database from one server to another.

During this standard procedure I found that the restored database was missing a few tables. Irritating as Magento doesn’t like missing tables.

Digging down into the backup and extracting the first missing table I was able to replicate the error which came out as 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 ‘USING BTREE

I eventually found out that some versions of mysql 5.1 export a dump file that contains mysql5.1 specific features and loading the file into mysql 5.0 will not work.

The solution is a little frustrating but if you run the command with the –compatible=mysql40 switch, the dump file extracts fine :- mysqldump –compatible=mysql40 Don’t ask me why there’s no –compatible=mysql50 flag.