| Bug #73252 | Duplicate foreign keys break insertions to a table | ||
|---|---|---|---|
| Submitted: | 10 Jul 2014 7:18 | Modified: | 21 Aug 2014 8:55 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | foreign key, innodb | ||
[21 Jul 2014 8:55]
MySQL Verification Team
Thank you for the report. I cannot repeat this behavior at my end with the similar test case. How often are you seeing this kind of errors triggered? Please could you provide the exact repeatable test case? Also, similar reported here Bug #69861 but caused by LAST_INSERT_ID.. Are you using LAST_INSERT_ID anywhere? ######## mysql> drop table if exists `t1`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists `t2`; drop table if exists `t3`; create table t2(id int primary key)engine=innodb; create table t3(id int primary key)engine=innodb; Query OK, 0 rows affected (0.13 sec) mysql> drop table if exists `t3`; create table `t1` ( `a` int not null ,`b` int not null ,`c` date not null, `d` timestamp not null default current_timestamp on update current_timestamp, primary key (`a`,`b`,`c`), key(`b`), key(`c`), constraint `_t1_ibfk_1` foreign key (`a`) references `t2` (`id`), constraint `_t1_ibfk_2` foreign key (`b`) references `t3` (`id`), constraint `t1_ibfk_1` foreign key (`a`) references `t2` (`id`), constraint `t1_ibfk_2` foreign key (`b`) references `t3` (`id`) ) engine=innodb; insert into t2 values(1); Query OK, 0 rows affected (0.10 sec) mysql> create table t2(id int primary key)engine=innodb; insert into t3 values(1); insert into t1(a,b,c) values (1,1,'2014-07-10');Query OK, 0 rows affected (0.29 sec) mysql> create table t3(id int primary key)engine=innodb; Query OK, 0 rows affected (0.30 sec) mysql> mysql> create table `t1` ( -> `a` int not null -> ,`b` int not null -> ,`c` date not null, -> `d` timestamp not null default current_timestamp on update current_timestamp, -> primary key (`a`,`b`,`c`), -> key(`b`), -> key(`c`), -> constraint `_t1_ibfk_1` foreign key (`a`) references `t2` (`id`), -> constraint `_t1_ibfk_2` foreign key (`b`) references `t3` (`id`), -> constraint `t1_ibfk_1` foreign key (`a`) references `t2` (`id`), -> constraint `t1_ibfk_2` foreign key (`b`) references `t3` (`id`) -> ) engine=innodb; Query OK, 0 rows affected (0.40 sec) mysql> mysql> insert into t2 values(1); Query OK, 1 row affected (0.04 sec) mysql> insert into t3 values(1); Query OK, 1 row affected (0.03 sec) mysql> insert into t1(a,b,c) values (1,1,'2014-07-10'); Query OK, 1 row affected (0.02 sec) mysql> select * from t1; +---+---+------------+---------------------+ | a | b | c | d | +---+---+------------+---------------------+ | 1 | 1 | 2014-07-10 | 2014-07-23 04:58:42 | +---+---+------------+---------------------+ 1 row in set (0.00 sec)
[22 Aug 2014 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: Due to an issue we probably triggered ourselves I noticed that if you have duplicate foreign keys in InnoDB it will not let you insert data properly. So running a script to collect some grants failed unexpectedly: # dba_import_grants 2014-07-10 09:03:22 myhost dba_import_grants[8184]: FATAL Unable to insert row into instance_grant_report with values (1631,40024,2014-07-10): [1452] Cannot add or update a child row: a foreign key constraint fails (`dba`.`instance_grant_report`, CONSTRAINT `_instance_grant_report_ibfk_1` FOREIGN KEY (`instance_id`) REFERENCES `instance` (`id`)) Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle database=dba;mysql_read_default_file=/path/to/.my.cnf at /path/to/Logging.pm line 77. The table structure was: root@myhost [dba]> show create table instance_grant_report\G *************************** 1. row *************************** Table: instance_grant_report Create Table: CREATE TABLE `instance_grant_report` ( `instance_id` int(10) unsigned NOT NULL, `grant_id` int(10) unsigned NOT NULL, `report_date` date NOT NULL, `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`instance_id`,`grant_id`,`report_date`), KEY `grant_id` (`grant_id`), KEY `report_date` (`report_date`), CONSTRAINT `_instance_grant_report_ibfk_1` FOREIGN KEY (`instance_id`) REFERENCES `instance` (`id`), CONSTRAINT `_instance_grant_report_ibfk_2` FOREIGN KEY (`grant_id`) REFERENCES `grant_info` (`id`), CONSTRAINT `instance_grant_report_ibfk_1` FOREIGN KEY (`instance_id`) REFERENCES `instance` (`id`), CONSTRAINT `instance_grant_report_ibfk_2` FOREIGN KEY (`grant_id`) REFERENCES `grant_info` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) I dropped the accidentally created duplicate foreign keys: root@myhost [dba]> alter table instance_grant_report drop foreign key _instance_grant_report_ibfk_1, drop foreign key _instance_grant_report_ibfk_2; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 and then reran the unchanged script and it ran fine: [root@myhost ~]# dba_import_grants # it's designed to be silent unless there are issues [root@myhost ~]# root@myhost [dba]> select * from instance_grant_report where instance_id= 1631 and grant_id=40024 and report_date = current_date(); +-------------+----------+-------------+---------------------+ | instance_id | grant_id | report_date | last_change | +-------------+----------+-------------+---------------------+ | 1631 | 40024 | 2014-07-10 | 2014-07-10 09:04:24 | +-------------+----------+-------------+---------------------+ 1 row in set (0.00 sec) My conclusion is that InnoDB does not "like" duplicate foreign key definitions and this actually breaks stuff. The SQL insert attempted by this script was very simple: INSERT INTO instance_grant_report (instance_id,grant_id,report_date) VALUES (?,?,?) with the placeholders replaced with real values. How to repeat: See above. Suggested fix: Is this behaviour documented? If my diagnosis is correct does it not make more sense to not allow duplicate foreign keys and generate an error as it makes it impossible to actually insert any data, should someone accidentally attempt to create such a duplicate foreign key? Note: the cause of the duplicate foreign key creation was self-inflicted but the effect it had was quite nasty. I'd rather not break all changes to a table due to this and get an error that the foreign key is already defined.