Description:
I have a table "client". I have two other tables: "client_user" and "client_record". Both tables have a foreign key with ON DELETE CASCADE to the "client" table. I have a fourth table "client_record_child" which has a CASCADE DELETE foreign key relationship to "client_record" and a CASCADE SET NULL foreign key relationship to "client_user".
When I delete a record from the table "client" I would expect any "client_record" records associated with the client to be deleted AND any "client_record_child" records associated with those "client_record" records to ALSO be deleted.
What currently happens is any records in "client_record_child" associated with "client_record" records that are associated with the "client" record AND also has a relationship with a "client_user" record that is also associated with the "client" record are NOT deleted but their association to the "client_user" record IS set to null.
I suspect this bug is related to https://bugs.mysql.com/bug.php?id=85605 although this bug is reported in a slightly different scenario.
How to repeat:
##BACKGROUND
create table client (
`id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
create table client_user (
`id` int(10) unsigned NOT NULL,
`client_id` int(10) unsigned NOT NULL,
`user_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `client_user_fk` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
create table client_record (
`id` int(10) unsigned NOT NULL,
`client_id` int(10) unsigned NOT NULL,
`created_by_id` int(10) unsigned DEFAULT NULL,
`some_text_value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `client_record_fk` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE CASCADE,
CONSTRAINT `cr_created_by_fk` FOREIGN KEY (`created_by_id`) REFERENCES `client_user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;
create table client_record_child (
`id` int(10) unsigned NOT NULL,
`client_record_id` int(10) unsigned NOT NULL,
`created_by_id` int(10) unsigned DEFAULT NULL,
`some_text_value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `client_record_child_fk` FOREIGN KEY (`client_record_id`) REFERENCES `client_record` (`id`) ON DELETE CASCADE,
CONSTRAINT `crc_created_by_fk` FOREIGN KEY (`created_by_id`) REFERENCES `client_user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;
insert into client (id, name) values (1, 'Acme Ltd');
insert into client_user (id, client_id, user_name) values (1, 1, 'john_smith');
insert into client_record (id, client_id, created_by_id, some_text_value) values (1, 1, 1, 'Lorem Ipsum');
insert into client_record_child (id, client_record_id, created_by_id, some_text_value) values (1, 1, 1, 'Lorem Ipsum');
##FAILING TEST CASE
delete from client where id=1;
select * from client_record_child; #Should be Empty
check table client_record_child; #Should report no corruptions
select * from client_record_child where client_record_id = 1; #Is Empty
select * from client_record_child use index () where client_record_id = 1; #Is Not Empty
