MySQL Bugs: #44987: DELETE IGNORE and FK constraint : inconsistency in 5.0 , 5.1 and docs

Bug #44987	DELETE IGNORE and FK constraint : inconsistency in 5.0 , 5.1 and docs
Submitted:	20 May 2009 14:29	Modified:	2 May 2010 19:09
Reporter:	Andrii Nikitin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.78 , 5.1.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Manual clearly states that DELETE IGNORE should "ignore all errors during processing":

http://dev.mysql.com/doc/refman/5.1/en/delete.html
"
The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings. 
"

in 5.1 (InnoDB and maybe other ACID engines) it deletes rows until meets first FK constraints, then stops execution.

in 5.0 (InnoDB and maybe other ACID engines) it doesn't delete rows but reports "N rows affected"

This bug maybe reclassified to "DML" or "Doc" category.

How to repeat:
create table parent (id int primary key) engine = innodb;
create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)) engine = innodb;
insert into parent values (1), (2), (3), (4), (5), (6);
insert into child values (3), (5);

delete ignore from parent;
show warnings;
select row_count();
select * from parent;

==========================
The script above produces following output on 5.1.31 :

mysql> select * from parent;
+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
|  6 |
+----+
4 rows in set (0.00 sec)

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.31-community-log |
+----------------------+
1 row in set (0.00 sec)

===================
and following result on 5.0 :

mysql> select * from parent;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.78-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Variant 1 (server bug, preferable): It should delete all rows which may be deleted and reports warnings for errors.

Variant 2 (docs request): Manual clearly describes behavior for DELETE IGNORE for InnoDB if FK constraint met in 5.0 and 5.1

Variant 3 (server bug in 5.1, docs request): 5.1 changed to not delete rows (as in 5.0) and docs describe that DELETE IGNORE doesn't change table if FK constraint met.

Andrii, thank you for the bug report. Before considering whether it's a code or doc issue, we need to determine if it's InnoDB specific or a general MySQL issue, otherwise the bug can't be in the InnoDB category and have Verified status at the same time.  Could you check the DELETE IGNORE behavior with another DB engine that supports FK constraints? Falcon, maybe? Sorry for my ignorance that I don't know if falcon has the FK support yet.

Output with Falcon:

mysql> create table parent (id int primary key) engine = falcon;
Query OK, 0 rows affected (0.15 sec)

mysql> create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)) engine =
    -> falcon;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into parent values (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into child values (3), (5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> delete ignore from parent;
Query OK, 6 rows affected (0.00 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 | 
+-------------+
1 row in set (0.01 sec)

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

PBXT behaves in same way as InnoDB:

mysql> create table parent (id int primary key) engine = pbxt;
Query OK, 0 rows affected (0.16 sec)

mysql>  create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id)) engine =pbxt;
Query OK, 0 rows affected (0.19 sec)

mysql>  insert into parent values (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>  insert into child values (3), (5);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into child values (7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Constraint: `FOREIGN_1`)
mysql>  delete ignore from parent;
Query OK, 2 rows affected, 1 warning (0.11 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------+
| Error | 1451 | Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`) | 
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 | 
+-------------+
1 row in set (0.00 sec)

mysql>  select * from parent;
+----+
| id |
+----+
|  4 | 
|  5 | 
|  6 | 
+----+
3 rows in set (0.00 sec)

Sorry, was hury: PBXT behaves not in very same way like InnoDB. See https://bugs.launchpad.net/pbxt/+bug/379315 also

Setting to DML because looks that PBXT has somewhat similar behavior (does it really deletes one parent with FK?).

I'm not aware of any intentional change in IGNORE handling between 5.0 and 5.1.
If it did change, it is most likely a regression.

See also WL#4103 "Define ignore"

Marko, would you like to have a look?

Sorry, when I assigned the bug to Marko I didn't notice the bug was moved from the InnoDB category, unassigning.

triage: setting tag to CHECKED

Hi everybody,

I've verified this issue and in my understanding it's definitely a bug.

Scrolling through the code I've also seen that the IGNORE statement is correctly checked for multi-table deletes (at least in accordance with the documentation: "... ignore all errors during the process of deleting rows...").

A workaround for the given example using the multi-table code might be:
DELETE IGNORE p1, p2 FROM parent p1 INNER JOIN parent p2 ON (p1.id = p2.id);

As this is quite inconvenient I've created a patch against 5.1.41 (also works with .43) which correctly checks the IGNORE statement for single table deletes. 

The patch is working for me but should be double-checked by somebody with a deeper understanding of the code.

Best regards,
Moritz.

DELETE IGNORE statement fix for single table deletes

Attachment: mysql-fix-44987.patch (application/octet-stream, text), 789 bytes.

We decided years ago that inconsistency here is not a bug.

@Peter Gulutzan:

So you mean that DELETE IGNORE stopping on the first row that can't be deleted is a plausible behaviour? (see example in the original post)

Monty has alread fixed that MariaDB 5.1...

Regards,
Moritz

I don't evaluate "plausible", in fact I acknowledged there's inconsistency.
I do try to say that, as I interpreted matters, a decision was made that
would result in this matter being left as is. That may change, but no promises.