Error Restoring MySQL Database When USING BTREE on Indexes
Occasionally when I’m restoring a database that was backed up from a different server than the one I’m restoring on I get an error along the lines of:
1 2 3 4 5 6 7
Hmm, the docs suggest that BTREE is a valid index type for both MyISAM and InnoDB. So why is it barfing?
Well it turns out dumping the database from a newer version of MySQL than what you are restoring onto can be bad. In my case running status at the mysql prompt told me that my source database was running on 5.5.9 but the destination version was 5.0.51a.
Basically the issue seems to be that MySQL 5.0.x has issues with USING BTREE. So…just remove that from your sql and you’ll be good to go. Alternatively you can use the –compatible option when running your mysqldump on the newer server version, something like:
The value of “compatible” can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options.