MySQL Bugs: #78853: INSERT IGNORE fails to ignore foreign key constraint

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

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);

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`))

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

// 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>

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.