| Bug #41761 | Foreign keys: integrity breach if UPDATE and non-transactional+self-referencing | ||
|---|---|---|---|
| Submitted: | 27 Dec 2008 16:06 | Modified: | 30 Jan 2009 18:31 |
| Reporter: | Dmitry Lenev | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 6.1.0-alpha-debug | OS: | Any |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[27 Jan 2009 11:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64114 2698 Dmitry Lenev 2009-01-27 Tentative patch that fixes bug #41761 "Foreign keys: integrity breach if UPDATE and non-transactional+self-referencing" and implements support of insertion/removal of self-referencing rows to non-transactional table. The problem was that in --foreign-key-all-engines mode one was able to create row with a dangling reference in a non-transactional table with a self-referencing foreign key. This has happened when one updated existing row in such table and its primary key was set to some new value while row's foreign key was set to the old value of primary key. This problem stemmed from the fact that foreign key checks for such tables, which happen before operation on the row is executed, didn't take into account contents of the row which is about to be changed. This fix implements extra checks which do so and also allow to support addition/removal of self-referencing rows to such tables. Questions for reviewer is marked by QQ.
[30 Jan 2009 13:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64634 2703 Dmitry Lenev 2009-01-30 Patch that fixes bug #41761 "Foreign keys: integrity breach if UPDATE and non-transactional+self-referencing" and implements support of insertion/removal of self-referencing rows to non-transactional table. The problem was that in --foreign-key-all-engines mode one was able to create row with a dangling reference in a non-transactional table with a self-referencing foreign key. This has happened when one updated existing row in such table and its primary key was set to some new value while row's foreign key was set to the old value of primary key. This problem stemmed from the fact that foreign key checks for such tables, which happen before operation on the row is executed, didn't take into account contents of the row which is about to be changed. This fix implements extra checks which do so and also allow to support addition/removal of self-referencing rows to such tables.
[30 Jan 2009 18:31]
Dmitry Lenev
Fix for this bug was pushed into mysql-6.1-fk tree. Since this bug was reported against tree which is not publicly available yet I am simply closing this bug report.
Description: I'm using mysql-6.1-fk. I start the server with mysqld --foreign-key-all-engines=1. UPDATE statement on non-transactional table with self-referencing foreign key can break referential integrity. Note that although we don't guarantee fully standard behavior for foreign keys on non-transactional tables we still strive for avoiding situations in which referential integrity is compromised. How to repeat: create table t1 (pk int primary key, fk int references t1 (pk) on update restrict on delete restrict) engine=myisam; insert into t1 values (1, NULL), (2, 1); # UPDATE should fail with FK violation error but it is not... update t1 set pk=3, fk= 2 where pk = 2; # SELECT demonstrates that referential integrity is compromised select * from t1; #pk fk #1 NULL #3 2