MySQL Bugs: #74120: LOAD DATA INFILE with CSV unexpectedly ignores rows

Bug #74120	LOAD DATA INFILE with CSV unexpectedly ignores rows
Submitted:	28 Sep 2014 7:47	Modified:	28 Sep 2014 17:41
Reporter:	Bill Plimpton	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.24a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	csv, LOAD DATA

Description:
When using LOAD DATA to import standard CSV containing a header row which is quoted, more than the expected number of rows are missing if IGNORE n LINES or ESCAPED BY is specified. This is similar to http://bugs.mysql.com/bug.php?id=11837

How to repeat:
Create CSV 'test.csv' containing data:

"field1","field2"
"field3","field4"
"field5","field6"

This file must use '\r\n' newlines (i.e. standard RFC4180 format for CSV.)

All 3 rows are skipped:

mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE XXX FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

Without "IGNORE 1 LINES", it works:

mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE XXX FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Without "ESCAPED BY", it also works:
mysql> load data local infile 'test.csv' into table xxx fields terminated by ',' enclosed by '"' lines terminated by '\r\n' ignore 1 lines;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

As mentioned in the bug 11837, adding a field terminator to each row also makes it work.

Just a small note. One shouldn't trust LOAD DATA if you do not have a fix for this important bug:

https://bugs.mysql.com/bug.php?id=58165

5.0.24a is beyond old, I'd advise trying a modern version, 5.5.40 or 5.6.21..

Thank you for the bug report. 5.0 version is End of Product Lifecycle.

http://dev.mysql.com/doc/refman/5.0/en/

"End of Product Lifecycle. Active development for MySQL Database Server version 5.0 has ended..."