| Bug #65140 | Infinite loop when updating a table record with cyclical Foreign Keys | ||
|---|---|---|---|
| Submitted: | 28 Apr 2012 1:29 | Modified: | 2 Jun 2012 20:10 |
| Reporter: | David Stavisski | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.4 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Foreign Key Infinite Loop | ||
[28 Apr 2012 6:05]
Valeriy Kravchuk
Had you really tried with 5.6.5? This is what I see with current code:
mysql> select version();
+----------------+
| version() |
+----------------+
| 5.6.6-m9-debug |
+----------------+
1 row in set (0.00 sec)
mysql> update attr set descr='Name 2' where name='OBJECT.Name';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show create table attr\G
*************************** 1. row ***************************
Table: attr
Create Table: CREATE TABLE `attr` (
`class` varchar(45) NOT NULL,
`name` varchar(45) NOT NULL,
`descr` varchar(45) NOT NULL,
`dependon` varchar(45) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
PRIMARY KEY (`name`),
KEY `FK_attr_class` (`class`),
KEY `FK_attr_type` (`type`),
CONSTRAINT `FK_attr_class` FOREIGN KEY (`class`) REFERENCES `class` (`name`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `FK_attr_type` FOREIGN KEY (`type`) REFERENCES `type` (`name`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[30 Apr 2012 17:37]
David Stavisski
Yes I tried it on 5.6.4 and 5.6.5 (upto 5.6.3 it worked fine). I actually built a test CentOS image with a clean MySQL install just to diagnose it
[2 May 2012 20:10]
Sveta Smirnova
Thank you for the feedback. I can not repeat described behavior too. Please send us your configuration file and inform us which exact MySQL package (file name you downloaded) you use.
[3 Jun 2012 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I have a schema that contains a cyclical foreign key chain. After upgrading to 5.6.4/5.6.5 (from 5.6.3) I noticed that when one of the table record is updated (any field) the query runs forever. Killing the db thread does not work, mysqld does not shutdown - the only option is kill -9 of mysqld. How to repeat: I create a simple schema that repeats the problem. Below is sqldump. After creating the DB, just run the following sql: update attr set descr='Name 2' where name='OBJECT.Name'; -- MySQL Administrator dump 1.4 -- -- ------------------------------------------------------ -- Server version 5.6.5-m8 /*!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_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' */; -- -- Create schema test -- CREATE DATABASE IF NOT EXISTS test; USE test; DROP TABLE IF EXISTS `attr`; CREATE TABLE `attr` ( `class` varchar(45) NOT NULL, `name` varchar(45) NOT NULL, `descr` varchar(45) NOT NULL, `dependon` varchar(45) DEFAULT NULL, `type` varchar(45) DEFAULT NULL, PRIMARY KEY (`name`), KEY `FK_attr_class` (`class`), KEY `FK_attr_type` (`type`), CONSTRAINT `FK_attr_class` FOREIGN KEY (`class`) REFERENCES `class` (`name`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `FK_attr_type` FOREIGN KEY (`type`) REFERENCES `type` (`name`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40000 ALTER TABLE `attr` DISABLE KEYS */; INSERT INTO `attr` (`class`,`name`,`descr`,`dependon`,`type`) VALUES ('object','OBJECT.Category','Category',NULL,'text'), ('root','OBJECT.Description','Description',NULL,'text'), ('root','OBJECT.Name','Name 1',NULL,'text'), ('object','OBJECT.View','Views',NULL,'objectAttr'); /*!40000 ALTER TABLE `attr` ENABLE KEYS */; DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `name` varchar(45) NOT NULL, `descr` varchar(45) NOT NULL, `parent` varchar(45) DEFAULT NULL, PRIMARY KEY (`name`), KEY `FK_class_parent` (`parent`), CONSTRAINT `FK_class_parent` FOREIGN KEY (`parent`) REFERENCES `class` (`name`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40000 ALTER TABLE `class` DISABLE KEYS */; INSERT INTO `class` (`name`,`descr`,`parent`) VALUES ('object','Object class','root'), ('root','Root class',NULL); /*!40000 ALTER TABLE `class` ENABLE KEYS */; DROP TABLE IF EXISTS `type`; CREATE TABLE `type` ( `name` varchar(45) NOT NULL, `descr` varchar(45) NOT NULL, `className` varchar(45) DEFAULT NULL, `labelAttr` varchar(45) DEFAULT NULL, `tipAttr` varchar(45) DEFAULT NULL, `valueAttr` varchar(45) DEFAULT NULL, PRIMARY KEY (`name`), KEY `FK_type_attr_lbl` (`labelAttr`), KEY `FK_type_class` (`className`), KEY `FK_type_attr_tip` (`tipAttr`), KEY `FK_type_attr_value` (`valueAttr`), CONSTRAINT `FK_type_attr_lbl` FOREIGN KEY (`labelAttr`) REFERENCES `attr` (`name`) ON DELETE CASCADE, CONSTRAINT `FK_type_attr_tip` FOREIGN KEY (`tipAttr`) REFERENCES `attr` (`name`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_type_attr_value` FOREIGN KEY (`valueAttr`) REFERENCES `attr` (`name`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_type_class` FOREIGN KEY (`className`) REFERENCES `class` (`name`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40000 ALTER TABLE `type` DISABLE KEYS */; INSERT INTO `type` (`name`,`descr`,`className`,`labelAttr`,`tipAttr`,`valueAttr`) VALUES ('integer','Integer',NULL,NULL,NULL,NULL), ('objectAttr','Object Attr',NULL,NULL,NULL,NULL), ('text','Text',NULL,NULL,NULL,NULL); /*!40000 ALTER TABLE `type` ENABLE KEYS */; /*!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 */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;