Description:
To reproduce this bug, one needs a fix for Bug#40127, otherwise the server hangs or crashes.
The manual says that IGNORE keyword causes errors to be downgraded to warnings.
This is the case for DELETE, but not for multi-table DELETE. WL#4103 "Define IGNORE" confirms.
mysql> drop table if exists t1,t2,t3;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
mysql> create table t1 (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 (a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> create table t2 (a int primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t2 (a) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> create table t3 (a int, b int, constraint c_a foreign key (a) references t1 (a), constraint c_b foreign key (b) references t2 (a)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t3 (a, b) values (1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> delete ignore from t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `c_a` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> delete ignore from t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `c_b` FOREIGN KEY (`b`) REFERENCES `t2` (`a`)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete ignore t1,t2 from t1,t2;
Query OK, 1 row affected (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> select * from t1;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> select * from t2;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
Notice also the strange number of affected rows (1) in case of multi-delete.
How to repeat:
drop table if exists t1,t2,t3;
create table t1 (a int primary key) engine=innodb;
insert into t1 (a) values (1);
create table t2 (a int primary key) engine=innodb;
insert into t2 (a) values (1);
create table t3 (a int, b int, constraint c_a foreign key (a) references t1 (a), constraint c_b foreign key (b) references t2 (a)) engine=innodb;
insert into t3 (a, b) values (1, 1);
delete ignore from t1;
show warnings;
delete ignore from t2;
show warnings;
delete ignore t1,t2 from t1,t2;
show warnings;
select * from t1;
select * from t2;
Suggested fix:
Report warnings in case of IGNORE if we're doing a multi-table DELETE.
