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 Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:6.1.0-alpha-debug OS:Linux (SUSE 10 | 32-bit)
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.