Bug #73654 LOAD DATA LOCAL INFILE's behavior depends on whether with REPLACE or not
Submitted: 20 Aug 2014 9:57 Modified: 11 Mar 2016 4:42
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.20 OS:Linux (CentOS 6.3)
Assigned to: CPU Architecture:Any
Tags: strict_trans_tables, warning

[20 Aug 2014 9:57] tsubasa tanaka
Description:
LOAD DATA LOCAL INFILE without REPLACE keyword *ignores* data-interpretation error even under STRICT_TRANS_TABLES sql_mode.
But LOAD DATA LOCAL INFILE with REPLACE *doesn't ignore* data-interpretation error but raises warning instead of error, this doesn't work under STRICT_TRANS_TABLES sql_mode.

Document, http://dev.mysql.com/doc/refman/5.6/en/load-data.html , says "
With LOAD DATA LOCAL INFILE, data-interpretation and duplicate-key errors become warnings".

I think "LOAD DATA LOCAL INFILE with REPLACE"'s behavier is correct, "without REPLACE"'s behavier is incorrect.

How to repeat:
$ echo -e "1\tone" > /tmp/test

mysql56> SELECT @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql56> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.07 sec)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql56> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level   | Code | Message                                                                   |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

* This behavier is ignoring data-interpretation error.
* This doesn't raise an error even in STRICT_TRANS_TABLES)

mysql56> LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

* This behavier is not ignoring data-iterpretation error.

This differences come from following lines in sql/sql_load.cc.

 318   /* We can't give an error in the middle when using LOCAL files */
 319   if (read_file_from_client && handle_duplicates == DUP_ERROR)
 320     ignore= 1;

LOAD DATA LOCAL INFILE without REPLACE keyword has handle_duplicates= DUP_ERROR,  "ignore" valiable set to 1.

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373170, table_list=0x7f217c373200, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_ERROR, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$3 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' INTO TABLE t1"

LOAD DATA LOCAL INFILE with REPLACE doesn't have handle_duplicates= DUP_ERROR, but has DUP_REPLACE. "ignore" variable is still 0.

Breakpoint 1, mysql_load (thd=0x36c9430, ex=0x7f217c373180, table_list=0x7f217c373210, fields_vars=..., set_fields=...,
    set_values=..., handle_duplicates=DUP_REPLACE, ignore=false, read_file_from_client=true)
    at /home/yoku0825/mysql-5.6.20/sql/sql_load.cc:190
190     {
(gdb) p thd->query_string->string->str
$2 = 0x7f217c373080 "LOAD DATA LOCAL INFILE '/tmp/test' REPLACE INTO TABLE t1"
[21 Aug 2014 13:58] Umesh Shastry
Hello tsubasa tanaka,

Thank you for the bug report.

Thanks,
Umesh
[11 Mar 2016 1:37] Paul Dubois
Per Umesh's comment ("May be this is code bug??"), we need to have a developer's evaluation whether this is a server bug or a docs bug, before just assuming it's a docs bug.