Bug #41737 Foreign keys: failure if multi-update

• Submitted: 24 Dec 2008 20:48
• Modified: 20 Dec 2013 6:52
• Reporter: Peter Gulutzan
• Status: Won't fix
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 6.1.0-alpha-debug
• OS: Linux (SUSE 10.0 / 32-bit)
• CPU Architecture: Any

[24 Dec 2008 20:48] Peter Gulutzan

Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I use multi-table UPDATE.
I get a non-matching foreign-key row.

How to repeat:
drop table if exists t1;
drop table if exists t2;
create table t1 (s1 int, s2 int, primary key (s1,s2)) engine=falcon;
create table t2 (s1 int, s2 int, foreign key (s1,s2) references t1 (s1,s2) on update set null on delete set null) engine=falcon;
insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert into t2 select * from t1;
update t1,t2 set t1.s1 = 0, t2.s1 = t1.s1 where t1.s1 = 4 and t2.s1 = 4;
select * from t1;
select * from t2;
...
The results for the SELECT statements are:
mysql> select * from t1;
+----+----+
| s1 | s2 |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  0 |  4 |
|  5 |  5 |
+----+----+
5 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)

[24 Dec 2008 23:46] MySQL Verification Team

Thank you for the bug report. Verified as described:

c:\dbs>c:\dbs\6.1\bin\mysql -uroot --port=3361 --prompt="mysql 6.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.1.0-alpha-nt-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 6.1 > use test
Database changed
mysql 6.1 > drop table if exists t1;
Query OK, 0 rows affected (0.03 sec)

mysql 6.1 > drop table if exists t2;
Query OK, 0 rows affected (0.02 sec)

mysql 6.1 > create table t1 (s1 int, s2 int, primary key (s1,s2)) engine=falcon;
Query OK, 0 rows affected (0.06 sec)

mysql 6.1 > create table t2 (s1 int, s2 int, foreign key (s1,s2) references t1 (s1,s2) on update set
    -> null on delete set null) engine=falcon;
Query OK, 0 rows affected (0.14 sec)

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

mysql 6.1 > insert into t2 select * from t1;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 6.1 > update t1,t2 set t1.s1 = 0, t2.s1 = t1.s1 where t1.s1 = 4 and t2.s1 = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql 6.1 > select * from t1;
+----+----+
| s1 | s2 |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  0 |  4 |
|  5 |  5 |
+----+----+
5 rows in set (0.00 sec)

mysql 6.1 > select * from t2;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+
5 rows in set (0.00 sec)

mysql 6.1 >

[20 Dec 2013 6:52] Erlend Dahl

6.x project was abandoned years ago.