Bug #72574 Foreign keys do not work as expected
Submitted: 8 May 2014 13:57 Modified: 8 May 2014 20:01
Reporter: Pavel Nekrasov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5.34-0ubuntu0.12.04.1 OS:Linux (Ubuntu 12.04.3 LTS)
Assigned to: CPU Architecture:Any

[8 May 2014 13:57] Pavel Nekrasov
Description:
Hello,

I have some InnoDB tables created with FOREIGN KEY ... ON DELETE CASCADE ON UPDATE CASCADE.

Deleting a row in parent table causes the loss of extra lines in child tables.

See example in "How to repeat".

Checked using mysql 5.1.66 and 5.5.34.

How to repeat:

I have 3 tables with foreign keys:

CREATE TABLE t1 (
    c1 INT UNSIGNED PRIMARY KEY NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE t2 (
    c1 INT UNSIGNED NOT NULL,
    c2 INT UNSIGNED NOT NULL,
    KEY (c2),
    FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE t3 (
    c1 INT UNSIGNED NOT NULL,
    c2 INT UNSIGNED NOT NULL,
    c3 INT UNSIGNED NOT NULL,
    FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (c2) REFERENCES t2(c2) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Insert some dummy data:

INSERT INTO t1 (c1) VALUES (1),(2);
INSERT INTO t2 (c1, c2) VALUES (1, 10),(2, 10);
INSERT INTO t3 (c1, c2, c3) VALUES (1, 10, 100),(2, 10, 100);

Check the result:

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;

mysql> SELECT * FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+----+----+
| c1 | c2 |
+----+----+
|  1 | 10 |
|  2 | 10 |
+----+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t3;
+----+----+-----+
| c1 | c2 | c3  |
+----+----+-----+
|  1 | 10 | 100 |
|  2 | 10 | 200 |
+----+----+-----+
2 rows in set (0.00 sec)

Delete the first row from t1:

DELETE FROM t1 WHERE c1=1;

Now I expect to find one row in table t3, but t3 is empty!

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;

mysql> SELECT * FROM t1;
+----+
| c1 |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+----+----+
| c1 | c2 |
+----+----+
|  2 | 10 |
+----+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM t3;
Empty set (0.00 sec)
[8 May 2014 20:01] MySQL Verification Team
Thank you for the bug report. That's expected behavior with 2nd FOREIGN KEY (c2) REFERENCES t2(c2) ON DELETE CASCADE ON UPDATE CASCADE on t3 which could cause cycles or multiple cascade paths.