Bug #48189 LOAD DATA LOCAL and IGNORE behavior
Submitted: 20 Oct 2009 14:41
Reporter: Scott Noyes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any

[20 Oct 2009 14:41] Scott Noyes
Description:
LOAD DATA LOCAL automatically sets the IGNORE option, "because the server has no way to stop transmission of the file in the middle of the operation."

"With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort." - but I had to look on the INSERT syntax page to find that out. It is not mentioned on the LOAD DATA syntax page.

How to repeat:
cat 10 > file.txt

CREATE TABLE t (d decimal(1,0));
SET sql_mode='STRICT_ALL_TABLES';
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE t;

Results: 

Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'd' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t;
+------+
| d    |
+------+
|    9 |
+------+
1 row in set (0.00 sec)

Suggested fix:
Three requests:

- The LOAD DATA manual page only discusses IGNORE's treatment of duplicate key violations; it should also cover data conversions. Either reference the INSERT page where it is described in full, or copy the information to the LOAD DATA page.

- IGNORE is able to skip rows that violate a duplicate key; it should also skip rows that violate the sql_mode, rather than behaving as if there were no sql_mode.

- The LOCAL option should not automatically turn on the IGNORE option. The argument that "the server has no way to stop transmission of the file" is not sufficient. Issue an error, and then quietly consume and discard the rest of the file.