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
• Status: Closed
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.0.27, 5.1, 4.1
• OS: Any (Linux, Win32)
• 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:[e-mail]-20090804194615-h40sa098mx4z49qg) (version source revid:[e-mail]-20090714171651-ziq080rkon3u244l) (merge vers: 5.4.4-alpha) (pib:11)

[4 Aug 2009 20:45] Bugs System

Pushed into 5.1.38 (revid:[e-mail]-20090804204317-ggodqkik7de6nfpz) (version source revid:[e-mail]-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:[e-mail]-20091001055605-ap2kiaarr7p40mmv) (version source revid:[e-mail]-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:[e-mail]-20091001072547-kv17uu06hfjhgjay) (version source revid:[e-mail]-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:[e-mail]-20091001123013-g9ob2tsyctpw6zs0) (version source revid:[e-mail]-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:[e-mail]-20091005103850-dwij2dojwpvf5hi6) (version source revid:[e-mail]-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.