Bug #100023 Foreign Key Update Cascade FAILS on 2+ Tables Where BOTH are Parent and Child
Submitted: 28 Jun 2020 16:31 Modified: 1 Jul 2020 12:00
Reporter: Brad Lanier Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: Foreign Key Update Cascade

[28 Jun 2020 16:31] Brad Lanier
Description:
MySQL 8.0 Reference Manual Section 13.1.20.5 FOREIGN KEY Constraints "Referential Actions" under "Cascade" states.... "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."

With the case which follows, the UPDATE FAILS. This case seems to follow the guidelines quoted above in the reference manual.

How to repeat:
Run the following script:
use test;
create table `roleType` (
	`name` varchar(25) NOT NULL,
	`baseName` varchar(25) NOT NULL,
	PRIMARY KEY(`name`),
	KEY `baseName` (`baseName`));
create table `baseRoleType` (
	`name` varchar(25) NOT NULL,
	PRIMARY KEY (`name`),
	CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
alter table `roleType`
	ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
SET @@foreign_key_checks = FALSE;
insert into baseRoleType (name) values ("Person");
SET @@foreign_key_checks = TRUE;
insert into RoleType (name, baseName) values ("Person","Person");
update roleType set name = "Person" where name = "Persons";

The UPDATE FAILS with the following error: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`roletype`, CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE)
[29 Jun 2020 8:32] MySQL Verification Team
Thank you for the bug report. I couldn't repeat:
Database changed
mysql> create table `roleType` (
    -> `name` varchar(25) NOT NULL,
    -> `baseName` varchar(25) NOT NULL,
    -> PRIMARY KEY(`name`),
    -> KEY `baseName` (`baseName`));
Query OK, 0 rows affected (0.03 sec)

mysql> create table `baseRoleType` (
    -> `name` varchar(25) NOT NULL,
    -> PRIMARY KEY (`name`),
    -> CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table `roleType`
    -> ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = FALSE;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into baseRoleType (name) values ("Person");
Query OK, 1 row affected (0.01 sec)

mysql> SET @@foreign_key_checks = TRUE;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into RoleType (name, baseName) values ("Person","Person");
Query OK, 1 row affected (0.01 sec)

mysql> update roleType set name = "Person" where name = "Persons";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show variables like "%version%";
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.20                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.20                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Win64                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
11 rows in set (0.01 sec)

mysql>

If you have additional information or something I missed please comment here, Thanks.
[29 Jun 2020 9:16] MySQL Verification Team
Checked on Linux, see the error in the test case:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0,17 sec)

mysql> USE test
Database changed
mysql> create table `roleType` (
    -> `name` varchar(25) NOT NULL,
    -> `baseName` varchar(25) NOT NULL,
    -> PRIMARY KEY(`name`),
    -> KEY `baseName` (`baseName`));
Query OK, 0 rows affected (1,04 sec)

mysql> create table `baseRoleType` (
    -> `name` varchar(25) NOT NULL,
    -> PRIMARY KEY (`name`),
    -> CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
ERROR 1824 (HY000): Failed to open the referenced table 'roletype'
mysql> create table `baseRoleType` ( `name` varchar(25) NOT NULL, PRIMARY KEY (`name`), CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
Query OK, 0 rows affected (0,64 sec)

mysql> alter table `roleType`
    -> ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
ERROR 1824 (HY000): Failed to open the referenced table 'baseroletype'
mysql> alter table `roleType` ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
ERROR 1824 (HY000): Failed to open the referenced table 'baseroleType'
mysql> alter table `roleType` ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseRoleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (1,93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = FALSE;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into baseRoleType (name) values ("Person");
Query OK, 1 row affected (0,10 sec)

mysql> SET @@foreign_key_checks = TRUE;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into RoleType (name, baseName) values ("Person","Person");
ERROR 1146 (42S02): Table 'test.RoleType' doesn't exist
mysql> insert into roleType (name, baseName) values ("Person","Person");
Query OK, 1 row affected (0,07 sec)

mysql> update roleType set name = "Person" where name = "Persons";
Query OK, 0 rows affected (0,00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>

mysql> SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.20                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.20                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
11 rows in set (0,00 sec)
[29 Jun 2020 12:49] Brad Lanier
Sorry....there was a mistake in my UPDATE statement in the test case.  Should read:
use test;
create table `test`.`roleType` (
	`name` varchar(25) NOT NULL,
	`baseName` varchar(25) NOT NULL,
	PRIMARY KEY(`name`),
	KEY `baseName` (`baseName`));
create table `test`.`baseRoleType` (
	`name` varchar(25) NOT NULL,
	PRIMARY KEY (`name`),
	CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `test`.`roleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
alter table `test`.`roleType`
	ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `test`.`baseRoleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
SET @@foreign_key_checks = FALSE;
insert into `test`.`baseRoleType` (`name`) values ("Person");
SET @@foreign_key_checks = TRUE;
insert into `test`.`RoleType` (`name`, `baseName`) values ("Person","Person");
update `test`.`roleType` set `name` = "Persons" where `name` = "Person";

Fails with:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`roletype`, CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE)
[29 Jun 2020 14:28] MySQL Verification Team
Hi Mr. Lanier,

You have a recursive constraint, with the UPDATE that can not work in this situation.

Can you please explain why do you think that it should work ????
[29 Jun 2020 17:18] Brad Lanier
First, the field to be updated in the original UPDATE of the first parent table is different than the field in the failing FK Constraint. The original field is `roleType`.`name`.  This causes a Cascade to the child `baseRoleType`.`name`. Then, `baseRoleType` becomes the parent Cascading to the now child `roleType`.`baseName`.

Please refer to the original Description section of this Bug report referencing the MySql 8.0 Reference Manual.  According to the documentation, this should work.  Making the case of why it is needed is easy enough to do, but the point of the Bug report is that the behavior where BOTH tables are Parents AND Children is not in keeping with the documentation.
[30 Jun 2020 12:42] MySQL Verification Team
Hi,

SQL standard clearly specifies that, if you utilise cyclic constraints, the only action that is supported is NO ACTION. No changes in those columns are allowed.

MySQL follows that standard. Hence, what we can do is make this a documentation improvement bug, since this is not mentioned anywhere in our Reference Manual.

Let us know if you agree with this resolution.
[30 Jun 2020 15:47] Brad Lanier
I agree.  Please refer to the original Description section of this Bug report referencing the MySql 8.0 Reference Manual....Section 13.1.20.5 FOREIGN KEY Constraints "Referential Actions" under "Cascade" states.... "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."

The way this is written, I was under the impression that this was describing behavior of MySql that was an extension to the standard.  Please address this section in particular, and if this is NOT in error, please make the text more clear as to the subject matter.

Please update Bug status
[1 Jul 2020 12:00] MySQL Verification Team
Thank you Mr. Lanier.

Verified as a documentation bug.