Bug #78853 INSERT IGNORE fails to ignore foreign key constraint
Submitted: 16 Oct 2015 1:17 Modified: 11 Feb 2016 16:21
Reporter: Chongning Liao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.27, 5.5.47, 5.1.77 OS:Red Hat (Red Hat Enterprise Linux Server release 6.2 (Santiago))
Assigned to: CPU Architecture:Any

[16 Oct 2015 1:17] Chongning Liao
Description:
When trying to insert an entry that violates a foreign key constraint, mysql server fires ERROR 1452 regardless of the "IGNORE" keyword.

Same issue as reported in Bug #41693, but the bug persists in MySQL server 5.6.27.

Same issue also reported at http://stackoverflow.com/questions/6849393/mysqls-insert-ignore-into-foreign-keys

How to repeat:
create table t1 (s1 tinyint primary key) engine=innodb;
create table t2 (s1 tinyint, foreign key (s1) references t1 (s1)) engine=innodb;
insert into t1 values (0);
insert ignore into t2 values (1);
[16 Oct 2015 1:20] Chongning Liao
Below is the error generated, FYI.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ttt`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`))
[16 Oct 2015 5:51] Umesh Shastry
Hello Chongning Liao,

Thank you for the report.
This issue exists in 5.1,5.5,5.6 builds but I see some improvements done in 5.7 where error has been converted to warning instead of error after WL#6614.

Thanks,
Umesh
[16 Oct 2015 5:53] Umesh Shastry
// 5.1, 5.5, 5.6

mysql> create table t1 (s1 tinyint primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (s1 tinyint, foreign key (s1) references t1 (s1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into t2 values (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`))
mysql> \q

// 5.7.10

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t1 (s1 tinyint primary key) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (s1 tinyint, foreign key (s1) references t1 (s1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into t2 values (1);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                          |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t1` (`s1`)) |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+
| s1 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

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

mysql>
[11 Feb 2016 16:21] Paul Dubois
Noted in 5.5.49, 5.6.30, 5.7.12, 5.8.0 changelogs.

For INSERT and UPDATE operations that caused FOREIGN KEY constraint
violations, errors were reported rather than warnings when the IGNORE
keyword was used.