MySQL Bugs: #39837: SQL_MODE=TRADITIONAL and LOAD DATA LOCAL INFILE does not error

Bug #39837	SQL_MODE=TRADITIONAL and LOAD DATA LOCAL INFILE does not error
Submitted:	3 Oct 2008 12:04	Modified:	3 Oct 2008 19:56
Reporter:	Andy Rigby	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.28-rc-community	OS:	Any
Assigned to:
Tags:	SQL_MODE, traditional

Description:
When using LOAD DATA LOCAL INFILE, and SQL_MODE=TRADITIONAL if data that is being loaded is invalid for the column type (e.g. text is too long), an error is not produced. However, ommitting the the LOCAL keyword and loading data from server does error.

How to repeat:
-- c:\x.txt --
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--------------

mysql> use test;
Database changed

mysql> CREATE TABLE `xxx` (
  `txt` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

mysql> load data local infile 'c:\\x.txt' into table xxx;
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

# no try without LOCAL

mysql> load data infile 'c:\\x.txt' into table xxx;
ERROR 1406 (22001): Data too long for column 'txt' at row 1

Suggested fix:
LOAD DATA LOCAL INFILE should produce error in the same way LOAD DATA INFILE does when SQL_MODE=TRADITIONAL

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/load-data.html:

If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation. 

Strict mode affects only inserting NULL values into fields defined as NOT NULL