Bug #28071 mysqlimport does not quote or escape table name
Submitted: 24 Apr 2007 12:30 Modified: 6 Aug 2009 20:34
Reporter: Ben Taieb Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.27, 5.1, 4.1 OS:Any (Linux, Win32)
Assigned to: CPU Architecture:Any
Tags: mysqlimport

[24 Apr 2007 12:30] Ben Taieb
Description:
Hi,
I'm using mysqlimport to import data to a table with following command line under windows XP SP2:
mysqlimport --password=pass --user=root --ignore -fields-terminated-by=; testdb C:\load.txt
I use the same cmd line to import tens of files so the command appears to be fine
This produces :
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 'load FIELDS TERMINATED BY ';' IGNORE 0 LINES' at line 1, when using table: load.

Then using directly Mysql browser :
LOAD DATA INFILE 'C:/load.txt' IGNORE INTO TABLE load FIELDS TERMINATED BY ';' IGNORE 0 LINES
produces the same error where :
LOAD DATA INFILE 'C:/load.txt' IGNORE INTO TABLE testdb.load FIELDS TERMINATED BY ';' IGNORE 0 LINES
work like a charm...

How to repeat:
see .
I haven't test but i really believes that this problem exists with any reserved word in mysql...

Suggested fix:
just fully qualified the table with db name in mysqlimport.
An easy workaround is to import from mysql browser or with mysql --execute statement or rename the file and table before upload....
[24 Apr 2007 13:25] Sveta Smirnova
Thank you for the report.

Version 5.0.27 is quite old. Additionally I can not repeat described behaviour with mysqlimport current version and test file.

Please try with current 5.0.37 version of MySQL and if you can repeat provide file you trying to import.
[25 Apr 2007 9:03] Ben Taieb
Hi,
I had time this morning to upgrade to 5.0.37, and still have this behaviour...
To be absolutly sure, i have :
-check that mysqlimport version has been changed it is now:
1 482 752 bytes from 5 March 2007 21:58:16 so i assume it is mysqlimport released with 5.0.37.

1) connect through mysql rather than mysql query browser (version 1.2.9 rc)
2) put an empty load.txt file to be sure it's not interfering...
I get exactly the same result :
ERROR 1064 (42000): You have and error etc......
even with verbose mode, i don't get that much information...
Let me know if you want me to test others things...
[25 Apr 2007 9:04] Ben Taieb
don't know if it has any importance but engine of this db is innodb.
[25 Apr 2007 9:13] Sveta Smirnova
Please provide output of SHOW CREATE TABLE load;
[25 Apr 2007 9:18] Ben Taieb
Hi Sveta,
SHOW CREATE TABLE load;
return the same error...
but
SHOW CREATE TABLE `load`;
OR
SHOW CREATE TABLE testdb.load;
returns :
CREATE TABLE `load` (
  `Timestamp` datetime NOT NULL,
  `Value` double unsigned NOT NULL,
  `QUALIF` varchar(45) NOT NULL,
  `DS` varchar(45) NOT NULL,
  `Platform` varchar(45) NOT NULL,
  `Object` varchar(45) NOT NULL,
  `Computer` varchar(45) NOT NULL,
  PRIMARY KEY  (`Timestamp`,`QUALIF`,`DS`,`Object`,`Computer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
[25 Apr 2007 9:36] Sveta Smirnova
Hi Ben,

> SHOW CREATE TABLE load;
> return the same error...

that's interesting. Please provide output of SHOW VARIABLE LIKE '%char%', SHOW VARIABLE LIKE '%collation%' statements and indicate locale of your Operating System.
[25 Apr 2007 9:50] Ben Taieb
SHOW VARIABLES LIKE '%char%' from mysql
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | latin1                                                  |
| character_set_connection | latin1                                                  |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | latin1                                                  |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
SHOW VARIABLES LIKE '%char%' from mysql query browser
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
SHOW VARIABLES LIKE '%collation%' from mysql
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
SHOW VARIABLES LIKE '%collation%' from mysql query browser
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
seems that there is something odd here...
[25 Apr 2007 9:55] Sveta Smirnova
Thank you for the feedback.

But what is the language and locale of your Windows? (You can find information about locale in the "Language and region" control panel.)
[25 Apr 2007 10:02] Ben Taieb
Sorry,
On the server ->Regional and Languaga Options -> Languages-> Details-> Default input language ->English(United Kingdom)
Is that what you needed ?
Rgds,
Ben.
[25 Apr 2007 10:03] Ben Taieb
And Regional and Language Options->Regional Options->English(United Kingdom).
[25 Apr 2007 10:14] Sveta Smirnova
Thank you, Ben.

It was what I needed.
[8 May 2007 20:04] Sveta Smirnova
Thank you for the report and comments.

Verified as described.

Head of te report can be read as "mysqlimport fails insert into table is table name is reserved word" though
[8 May 2007 20:04] Sveta Smirnova
test case

Attachment: bug28071.test (application/octet-stream, text), 452 bytes.

[28 Feb 2008 2:41] Jim Winstead
There also may be other issues with values not being properly escaped before being used in the LOAD DATA query (like the filename).
[19 May 2009 23:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74532

2903 Jim Winstead	2009-05-19
      Table identifiers and file names were not quoted and escaped correctly by
      mysqlimport. (Bug #28071)
      modified:
        client/mysqlimport.c
        mysql-test/r/mysqldump.result
        mysql-test/t/mysqldump.test
[14 Jul 2009 17:21] Jim Winstead
Pushed to 5.1-bugteam and mysql-pe.
[4 Aug 2009 19:51] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:jimw@mysql.com-20090714171651-ziq080rkon3u244l) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[6 Aug 2009 20:34] Paul DuBois
Noted in 5.1.38, 5.4.4 changelogs.

Table identifiers and file names were not quoted and escaped 
correctly by mysqlimport.
[12 Aug 2009 23:01] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:19] Paul DuBois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[9 Oct 2009 1:30] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.