Bug #41827 Load data: load a csv file fails without error

• Submitted: 2 Jan 2009 10:18
• Modified: 2 Jan 2009 12:01
• Reporter: Julien Theulier
• Status: Verified
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.0.74, 5.1.30
• OS: Windows (XP Pro 2002 SP3)
• CPU Architecture: Any
• Tags: load data; loading;fail;csv

[2 Jan 2009 10:18] Julien Theulier

Description:
Trying to load a CSV file on a table having 2 columns.
No row is loaded at all if instruction "IGNORE 1 LINES" present.
All rows except the header (this is normal) loaded if instruction "IGNORE 1 LINES" is removed

Have done a few imports. Seems to occurs when the table have few columns.

How to repeat:
Table definition:
CREATE TABLE "PROMOTION_YN"
(
  "ITEM_ID" numeric(18) NOT NULL,
  "Promotion_YN" varchar(1),
  CONSTRAINT item_promo PRIMARY KEY ("ITEM_ID")
) engine=INNODB;

Loading script:
LOAD DATA
INFILE 'E:/ebay/US/oracle/PROMOTION_YN.csv'
INTO TABLE "test"."promotion_yn"
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' 
IGNORE 1 LINES 
(@"ITEM_ID" ,
"Promotion_YN" )
SET
"ITEM_ID" = nullif(@"ITEM_ID",'');

Sample data to be attached

[2 Jan 2009 12:01] Valeriy Kravchuk

Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.30-enterprise-gpl-advanced-debug-log MySQL Enterprise Server
 - Advanced Edition Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_mode='ansi_quotes';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE "PROMOTION_YN"
    -> (
    ->   "ITEM_ID" numeric(18) NOT NULL,
    ->   "Promotion_YN" varchar(1),
    ->   CONSTRAINT item_promo PRIMARY KEY ("ITEM_ID")
    -> ) engine=INNODB;
Query OK, 0 rows affected (0.89 sec)

mysql> LOAD DATA
    -> INFILE 'c:\\tmp\\work\\41827.csv'
    -> INTO TABLE "test"."promotion_yn"
    -> FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    -> IGNORE 1 LINES
    -> (@"ITEM_ID" ,
    -> "Promotion_YN" )
    -> SET
    -> "ITEM_ID" = nullif(@"ITEM_ID",'');
Query OK, 0 rows affected (0.05 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA
    -> INFILE 'c:\\tmp\\work\\41827.csv'
    -> INTO TABLE "test"."promotion_yn"
    -> FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    -> (@"ITEM_ID" ,
    -> "Promotion_YN" )
    -> SET
    -> "ITEM_ID" = nullif(@"ITEM_ID",'');
Query OK, 50 rows affected, 2 warnings (0.17 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 1

[2 Jan 2009 12:07] Valeriy Kravchuk

5.0.74 is also affected.