Bug #52084 LOAD DATA LOCAL INFILE does not honor STRICT_TRANS_TABLES SQL mode

• Submitted: 16 Mar 2010 10:18
• Modified: 16 Mar 2010 11:11
• Reporter: Stephane Giron
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.39
• OS: Any
• CPU Architecture: Any

[16 Mar 2010 10:18] Stephane Giron

Description:
Hi,

It seems that load data local infile does not behave as load data infile when SQL mode is strict. I could not find in documentation any reason for that.

Cheers
Stephane

How to repeat:
1. Create a table :

mysql> show create table i;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| i     | CREATE TABLE `i` (
  `i` varchar(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. Set SQL mode to STRICT_TRANS_TABLES

mysql> set @@session.sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

3. Try to load the file using load data infile and load data local infile :

mysql> load data infile '/tmp/test.sql' into table i;
ERROR 1406 (22001): Data too long for column 'i' at row 1

mysql> select * from i;
Empty set (0.00 sec)

mysql> load data local  infile '/tmp/test.sql' into table i;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from i;
+------+
| i    |
+------+
| abcd |
+------+
1 row in set (0.00 sec)

The file is a really simple one :
cat /tmp/test.sql 
abcdefgh

And finally, both statements are run on the server itself (same file is used in the two cases)

[16 Mar 2010 11:11] Sveta Smirnova

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 about different behavior of LOAD DATA INFILE and LOAD DATA LOCAL INFILE and at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html about how STRICT modes work with IGNORE