Inconsistent behavior of DELETE IGNORE and FK constraint

Bug #379315 reported by svetasmirnova
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
PBXT
Fix Committed
Undecided
Vladimir Kolesnikov

Bug Description

PBXT reports error when FK constraint fails and DELETE IGNORE is used, but deletes one of rows which should not be deleted.

How to repeat:

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)

See also http://bugs.mysql.com/bug.php?id=44987

Related branches

Revision history for this message
Vladimir Kolesnikov (vkolesnikov) wrote :

Hi Sveta,

thanks for the report. I can confirm this. I think we should make it work like in InnoDB - does it make sense?

Changed in pbxt:
assignee: nobody → Vladimir Kolesnikov (vkolesnikov)
status: New → Confirmed
Revision history for this message
svetasmirnova (sveta-sun) wrote :

Hi Vladimir,

in general I agree, but probably would be better to coordinate with MySQL (especially with Runtime team who implements foreign keys for all engines) and InnoDB development as well.

Revision history for this message
svetasmirnova (sveta-sun) wrote :

Really we have private worklog #4103 about how DELETE IGNORE should behave.

Idea in this worklog is "skip the row, no change, move to the next row", so last SELECT should return:

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

Changed in pbxt:
status: Confirmed → In Progress
Revision history for this message
Vladimir Kolesnikov (vkolesnikov) wrote :

So far the fix is to rutn off the IGNORE option.

Changed in pbxt:
status: In Progress → Fix Committed
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.