Description:
Tried to export a dump file from MySQL 5.1 to import into MySQL 5.0.
dos command executed:
mysqldump.exe -uroot -pmypassword -d mydatabase > mydumpfile.sql
The output file could not be imported in the target MySQL db because of the error:
ERROR 1064 (42000) at line 23: 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,
KEY `columnAindex` (`columnA`) USING HASH,
KEY `columnBindex' at line 24
On investigating the dump file, the index portion of the CREATE TABLE statement violated
the syntax in the documentation:
PRIMARY KEY (`row_id`) USING BTREE,
UNIQUE KEY `columnAindex` (`columnA`),
KEY `columnBindex` (`columnB`) USING HASH,
KEY `columnCindex` (`columnC`) USING BTREE,
...
How to repeat:
EXPORT: At source database machine (MySQL 5.1), run command:
mysqldump.exe -uroot -pmypassword -d mydatabase > mydumpfile.sql
IMPORT: At target database machine (MySQL 5.0), run command:
mysql -uroot -pmyotherpassword -D myotherdatabase < mydumpfile.sql
Suggested fix:
Create the dump file so that the CREATE TABLE statement has the index type BEFORE the
index column names.
Using the example in the Descriptioon portion:
Change:
PRIMARY KEY (`row_id`) USING BTREE,
UNIQUE KEY `columnAindex` (`columnA`),
KEY `columnBindex` (`columnB`) USING HASH,
KEY `columnCindex` (`columnC`) USING BTREE,
...
To:
PRIMARY KEY USING BTREE (`row_id`),
UNIQUE KEY `columnAindex` (`columnA`),
KEY `columnBindex` USING HASH (`columnB`),
KEY `columnCindex` USING BTREE (`columnC`),
...
