Description:
Given ON DELETE CASCADE relations between four tables a <- b <- c <- d and executing a "DELETE FROM a":
- All rows from a are deleted (expected)
- All rows from b are deleted (expected)
- Only the first row of c is deleted in case multiple rows of b reference to a single row from a (unexpected)
More specifically, given the following data:
- a: a_1
- b:
- b_1, references a_1
- b_2, references a_1
- b_3, references a_1
- c:
- c_1, references b_1
- c_2, references b_2
- c_3, references b_3
- d: no rows
After executing "delete from a", all rows are deleted, except for rows "c_2" and "c_3".
When setting innodb_native_foreign_keys = TRUE (or using MySQL 8.4), all rows are deleted, as expected.
Also, removing the foreign key d -> c resolves the problem and all rows are deleted. This is suspicious as the table d does not contain any rows, so the foreign key should not have any influence.
How to repeat:
Use the following code to set up the database:
CREATE TABLE `a` (
`pk_a` bigint NOT NULL,
`name` VARCHAR(32),
PRIMARY KEY (`pk_a`)
);
INSERT INTO `a` VALUES
(1,"a_1");
CREATE TABLE `b` (
`pk_b` bigint NOT NULL,
`fk_a` bigint NOT NULL,
`name` VARCHAR(32),
PRIMARY KEY (`pk_b`),
KEY `fk_a` (`fk_a`),
CONSTRAINT `fk_a` FOREIGN KEY (`fk_a`) REFERENCES `a` (`pk_a`) ON DELETE CASCADE
);
INSERT INTO `b` VALUES
(1,1,"a1_b1"),
(2,1,"a1_b2"),
(3,1,"a1_b3");
CREATE TABLE `c` (
`pk_c` bigint NOT NULL,
`fk_b` bigint NOT NULL,
`name` VARCHAR(32),
PRIMARY KEY (`pk_c`),
KEY `fk_b` (`fk_b`),
CONSTRAINT `fk_b` FOREIGN KEY (`fk_b`) REFERENCES `b`(`pk_b`) ON DELETE CASCADE
);
INSERT INTO `c` VALUES
(1,1,"a1_b1_c1"),
(2,2,"a1_b2_c2"),
(3,3,"a1_b3_c3");
CREATE TABLE `d` (
`pk_d` bigint NOT NULL AUTO_INCREMENT,
`fk_c` bigint NOT NULL,
PRIMARY KEY (`pk_d`),
KEY `fk_c` (`fk_c`),
CONSTRAINT `fk_c` FOREIGN KEY (`fk_c`) REFERENCES `c`(`pk_c`) ON DELETE CASCADE
);
Then, execute DELETE FROM a.
