Bug #11123 Mysqldump creates invalid table create with numeric field titles
Submitted: 7 Jun 2005 3:11 Modified: 7 Jun 2005 8:13
Reporter: Robert Settle Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:mysqldump Ver 8.23 Distrib 3.23.58 OS:pc-linux-gnu
Assigned to: CPU Architecture:Any

[7 Jun 2005 3:11] Robert Settle
Description:
Dumping a table that contains fields named as numbers (or starting with numbers probably) fails to import.  I suspect that mysql never intended its fields to be able to be named numbers, but its possible by prefixing the field name with the table name.

How to repeat:
Example:

create table test ( test.1 int, test.2 int, test.3 int );

Mysqldump produces:

CREATE TABLE test (
  1 int(11) default NULL,
  2 int(11) default NULL,
  3 int(11) default NULL
) TYPE=MyISAM;

Importing this creates this error:

"ERROR 1064: 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 '1 int(11) default NULL,
  2 int(11) default NULL,
  3 int(11) d"

Suggested fix:
When the field is named numeric, provide an exception for mysqldump to prefix the fields in the table create with table name "dot" (eg test.1).
[7 Jun 2005 8:13] Sergei Golubchik
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

in *that* old mysqldump you should use either --allow-keywords or --quote-names.
(you can even put one of them in ~/.my.cnf)

In newer mysqldump it should not be necessary.