| Bug #41700 | Foreign keys: failure with update cascade and duplicate key | ||
|---|---|---|---|
| Submitted: | 23 Dec 2008 0:40 | Modified: | 20 Dec 2013 6:53 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 6.1.0-alpha-debug | OS: | Linux (SUSE 10.0 / 32-bit) |
| Assigned to: | CPU Architecture: | Any | |
[23 Dec 2008 14:02]
MySQL Verification Team
Thank you for the bug report. Verified as described:
mysql> INSERT INTO t1 VALUES ('A','B') ON DUPLICATE KEY UPDATE s1 = 'C', s2 = 'D';
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM t2 WHERE s1 = 'C' and s2 = 'D'; /* count(*) is 0. wrong. */
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> DROP TABLE IF EXISTS t2,t1;
[20 Dec 2013 6:53]
Erlend Dahl
6.x project was abandoned years ago.
Description: I'm using mysql-6.1-fk. I start the server with mysqld --foreign-key-all-engines=1. I create a primary-key table and a foreign-key table. I use an INSERT ... ON DUPLICATE KEY ... statement. The result isn't what I'd get with InnoDB without --foreign-key-all-engines=1. How to repeat: SET @@storage_engine=falcon; DROP TABLE IF EXISTS t2,t1; CREATE TABLE t1 (s1 CHAR(3) NOT NULL UNIQUE, s2 CHAR(3) NOT NULL UNIQUE); CREATE TABLE t2 (s1 CHAR(3), s2 CHAR(3), FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE, FOREIGN KEY (s2) REFERENCES t1 (s2) ON UPDATE CASCADE); INSERT INTO t1 VALUES ('a','a'); INSERT INTO t2 VALUES ('a','a'); INSERT INTO t1 VALUES ('A','B') ON DUPLICATE KEY UPDATE s1 = 'C', s2 = 'D'; SELECT COUNT(*) FROM t2 WHERE s1 = 'C' and s2 = 'D'; /* count(*) is 0. wrong. */ DROP TABLE IF EXISTS t2,t1;