MySQL Bugs: #94780: ON DELETE SET NULL not working on MySQL 8

Bug #94780	ON DELETE SET NULL not working on MySQL 8
Submitted:	26 Mar 2019 12:44	Modified:	2 May 2019 14:12
Reporter:	Ionut Vasilache	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	MySQL 8, on delete set null

Description:
On MySQL 5.7, ON DELETE SET NULL works fine on a standalone installation on MacOS (local machine), on Docker (local) and also on AWS RDS. 

On MySQL 8.0.15, ON DELETE SET NULL / CASCADE does not work for the same script on a standalone installation on MacOS (local), neither on Docker (local environment or CI agents, MySQL 8.0 & 8.0.15). However, it works on AWS RDS (MySQL 8.0.13).

However, on a standalone installation on MacOS, if ALTERing (adding / removing a column) one of the two tables involved in the ON DELETE SET NULL operation, the FK is set to null, working as expected. On Docker, apart from adding / removing a column, it works also by changing the order of two random columns from one of those two tables involved in the ON DELETE SET NULL operation.

I have checked the engine status (SHOW ENGINE INNODB STATUS), the general and error logs, compared the variables (SHOW VARIABLES) from a working environment and also the data from INFORMATION_SCHEMA. Unfortunately, no clue after investigating these things.

Any help is more than appreciated. Please let me know if you need more details. Thank you!

How to repeat:
Steps to reproduce:
	1. Have a script that creates a database with more than 100 tables. Two of them are EMPLOYEES and PICTURES. Each record from EMPLOYEES can have a foreign key to a record from PICTURES.
	2. Specify ON DELETE SET NULL on the foreign key.  Please note that the FK accepts null values.
	3. Insert into PICTURES a record and INSERT into EMPLOYEES a record that has a reference to previously added row from PICTURES. Basically, an employee has a picture.
	4. DELETE the record from PICTURES. The record is deleted successfully.

Expected result:
	The EMPLOYEES record should have the PICTURES foreign key null.

Actual result:
	The PICTURES foreign key from EMPLOYEES is not affected at all by the DELETE operation. The value remains as it was (not null),  the database being in an inconsistent state, because that specific picture does not exist anymore.

Please note that I cannot post the entire script, but I will try to extract the things that I believe that matter:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `EMPLOYEES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `EMPLOYEES` (
	`KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
	`PROFILEIMAGEKEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN DEFAULT NULL,
	PRIMARY KEY (`KEY`),
	CONSTRAINT `EMPLOYEE_FK5` FOREIGN KEY (`PROFILEIMAGEKEY`) REFERENCES `PICTURES` (`KEY`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

DROP TABLE IF EXISTS `PICTURES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `PICTURES` (
  `KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
  PRIMARY KEY (`KEY`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
	
INSERT INTO `PICTURES` (`KEY`) VALUES ('123’);

INSERT INTO `EMPLOYEES` (`KEY`,`PROFILEIMAGEKEY`) VALUES ('abc', '123’);

DELETE FROM `PICTURES`;

SELECT * FROM `EMPLOYEES`;

Suggested fix:
As I said, so far, the only workaround would be to ADD and after that to DROP a column in each table, but considering the large number of tables (100+) and the fact that most probably new tables will be added in the future, this solution is to be avoided.

Hi,

Thank you for your bug report.

What happens if you first create Pictures and then Employees table ???

Hi,

Your script worked just fine for me. 

That SELECT on the end returns:

KEY	PROFILEIMAGEKEY
abc	NULL

Hi,

I managed to reproduce it with the following script that contains only 2 tables, both on a MySQL 8.0.15 standalone installation and by using Docker. Both were performed on MacOS.

This is the command for running MySQL 8.0.15 on Docker:
docker run -p 3306:3306 --restart=always --name mysql -e MYSQL_ROOT_PASSWORD=somepassword -d mysql:8.0.15 mysqld --default_authentication_plugin=mysql_native_password --sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"

This is the SQL script:

create schema test;
use test;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `EMPLOYEES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `EMPLOYEES` (
	`KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
	`PROFILEIMAGEKEY1` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN,
	PRIMARY KEY (`KEY`),
	CONSTRAINT `EMPLOYEE_FK1` FOREIGN KEY (`PROFILEIMAGEKEY1`) REFERENCES `PICTURES` (`KEY`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

DROP TABLE IF EXISTS `PICTURES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `PICTURES` (
  `KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
  PRIMARY KEY (`KEY`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
	
INSERT INTO `PICTURES` (`KEY`) VALUES ('1');

INSERT INTO `EMPLOYEES` (`KEY`,`PROFILEIMAGEKEY1`) VALUES ('abc', '1');
select * from `PICTURES`;
SELECT * FROM `EMPLOYEES`;
DELETE FROM `PICTURES`;

SELECT * FROM `EMPLOYEES`;

Please note that this script works fine on MySQL 5.7.x (standalone installation or Docker).

I discovered that the scenario does not reproduce if using the following script:

create schema test2;
use test2;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `PICTURES`;
CREATE TABLE `PICTURES` (
  `KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
  PRIMARY KEY (`KEY`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

DROP TABLE IF EXISTS `EMPLOYEES`;
CREATE TABLE `EMPLOYEES` (
	`KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
	`PROFILEIMAGEKEY1` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN,
	PRIMARY KEY (`KEY`),
	CONSTRAINT `EMPLOYEE_FK1` FOREIGN KEY (`PROFILEIMAGEKEY1`) REFERENCES `PICTURES` (`KEY`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
	
INSERT INTO `PICTURES` (`KEY`) VALUES ('1');

INSERT INTO `EMPLOYEES` (`KEY`,`PROFILEIMAGEKEY1`) VALUES ('abc', '1');
select * from `PICTURES`;
SELECT * FROM `EMPLOYEES`;
DELETE FROM `PICTURES`;

SELECT * FROM `EMPLOYEES`;

Basically, I removed /*!40101 SET NAMES UTF8 */; from the beginning of the script. 

Moreover, from table declarations, I removed:
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;

Apart from that, I also switched the creation order of the tables, as you suggested. Now the first table that is created is Pictures and after that I create Employees. I saw that without this switch, the defect still reproduces.

Considering the fact that the real database contains more than 100 tables and that mysqldump will not take care of the table creation order (they will be sorted alphabetically) or of removing the UTF8 stuff, the current workaround is not a feasible solution.

Also, please don't forget about the ALTER TABLE thing described in my first comment.

Thank you!

I managed to reproduce this bug also on a new AWS RDS instance (MySQL 8.0.15). The instance has been created from scratch.

Hi Ionut,

As I wrote, entity "Employees" has a foreign key on "Pictures" entity. Hence, "Pictures" have to be created first. That is on of the basic premises of the relational databases, described in many textbooks.

After creating each of the tables, you could run SHOW WARNINGS command. If you do not get any warnings, then, it is a very low severity server bug. If you do get any warning, then it is a documentation bug.

Please, let us know the results.

Tested on MySQL 8.0.16 (Docker) and the bug does not reproduce anymore. The bug was related to https://bugs.mysql.com/bug.php?id=94400.

I think this ticket can be closed.

Thank you for your support!

Thank you very much on your feedback.