Bug #27890 mysqldump does not export table indices in the right format

• Submitted: 17 Apr 2007 15:52
• Modified: 19 Sep 2007 21:31
• Reporter: Riz Joj
• Status: Duplicate
• Category: MySQL Server: Backup
• Severity: S3 (Non-critical)
• Version: 5.1 BK, mysql Ver 14.12 Distrib 5.1.12-beta, fo
• OS: Windows (XP, Linux)
• CPU Architecture: Any
• Tags: CREATE TABLE, export, mysqldump

[17 Apr 2007 15:52] Riz Joj

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`),
...

[18 Apr 2007 6:51] Sveta Smirnova

Thank you for the report.

Verified as described.

[18 Apr 2007 6:52] Sveta Smirnova

test case

Attachment: bug27890.test (application/octet-stream, text), 320 bytes.

[19 Sep 2007 21:31] MySQL Verification Team

Duplicate of bug: http://bugs.mysql.com/bug.php?id=25162.