MySQL Bugs: #48126: mysqlimport: does not quote column names

Bug #48126	mysqlimport: does not quote column names
Submitted:	16 Oct 2009 23:12	Modified:	16 Oct 2018 13:38
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
We received this comment in the online manual at:

http://dev.mysql.com/doc/refman/5.1/en/copying-databases.html

----------
Posted by Keller Ramon on October 13 2009 3:32pm

mysqlimport automatically generates the SQL statements to import the data from the *.txt files.

This leads to an error, if the table's name is a reserved word like i.e. 'order'. mysqlimport does not escape table names with quotes (`order`) like it is done in the corresponding *.sql file generated by mysqldump using the --tab option.

In this case, you'll have to use one of the other methods.
----------

Create a table where the table and column name are reserved:

mysql> drop table `order`;
ERROR 1051 (42S02): Unknown table 'order'
mysql> create table `order` (`order` int);
Query OK, 0 rows affected (0.34 sec)

Create empty data file:

% touch /tmp/order.txt

Try to load the file (MySQL 5.0.88):

% mysqlimport test /tmp/order.txt
mysqlimport: Error: 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 'order IGNORE 0 LINES' at line 1, when using table: order

Statement reported in the general query log:

		      9 Query       LOAD DATA   INFILE '/tmp/order.txt' INTO TABLE order IGNORE 0 LINES

Try it specifying the column name explicitly:

% mysqlimport --columns=order test /tmp/order.txt
mysqlimport: Error: 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 'order IGNORE 0 LINES (order)' at line 1, when using table: order

Statement reported in the general query log:

		     11 Query       LOAD DATA   INFILE '/tmp/order.txt' INTO TABLE order IGNORE 0 LINES (order)

In MySQL 5.1, the table name is quoted (which is a little progress), but the column name still is not:

% mysqlimport test /tmp/order.txt
test.order: Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

(success)

Statement reported in the general query log:

		    8 Query	LOAD DATA   INFILE '/tmp/order.txt' INTO TABLE `order` IGNORE 0 LINES

% mysqlimport --columns=order test /tmp/order.txt
mysqlimport: 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 'order)' at line 1, when using table: order

Statement reported in the general query log:

		    9 Query	LOAD DATA   INFILE '/tmp/order.txt' INTO TABLE `order` IGNORE 0 LINES (order)

How to repeat:
See above

Suggested fix:
Apply identifier quoting to any column names specified via the --columns option.

Thank you for the bug report.

This bug is still open.

The current work around is to manually generate the 'LOAD DATA INFILE' command (and ` quote column names) and execute the generated command using 'mysql --user=fred --password=secret dbname -e LOAD DATA INFILE...'.

This is still present on 5.6 mysql import. A reserved word in the column list will not allow for me to use a column list on the command line

This is not restricted to columns; also table names cannot be keywords.

Seems to be fixed since 5.7 already? It works with 5.7.22 at least ...

Posted by developer:
 
In fact, this is fixed in 5.1 (5.1.73) and above, but still and issue in 5.0. Presumably it was fixed "sometime in 5.1".