Bug #43895 Multi-DELETE IGNORE does not report warnings
Submitted: 26 Mar 2009 18:45 Modified: 14 Jul 2014 15:31
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1 bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[26 Mar 2009 18:45] Konstantin Osipov
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.
[26 Mar 2009 20:35] Sveta Smirnova
Thank you for the report.

Verified as described.
[31 Mar 2009 7:35] Konstantin Osipov
This does not depend on WL#4103
[14 Jul 2014 15:31] Paul Dubois
Noted in 5.7.5 changelog.

The server could fail to report warnings for multiple-table DELETE
IGNORE statements.