Bug #830 mysqldump creates SQL with errors
Submitted: 10 Jul 2003 18:19 Modified: 11 Jul 2003 1:19
Reporter: Robert Allen Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: CPU Architecture:Any

[10 Jul 2003 18:19] Robert Allen
We have a database up and running and were testing backup/restore procedures.  We dumped the database using mysqldump and when we try to load the data back into the database I'm getting an SQL error on the CREATE TABLE statement.  The generated sql follows:
CREATE TABLE match_entry_detail (
  match_entry_id bigint(20) default NULL,
  field varchar(64) default NULL,
  value text,
  UNIQUE KEY match_entry_id (match_entry_id,field),
  KEY value_index (value(30)),
  KEY field (field(15)),
  KEY value (value(30))

The error is on the line: KEY field (field(15))

To be honest, I'm not sure why/how the KEY is defined like that, I can only assume that a developer has used an alter statement on the table/index.  If I recreate the table using our database setup script and then immediately dump it I do not get the same SQL.  So, I don't have the original SQL that defined this table, and since we ran the mysqldump output we no longer have the table defined in mysql any longer either.  

However, I know that this table was readable and working fine.   It seems to me that mysqldump should be able to create good sql for a working table.


How to repeat:
I don't think this exact example can be repeated as I am not sure how exactly the table was defined before we dumped/loaded it.
[11 Jul 2003 1:19] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

There is a function FIELD(). So, FIELD(15) is treated as a function call.
To make it an identifier one has to put space before parenthesis:


it's how this key was probably created.

As for mysqldump, there is an option--quote-names that you shouild use to solve your problem.