Bug #5744 mysqldump doesn't quote reserved or otherwise invalid column names
Submitted: 25 Sep 2004 8:34 Modified: 28 Sep 2004 8:39
Reporter: Laszlo Thoth Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.0.0-alpha-standard OS:Darwin 7.5.0 (MacOS X 10.3.5)
Assigned to: CPU Architecture:Any

[25 Sep 2004 8:34] Laszlo Thoth
Description:
One of my tables contains a column called 'show', a reserved word.

I can create tables with this column and I can dump these tables to a file, but I cannot re-import the dumped table.

How to repeat:
# show the table I'm creating...
$ cat test.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  `show` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# add the table to the 'test' database...
$ ~/Tools/mysql/bin/mysql -uroot test < test.sql

# dump the table back out...
$ ~/Tools/mysql/bin/mysqldump -uroot test |tee test2.sql
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  show int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# now re-import the dumped table...
$ ~/Tools/mysql/bin/mysql -uroot test < test2.sql
ERROR 1064 at line 17: 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 'show int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 2

$ diff -u test.sql test2.sql 
 DROP TABLE IF EXISTS test;
 CREATE TABLE test (
-  `show` int(11) default NULL
+  show int(11) default NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Suggested fix:
Make mysqldump add `quotes` around column names when exporting.
[26 Sep 2004 14:37] Hartmut Holzgraefe
mysqldump does not add quotes by default but you can ask it to by
calling it with the following option:

  -Q, --quote-names   Quote table and column names with backticks (`).

A dump created with this option should work just fine in your case
[28 Sep 2004 8:39] Sergei Golubchik
Actually, mysqldump does add quotes by default - it was changed on July 2, 2004.
You may upgrade to get the change.