| Bug #83263 | ON DELETE constraints are not applied if multiple constraints exist | ||
|---|---|---|---|
| Submitted: | 5 Oct 2016 8:18 | Modified: | 27 Oct 2016 16:26 |
| Reporter: | Reguel Wermelinger | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.7.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[5 Oct 2016 8:18]
Reguel Wermelinger
[11 Oct 2016 14:15]
Geoff Raeder
I believe I'm running into the same situation. Deleting a parent "Job" record is not deleting all of it's related records. I have tables Job, JobPhaseCode, TimeBlock. JobPhaseCode has a foreign key to Job and set to ON CASCADE DELETE. TimeBlock has foreign keys to both Job and JobPhaseCode, and Job is configured to ON CASCADE DELETE, but JobPhaseCode is set to ON DELETE SET NULL. When I delete a Job, the associated JobPhaseCode record is deleted, but not the associated TimeBlock record. TimeBlock's foreign key on JobPhaseCode is set to NULL, but seems like it should be deleted given the Job was deleted. I'm running 5.7.15 on macOS 10.12. Dump file below: # ************************************************************ # Sequel Pro SQL dump # Version 4541 # # http://www.sequelpro.com/ # https://github.com/sequelpro/sequelpro # # Host: 127.0.0.1 (MySQL 5.7.15) # Database: CascadeTest # Generation Time: 2016-10-11 14:04:42 +0000 # ************************************************************ /*!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 */; /*!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 */; # Dump of table Job # ------------------------------------------------------------ DROP TABLE IF EXISTS `Job`; CREATE TABLE `Job` ( `id` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `created` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4), `modified` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), `metaDeleted` tinyint(1) NOT NULL DEFAULT '0', `metaSequence` int(11) DEFAULT NULL, `appCreated` datetime(4) NOT NULL, `appCreatorID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `jobNumber` varchar(200) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, `name` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `note` text, `city` varchar(60) DEFAULT NULL, `stateID` varchar(100) DEFAULT NULL, `countryID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `generalManagerID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, `startStamp` datetime(4) DEFAULT NULL, `isClosed` tinyint(1) NOT NULL DEFAULT '0', `closedDate` datetime(4) DEFAULT NULL, `isCertified` tinyint(1) DEFAULT NULL, `timeZoneName` varchar(100) DEFAULT NULL, `stateOther` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), KEY `metaDeleted` (`metaDeleted`), KEY `metaSequence` (`metaSequence`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; LOCK TABLES `Job` WRITE; /*!40000 ALTER TABLE `Job` DISABLE KEYS */; INSERT INTO `Job` (`id`, `created`, `modified`, `metaDeleted`, `metaSequence`, `appCreated`, `appCreatorID`, `jobNumber`, `name`, `note`, `city`, `stateID`, `countryID`, `generalManagerID`, `startStamp`, `isClosed`, `closedDate`, `isCertified`, `timeZoneName`, `stateOther`) VALUES ('js-1234','2016-10-11 10:02:10.9212','2016-10-11 10:02:10.9212',0,NULL,'2016-10-11 10:02:10.0000','00000000-0000-0000-0000-000000000000','js-1234',NULL,NULL,NULL,'4-NH',NULL,NULL,NULL,0,NULL,NULL,NULL,NULL); /*!40000 ALTER TABLE `Job` ENABLE KEYS */; UNLOCK TABLES; # Dump of table JobPhaseCode # ------------------------------------------------------------ DROP TABLE IF EXISTS `JobPhaseCode`; CREATE TABLE `JobPhaseCode` ( `id` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `created` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4), `modified` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), `metaDeleted` tinyint(1) NOT NULL DEFAULT '0', `metaSequence` int(11) DEFAULT NULL, `appCreated` datetime(4) NOT NULL, `appCreatorID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `name` varchar(100) NOT NULL, `jobID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, PRIMARY KEY (`id`), KEY `jobID` (`jobID`), CONSTRAINT `FK_JobPhaseCode_jobID` FOREIGN KEY (`jobID`) REFERENCES `Job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; LOCK TABLES `JobPhaseCode` WRITE; /*!40000 ALTER TABLE `JobPhaseCode` DISABLE KEYS */; INSERT INTO `JobPhaseCode` (`id`, `created`, `modified`, `metaDeleted`, `metaSequence`, `appCreated`, `appCreatorID`, `name`, `jobID`) VALUES ('jpc-1234','2016-10-11 10:02:16.4279','2016-10-11 10:02:16.4279',0,NULL,'2016-10-11 10:02:16.0000','00000000-0000-0000-0000-000000000000','test phase code','js-1234'); /*!40000 ALTER TABLE `JobPhaseCode` ENABLE KEYS */; UNLOCK TABLES; # Dump of table TimeBlock # ------------------------------------------------------------ CREATE TABLE `TimeBlock` ( `id` varchar(100) NOT NULL, `created` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4), `modified` datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4), `metaDeleted` tinyint(1) NOT NULL DEFAULT '0', `metaSequence` int(11) DEFAULT NULL, `appCreated` datetime(4) NOT NULL, `appCreatorID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `hours` decimal(16,4) DEFAULT NULL, `jobID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL, `jobPhaseCodeID` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `metaSequence` (`metaSequence`), KEY `FK_TimeBlock_jobPhaseCodeID` (`jobPhaseCodeID`), KEY `FK_TimeBlock_jobID` (`jobID`), CONSTRAINT `FK_TimeBlock_jobID` FOREIGN KEY (`jobID`) REFERENCES `Job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_TimeBlock_jobPhaseCodeID` FOREIGN KEY (`jobPhaseCodeID`) REFERENCES `JobPhaseCode` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; LOCK TABLES `TimeBlock` WRITE; /*!40000 ALTER TABLE `TimeBlock` DISABLE KEYS */; INSERT INTO `TimeBlock` (`id`, `created`, `modified`, `metaDeleted`, `metaSequence`, `appCreated`, `appCreatorID`, `hours`, `jobID`, `jobPhaseCodeID`) VALUES ('tb-1234','2016-10-11 10:02:21.9749','2016-10-11 10:02:21.9749',0,NULL,'2016-10-11 10:02:21.0000','00000000-0000-0000-0000-000000000000',41.0000,'js-1234','jpc-1234'); /*!40000 ALTER TABLE `TimeBlock` ENABLE KEYS */; UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_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 */;
[21 Oct 2016 15:59]
MySQL Verification Team
Hi! Thank you for your bug report. However, I have to get from you some additional data. First and first, I do not see, in your test case, any DELETE that does not produce the desired effect of the cascading DELETEs. Second, have you tried not using DISABLE/ENABLE keys and LOCK / UNLOCK ??? Did it produce the same results ???? Third and final question. Have you tried not inserting NULLs into the columns which are involved in the constraints on all tables ???? We need your responses before we can proceed further on.
[26 Oct 2016 8:26]
Reguel Wermelinger
FIRST: The delete statement I execute is as follows DELETE FROM `application` where id = 2; SELECT * FROM user where id = 3 /*should not return any result - but returns a row !!!*/ SECOND: Yes, we also tried without DISABLE/ENABLE keys and LOCK / UNLOCK. The result is the same. THIRD: The DELETE statement works if ON DELETE is set to CASCADE for both of the foreign keys, if one is set to CASCADE and the other is set to NO ACTION or to RESTRICT the following error appears: SQL Error(1451): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user`, CONSTRAINT `FK_user_application` FOREIGN KEY (`app_lastUsed`) REFERENCES `application` (`id`))
[27 Oct 2016 16:26]
MySQL Verification Team
Hi, I have ran the script that is very similar to yours and got the same result. This is a script: -- Dumping structure for table test.application CREATE TABLE IF NOT EXISTS `application` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `application` (`id`, `name`) VALUES (1, 'hey first app'), (2, 'second application'); CREATE TABLE IF NOT EXISTS `user` (\ `id` int(10) NOT NULL AUTO_INCREMENT,\ `name` varchar(50) DEFAULT '0',\ `app_origin` int(11) DEFAULT '0',\ `app_lastUsed` int(11) DEFAULT '0',\ PRIMARY KEY (`id`),\ UNIQUE KEY `app_origin` (`app_origin`), KEY `FK_user_application` (`app_lastUsed`),\ CONSTRAINT `FK__application` FOREIGN KEY (`app_origin`) REFERENCES `application` (`id`) ON DELETE CASCADE, CONSTRAINT `FK_user_application` FOREIGN KEY (`app_lastUsed`) \ REFERENCES `application` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; INSERT INTO `user` (`id`, `name`, `app_origin`, `app_lastUsed`) VALUES (1, 'myself', 1, 1), (3, 'other', 2, 2); DELETE FROM `application` where id = 2; SELECT * FROM user where id = 3; DROP TABLE IF EXISTS `user`; DROP TABLE IF EXISTS `application`;
[28 Oct 2016 6:29]
Marko Mäkelä
Posted by developer: We do not currently make any effort to detect conflicting ON actions for FOREIGN KEY constraints. Maybe in the reported case you could argue that the ON DELETE CASCADE should take precedence over ON DELETE SET NULL when the referenced record in the parent table is deleted. But what if we had ON UPDATE CASCADE and ON UPDATE SET NULL, and the parent key was updated? What should the child key be updated to? NULL or the new value? It seems to me that we should simply refuse to create multiple foreign key constraints that refer to the same table (columns) and specify conflicting actions.
[16 Nov 2016 19:05]
Paul Oppenheim
Also running into this. Here is a different test case, it does not require multiple columns to be on the same table, each constraint can be on a different table. DROP DATABASE IF EXISTS sql_test ; CREATE DATABASE sql_test ; USE sql_test ; CREATE TABLE a ( a_id int NOT NULL, PRIMARY KEY (a_id) ) ; CREATE TABLE b ( b_id int NOT NULL, a_id int NOT NULL, FOREIGN KEY(a_id) REFERENCES a (a_id) ON DELETE CASCADE, PRIMARY KEY (b_id) ) ; CREATE TABLE c ( c_id int NOT NULL, a_id int NOT NULL, b_id int, FOREIGN KEY(a_id) REFERENCES a (a_id) ON DELETE CASCADE, FOREIGN KEY(b_id) REFERENCES b (b_id) ON DELETE SET NULL, PRIMARY KEY (c_id) ) ; INSERT INTO a (a_id) VALUES ( 1 ) , ( 2 ) ; INSERT INTO b (b_id, a_id) VALUES ( 1 , 1 ) , ( 2 , 1 ) ; INSERT INTO c (c_id, a_id, b_id) VALUES ( 1 , 1 , 1 ) , ( 2 , 1 , 2 ) ; SELECT * FROM a ; SELECT * FROM b ; SELECT * FROM c ; SELECT '========================================' ; DELETE FROM a WHERE a_id = 1 ; SELECT * FROM a ; SELECT * FROM b ; SELECT * FROM c ;
[28 Feb 2017 14:09]
Sandro Hopf
Hi, I experienced the same behaviour. The requiredments to reproduce are the following: Setup: - Table A (id, name) - Table B (created_by, updated_by) - Constraint on updated_by referenceing a.id with ON DELETE CASCADE ON UPDATE NO ACTIONS - Constraint on created_by referenceing a.id with ON DELETE SET NULL ON UPDATE NO ACTIONS Requirements: - the ON DELETE CASCADE constraint is defined before the ON DELETE SET NULL constraint - one record on Table B referencing the same user for both columns(updated_by/created_by) I am using the following snipped to reproduce it(tested on http://rextester.com/JRK5385): DROP TABLE IF EXISTS `yyyyyyyyyyyyyy`; DROP TABLE IF EXISTS `xxxxxxxxxxxxx`; CREATE TABLE `xxxxxxxxxxxxx` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `yyyyyyyyyyyyyy` ( `id` int(11) NOT NULL, `created_by` int(11) DEFAULT NULL, `updated_by` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk1_idx` (`created_by`), KEY `fk2_idx` (`updated_by`), CONSTRAINT `fk2` FOREIGN KEY (`updated_by`) REFERENCES `xxxxxxxxxxxxx` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION, CONSTRAINT `fk1` FOREIGN KEY (`created_by`) REFERENCES `xxxxxxxxxxxxx` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB; INSERT INTO `xxxxxxxxxxxxx` VALUES (1,'1'),(2,NULL); INSERT INTO `yyyyyyyyyyyyyy` VALUES (1,1,1),(2,1,2),(3,2,1),(4,2,2); DELETE FROM xxxxxxxxxxxxx where id = 2; SELECT * FROM yyyyyyyyyyyyyy;
[19 Mar 2018 10:22]
Sandro Hopf
It seems, that MySQL version 5.7.21 is fixing this issue, but this ticket is still in status Verified. Could you verify and close it if it is so that we have the clarity that this is resolved?
[30 Jun 2021 7:40]
lalit Choudhary
with given test case and test with 5.7.33
I don't see the issue described issue.
master [localhost] {msandbox} (foo) > select * from xxxxxxxxxxxxx;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | NULL |
+----+------+
2 rows in set (0.00 sec)
master [localhost] {msandbox} (foo) > select * from yyyyyyyyyyyyyy;
+----+------------+------------+
| id | created_by | updated_by |
+----+------------+------------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
+----+------------+------------+
4 rows in set (0.00 sec)
master [localhost] {msandbox} (foo) > DELETE FROM xxxxxxxxxxxxx where id = 2;
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (foo) > select * from xxxxxxxxxxxxx;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
master [localhost] {msandbox} (foo) > select * from yyyyyyyyyyyyyy;
+----+------------+------------+
| id | created_by | updated_by |
+----+------------+------------+
| 1 | 1 | 1 |
| 2 | 1 | NULL |
+----+------------+------------+
2 rows in set (0.00 sec)
