MySQL Bugs: #830: mysqldump creates SQL with errors

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
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))
) TYPE=MyISAM;

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.

Thanks!
Robert

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.

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:

ALTER TABLE ... ADD KEY (FIELD (15))

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.