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.
© 2024, Oracle Corporation and/or its affiliates