MySQL Bugs: #3277: mysqldump extracts column names that are numbers incorrectly

Bug #3277	mysqldump extracts column names that are numbers incorrectly
Submitted:	23 Mar 2004 19:57	Modified:	24 Mar 2004 9:15
Reporter:	C. Brown	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	4.0.18-standard	OS:	Linux (Linux Red Hat 7.0)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Took a mysqldump of a database containing column names that are numbers.  The dump would not re-import until I added backticks (`) to the column names.  So a table of the form

CREATE TABLE abc (
 xyz date NOT NULL,
 1 date NOT NULL,
 PRIMARY KEY (xyz)
) TYPE=MyISAM;

will be produced by the export but will not import.  Adding backticks to the 1 so that the table reads

CREATE TABLE abc (
 xyz date NOT NULL,
 `1` date NOT NULL,
 PRIMARY KEY (xyz)
) TYPE=MyISAM;

will fix the problem.  This workaround is relatively easy but could prove a nuisance for large databases or databases with lots of data.

How to repeat:
Create a table with a column name that is a number using any tool.  
Run mysqldump on the database containing the table, saving the output to a file.
Attempt to import the resulting dump into a clean database.  A syntax error should occur.

Suggested fix:
The dump function should put ` symbols around all column names.  Initial tests seem to indicate that standard text column names will also work with backticks around them.

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

Additional info:

Use --quote-names or -Q to have the names escaped.