Bug #43519 Foreign keys: no error with RESTRICT and multi-table update

• Submitted: 9 Mar 2009 23:32
• 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 | 32-bit)
• CPU Architecture: Any

[9 Mar 2009 23:32] Peter Gulutzan

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

I create a primary-key table.
I create a foreign-key table with an ON UPDATE RESTRICT clause.
I do a multi-table update on both tables.
It succeeds, presumably because at end of statement all is well.
But this is a RESTRICT, so it should have failed before statement end.

See also
Bug #41737  	Foreign keys: failure if multi-update

How to repeat:
How to repeat:

DROP TABLE IF EXISTS t2,t1;
SET @@storage_engine=falcon;
CREATE TABLE t1 (s1 CHAR PRIMARY KEY);
CREATE TABLE t2 (s1 CHAR, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE RESTRICT);
INSERT INTO t1 VALUES ('a');
INSERT INTO t2 VALUES ('a');
UPDATE t1, t2 SET t1.s1 = 'b', t2.s1 = 'b';
UPDATE t2, t1 SET t2.s1 = 'c', t1.s1 = 'c';
DROP TABLE IF EXISTS t2,t1;

[10 Mar 2009 1:10] MySQL Verification Team

Thank you for the bug report. Verified as described:

c:\dbs\6.1>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.1.0-alpha-Win x64 revno 2705 Source distribution

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

mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> SET @@storage_engine=falcon;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 (s1 CHAR PRIMARY KEY);
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE t2 (s1 CHAR, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE RESTRICT);
Query OK, 0 rows affected (0.48 sec)

mysql> INSERT INTO t1 VALUES ('a');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO t2 VALUES ('a');
Query OK, 1 row affected (0.05 sec)

mysql> UPDATE t1, t2 SET t1.s1 = 'b', t2.s1 = 'b';
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE t2, t1 SET t2.s1 = 'c', t1.s1 = 'c';
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected (0.14 sec)

mysql>

[20 Dec 2013 6:52] Erlend Dahl

6.x project was abandoned years ago.