Inconsistent behavior of DELETE IGNORE and FK constraint

Reported by svetasmirnova on 2009-05-22
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
PBXT
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

lp:~vkolesnikov/pbxt/pbxt-bug-379315
PBXT Core: Pending requested 2009-06-23

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

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

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  Edit
Everyone can see this information.

Other bug subscribers