MySQL Bugs: #89696: Cyclic dependencies are not resolved properly with cascade removal

Bug #89696	Cyclic dependencies are not resolved properly with cascade removal
Submitted:	16 Feb 2018 13:13	Modified:	1 Jun 2018 12:39
Reporter:	Andrei Anishchenko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.21	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When there are tables that have a circular reference to each other, cascade removal fails in 5.7.21 with "Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`cascade`.`table1`, CONSTRAINT `FK_table1_to_table2` FOREIGN KEY (`table2_id`) REFERENCES `table2` (`id`))". 

With prior versions (at least 5.7.20 and 5.7.17 have been tried out) this issue does not manifest itself, and removal goes through fine.

How to repeat:
DROP SCHEMA IF EXISTS `cascade`;
CREATE SCHEMA `cascade`;
USE `cascade`;

CREATE TABLE table1 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  table2_id bigint(20) DEFAULT NULL,
  `data` varchar(64) NOT NULL,
  PRIMARY KEY (id),
  KEY FK_table1_to_table2 (table2_id)
);

CREATE TABLE `table2` (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  table1_id bigint(20) NOT NULL,
  PRIMARY KEY (id),
  KEY FK_table2_to_table1 (table1_id),
  CONSTRAINT FK_table2_to_table1 FOREIGN KEY (table1_id) REFERENCES table1 (id) ON DELETE CASCADE
);

ALTER TABLE table1 ADD CONSTRAINT FK_table1_to_table2 FOREIGN KEY (table2_id) REFERENCES table2 (id);

INSERT INTO table1 (`data`) VALUES ('data1');

INSERT INTO table2 (table1_id) VALUES (last_insert_id());

UPDATE table1 SET table2_id = (last_insert_id());

# fails on 5.7.21, works fine on 5.7.20 and 5.7.17
DELETE FROM table1;

Hi!

I have run your scripts and I get no error what so ever !!!!!

Have you set any potential configuration option that prevented DMLs ???

This is a script that I have ran:

DROP SCHEMA IF EXISTS `cascade`;
CREATE SCHEMA `cascade`;
USE `cascade`;

DROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;

CREATE TABLE table1 (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  table2_id bigint(20) DEFAULT NULL,
  `data` varchar(64) NOT NULL,
  PRIMARY KEY (id),
  KEY FK_table1_to_table2 (table2_id)
);

CREATE TABLE `table2` (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  table1_id bigint(20) NOT NULL,
  PRIMARY KEY (id),
  KEY FK_table2_to_table1 (table1_id),
  CONSTRAINT FK_table2_to_table1 FOREIGN KEY (table1_id) REFERENCES table1 (id) ON DELETE CASCADE
);

ALTER TABLE table1 ADD CONSTRAINT FK_table1_to_table2 FOREIGN KEY (table2_id) REFERENCES table2 (id);

INSERT INTO table1 (`data`) VALUES ('data1');

INSERT INTO table2 (table1_id) VALUES (last_insert_id());

UPDATE table1 SET table2_id = (last_insert_id());

# it works fine on 5.7.21, works fine on 5.7.20 and 5.7.17 !!!!
DROP SCHEMA IF EXISTS `cascade`;

Hi Sinisa,

Thank you for getting back to me on this one.
As for your SQL script, you drop the schema. I recognize that this might work. However, I am not interested in schema removal at all, and this is not what this bug report is about. 

It is about foreign keys having this ability of cascade removal of all records in the dependent tables if a record in the "parent" table is removed. In my script I create two tables, hook them up by two foreign keys pointing in opposite directions, thus creating a cyclic dependency while making one of them to have cascade delete. Pretty basic stuff really, I'd even say fundamental one, yet in 5.7.21 it fails, and only in this version.

Hi!

If table1 and table2 are both child and parent to each other, then you can not drop only one at a time.

SQL 2011 standard, in its feature F311, allows only schema to be dropped when there is a circular reference.

The best we could do is to allow dropping both tables simultaneously with:

DROP TABLE table1, table2;

which would make this a feature request. I do not know whether it would be accepted as it clearly violates the SQL standard.

Hi,

I don't understand why do you insist on *dropping* anything. I don't not drop schema, nor do I drop tables. I want for them both to remain. We are talking *deleting* records from one table here, so that dependent *records* in the other table would be deleted automatically, by the virtue of cascade removal.

Could you please execute the script I provided in the original message, without modifying it in ways that completely change its intended logic?

Hi!

Deleting rows from tables, that would lead to circular deletion is also prohibited.

Well, I don't know about that. Like I said, it worked with 5.7.20 and 5.7.17, and I bet with all the versions in between, and all the versions prior to that. Seems to me that it should work also in 5.7.21, otherwise it's a regression bug.

Hi!

There were many changes in 5.6 and in 5.7 on this matter. After lot's of discussions, seems that DELETE must pass, in this test case, that you have provided.

Verified as a regression bug.

Thank you, I appreciate that.

Posted by developer:
 
The "Using Foreign Key Constraints" section has been updated to include the following information:

If a FOREIGN KEY clause is defined on both tables in a foreign key
relationship, making both tables a parent and child, an ON UPDATE CASCADE
or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be
defined for the other in order for cascading operations to succeed. If an
ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one
FOREIGN KEY clause, cascading operations fail with an error.

https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

The change should appear onlilne soon.

Thank you for the bug report.