Bug #86774 DELETE CASCADE on record with another DELETE SET NULL results in corrrupt tables
Submitted: 21 Jun 2017 10:26 Modified: 21 Jun 2017 17:35
Reporter: William Arslett Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[21 Jun 2017 10:26] William Arslett
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
[21 Jun 2017 17:35] MySQL Verification Team
Thank you for the bug report. Probably duplicate/related to mentioned bug https://bugs.mysql.com/bug.php?id=85605.