Description:
LOAD DATA operation with IGNORE clause should skip current row and
continue on check constraint violation. But LOAD DATA is
terminating on check constraint violation.
How to repeat:
CREATE TABLE t2(f1 INT, f2 INT);
INSERT INTO t2 VALUES (1, 10), (20, 20), (3, 30), (4, 40);
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--eval SELECT * FROM t2 INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/tmp1.txt';
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/tmp1.txt' IGNORE INTO TABLE t1;
Warnings:
Warning 3819 Check constraint 't1_chk_1' is violated.
SHOW WARNINGS;
Level Code Message
Warning 3819 Check constraint 't1_chk_1' is violated.
SELECT * FROM t1;
f1 f2
1 10
Row2 from tmp1.txt violates the check constraint t1_chk_1 and
with IGNORE clause LOAD DATA is terminated, Row-3 and Row-4 are
not loaded to table t1.
Suggested fix:
With IGNORE clause on check constraint violation, current row should be skipped and LOAD DATA
should continue with other rows.
Issue here is, in Sql_cmd_load_table::read_xml_field() and Sql_cmd_load_table::read_fixed_length(),
invoke_table_check_constraints() is called to evaluate the check constraints on row.
If constraint evaluation fails then with IGNORE clause READ_INFO::next_line() is not called.
...
if (invoke_table_check_constraints(thd, table)) {
if (thd->is_error()) DBUG_RETURN(true);
// continue when IGNORE clause is used.
goto continue_loop;
}
...
Not invoking READ_INFO::next_line() here will result in a unexpected behavior for
the next row. In the scenario mentioned in this bug report, next row load is
terminated but it may end up inserting junk values or may be infinite loop in worst case.
Description: LOAD DATA operation with IGNORE clause should skip current row and continue on check constraint violation. But LOAD DATA is terminating on check constraint violation. How to repeat: CREATE TABLE t2(f1 INT, f2 INT); INSERT INTO t2 VALUES (1, 10), (20, 20), (3, 30), (4, 40); --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --eval SELECT * FROM t2 INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/tmp1.txt'; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/tmp1.txt' IGNORE INTO TABLE t1; Warnings: Warning 3819 Check constraint 't1_chk_1' is violated. SHOW WARNINGS; Level Code Message Warning 3819 Check constraint 't1_chk_1' is violated. SELECT * FROM t1; f1 f2 1 10 Row2 from tmp1.txt violates the check constraint t1_chk_1 and with IGNORE clause LOAD DATA is terminated, Row-3 and Row-4 are not loaded to table t1. Suggested fix: With IGNORE clause on check constraint violation, current row should be skipped and LOAD DATA should continue with other rows. Issue here is, in Sql_cmd_load_table::read_xml_field() and Sql_cmd_load_table::read_fixed_length(), invoke_table_check_constraints() is called to evaluate the check constraints on row. If constraint evaluation fails then with IGNORE clause READ_INFO::next_line() is not called. ... if (invoke_table_check_constraints(thd, table)) { if (thd->is_error()) DBUG_RETURN(true); // continue when IGNORE clause is used. goto continue_loop; } ... Not invoking READ_INFO::next_line() here will result in a unexpected behavior for the next row. In the scenario mentioned in this bug report, next row load is terminated but it may end up inserting junk values or may be infinite loop in worst case.