MySQL Bugs: #73654: LOAD DATA LOCAL INFILE's behavior depends on whether with REPLACE or not

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

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"

Hello tsubasa tanaka,

Thank you for the bug report.

Thanks,
Umesh

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.

It looks like this issue is still unresolved.

I am currently using the mysql8.0 version, and this problem still occurs
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

LOAD DATA LOCAL INFILE without REPLACE keyword *ignores* data-interpretation error even under ‘traditional’ 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 'traditional' sql_mode

复现问题过程如下所示
复现问题过程如下所示
The problem recurrence process is as follows：

1. set sql_mode = 'traditional'
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> load data local infile 'std_data/loaddata_fun01.dat' replace into table t2 columns terminated by "," (a,b,@var) set c = 'x';
ERROR 1366 (HY000): Incorrect integer value: 'x' for column 'c' at row 1

mysql> load data local infile 'std_data/loaddata_fun01.dat' into table t2 columns terminated by "," (a,b,@var) set c = 'x';
Query OK, 0 rows affected, 10 warnings (0.01 sec)
Records: 5  Deleted: 0  Skipped: 5  Warnings: 10

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 1 |
| Warning | 1062 | Duplicate entry '1' for key 't2.PRIMARY'             |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 2 |
| Warning | 1062 | Duplicate entry '2' for key 't2.PRIMARY'             |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 3 |
| Warning | 1062 | Duplicate entry '3' for key 't2.PRIMARY'             |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 4 |
| Warning | 1062 | Duplicate entry '4' for key 't2.PRIMARY'             |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 5 |
| Warning | 1062 | Duplicate entry '5' for key 't2.PRIMARY'             |
+---------+------+------------------------------------------------------+
10 rows in set (0.00 sec)

mysql> load data local infile 'std_data/loaddata_fun01.dat' ignore into table t2 columns terminated by "," (a,b,@var) set c = 'x';
Query OK, 0 rows affected, 10 warnings (0.00 sec)
Records: 5  Deleted: 0  Skipped: 5  Warnings: 10

2. set session sel_mode = 'default'
mysql> set session sql_mode =default;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> load data local infile 'std_data/loaddata_fun01.dat' into table t2 columns terminated by "," (a,b,@var) set c = 'x';
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 5

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 1 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 2 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 3 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 4 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 5 |
+---------+------+------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> delete from t2;
Query OK, 5 rows affected (0.01 sec)

mysql> load data local infile 'std_data/loaddata_fun01.dat' replace into table t2 columns terminated by "," (a,b,@var) set c = 'x';
ERROR 1366 (HY000): Incorrect integer value: 'x' for column 'c' at row 1

Official documents：https://dev.mysql.com/doc/refman/8.0/en/load-data.html
"Those factors combine to produce restrictive or nonrestrictive data interpretation by LOAD DATA:
1)Data interpretation is restrictive if the SQL mode is restrictive and neither the IGNORE nor the LOCAL modifier is specified. Errors terminate the load operation.
2)Data interpretation is nonrestrictive if the SQL mode is nonrestrictive or the IGNORE or LOCAL modifier is specified. (In particular, either modifier if specified overrides a restrictive SQL mode.) Errors become warnings and the load operation continues."

But "load data local infile" with "replace" can't overrides a restrictive SQL mode, the details are as follows.

##########################################################################
mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t1(a INT NOT NULL, b INT, c INT);

mysql> load data local infile 'std_data/loaddata_fun01.dat' replace into table t1 columns terminated by "," (a,b,@var) set c = 'x';

mysql> load data local infile 'std_data/loaddata_fun01.dat' into table t1 columns terminated by "," (a,b,@var) set c = 'x';
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 5

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 1 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 2 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 3 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 4 |
| Warning | 1366 | Incorrect integer value: 'x' for column 'c' at row 5 |
+---------+------+------------------------------------------------------+
5 rows in set (0.00 sec)

#########################################################################

# cat std_data/loaddata_fun01.dat
1,10,60
2,20,70
3,30,80
4,40,90
5,50,100