| Bug #79156 | Circular foreign keys + index = failure | ||
|---|---|---|---|
| Submitted: | 6 Nov 2015 15:34 | Modified: | 3 Mar 2016 16:42 |
| Reporter: | Anders Kvist | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 7.4.8 | OS: | Linux |
| Assigned to: | Lakshmi Narayanan Sreethar | CPU Architecture: | Any |
| Tags: | foreign key, INDEX, ndbcluster | ||
[9 Nov 2015 11:41]
Anders Kvist
We've found an other example that causes the same result. This time it's not a circular foreign key, but A->B->C that causes index to fail if applied to B. It works on A and C, but not B which have both in and outgoing foreign keys.
CREATE TABLE TableA (
id INT,
tableB_id INT,
PRIMARY KEY(id)
) ENGINE = ndbcluster;
CREATE TABLE TableB (
id INT,
tableC_id INT,
PRIMARY KEY(id)
) ENGINE = ndbcluster;
CREATE TABLE TableC (
id INT,
PRIMARY KEY(id)
) ENGINE = ndbcluster;
ALTER TABLE TableA ADD CONSTRAINT FK_B_id FOREIGN KEY (tableB_id) REFERENCES TableB (id);
ALTER TABLE TableB ADD CONSTRAINT FK_C_id FOREIGN KEY (tableC_id) REFERENCES TableC (id);
CREATE INDEX IDX_C_id ON TableB (tableC_id);
[9 Nov 2015 12:21]
MySQL Verification Team
[ndbd(NDB)] 2 node(s)
id=1 @127.0.0.1 (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0, *)
id=2 @127.0.0.1 (mysql-5.6.24 ndb-7.4.6, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=3 @127.0.0.1 (mysql-5.6.24 ndb-7.4.6)
mysql> CREATE TABLE TableA (
-> id INTEGER,
-> tableB_id INTEGER,
-> PRIMARY KEY(id)
-> ) ENGINE = ndbcluster;
Query OK, 0 rows affected (0.93 sec)
mysql>
mysql> CREATE TABLE TableB (
-> id INTEGER,
-> tableA_id INTEGER,
-> PRIMARY KEY(id)
-> ) ENGINE = ndbcluster;
Query OK, 0 rows affected (0.89 sec)
mysql> ALTER TABLE TableA ADD CONSTRAINT FK_A_TO_B FOREIGN KEY (tableB_id) REFERENCES TableB (id);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE TableB ADD CONSTRAINT FK_B_TO_A FOREIGN KEY (tableA_id) REFERENCES TableA (id);
Query OK, 0 rows affected (5.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE INDEX IDX_A_id ON TableB (tableA_id);
Query OK, 0 rows affected (1.84 sec)
mysql>
[9 Nov 2015 12:24]
MySQL Verification Team
mysql> drop table TableB
-> ;
Query OK, 0 rows affected (0.30 sec)
mysql> drop table TableA;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TableA |
+----------------+
1 row in set (0.00 sec)
mysql> drop table TableA;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
[9 Nov 2015 12:25]
MySQL Verification Team
mysql> show create table TableA\G
*************************** 1. row ***************************
Table: TableA
Create Table: CREATE TABLE `TableA` (
`id` int(11) NOT NULL DEFAULT '0',
`tableB_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_A_TO_B` (`tableB_id`),
CONSTRAINT `FK_A_TO_B` FOREIGN KEY (`tableB_id`) REFERENCES `#sql2-769f-2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
[9 Nov 2015 12:32]
MySQL Verification Team
mysql> use test1;
Database changed
mysql> CREATE TABLE TableA (
-> id INT,
-> tableB_id INT,
-> PRIMARY KEY(id)
-> ) ENGINE = ndbcluster;
Query OK, 0 rows affected (0.84 sec)
mysql> CREATE TABLE TableB (
-> id INT,
-> tableC_id INT,
-> PRIMARY KEY(id)
-> ) ENGINE = ndbcluster;
Query OK, 0 rows affected (0.80 sec)
mysql> CREATE TABLE TableC (
-> id INT,
-> PRIMARY KEY(id)
-> ) ENGINE = ndbcluster;
Query OK, 0 rows affected (1.15 sec)
mysql> ALTER TABLE TableA ADD CONSTRAINT FK_B_id FOREIGN KEY (tableB_id) REFERENCES TableB (id);
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE TableB ADD CONSTRAINT FK_C_id FOREIGN KEY (tableC_id) REFERENCES TableC (id);
Query OK, 0 rows affected (0.91 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> CREATE INDEX IDX_C_id ON TableB (tableC_id);
Query OK, 0 rows affected (1.91 sec)
mysql>
[9 Nov 2015 12:42]
MySQL Verification Team
mysql> drop table TableB;
Query OK, 0 rows affected (0.31 sec)
mysql> drop table TableA;
Query OK, 0 rows affected (0.29 sec)
mysql> drop table TableC;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> show create table TableC\G
*************************** 1. row ***************************
Table: TableC
Create Table: CREATE TABLE `TableC` (
`id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
[9 Nov 2015 13:19]
MySQL Verification Team
exactly the same behavior with 7.4.8
[9 Nov 2015 13:20]
MySQL Verification Team
Hi Anders, Thanks for the report kind regards Bogdan Kecman
[9 Nov 2015 13:22]
MySQL Verification Team
mysql> show create table TableA\G
*************************** 1. row ***************************
Table: TableA
Create Table: CREATE TABLE `TableA` (
`id` int(11) NOT NULL DEFAULT '0',
`tableB_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_A_TO_B` (`tableB_id`),
CONSTRAINT `FK_A_TO_B` FOREIGN KEY (`tableB_id`) REFERENCES `#sql2-7c4f-2` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> \q
Bye
[root@gedora mysql]# bin/ndb_show_tables
id type state logging database schema name
1 IndexTrigger Online - NDB$INDEX_11_CUSTOM
4 UserTable Online Yes mysql def ndb_index_stat_head
2 IndexTrigger Online - NDB$INDEX_13_CUSTOM
15 ForeignKey Online - 12/10/FK_A_TO_B
5 FKChildTrigger Online - NDB$FK_15_CHILD_10
4 FKParentTrigger Online - NDB$FK_15_PARENT_12
6 OrderedIndex Online No sys def ndb_index_stat_sample_x1
16 OrderedIndex Online No sys def FK_B_TO_A
3 SystemTable Online Yes sys def NDB$EVENTS_0
12 UserTable Online Yes test def #sql2-7c4f-2
2 SystemTable Online Yes sys def SYSTAB_0
5 UserTable Online Yes mysql def ndb_index_stat_sample
8 FKChildTrigger Online - NDB$FK_17_CHILD_12
14 OrderedIndex Online No sys def FK_A_TO_B
8 UserTable Online Yes mysql def NDB$BLOB_7_3
3 IndexTrigger Online - NDB$INDEX_14_CUSTOM
7 UserTable Online Yes mysql def ndb_schema
6 IndexTrigger Online - NDB$INDEX_16_CUSTOM
9 UserTable Online Yes mysql def ndb_apply_status
1 0 Online - DEFAULT-HASHMAP-3840-2
11 OrderedIndex Online No sys def PRIMARY
0 IndexTrigger Online - NDB$INDEX_6_CUSTOM
13 OrderedIndex Online No sys def PRIMARY
10 UserTable Online Yes test def TableA
17 ForeignKey Online - 10/12/FK_B_TO_A
7 FKParentTrigger Online - NDB$FK_17_PARENT_10
2 TableEvent Online - REPL$mysql/ndb_schema
5 TableEvent Online - REPL$test/TableA
3 TableEvent Online - NDB$BLOBEVENT_REPL$mysql/ndb_schema_3
4 TableEvent Online - REPL$mysql/ndb_apply_status
1 TableEvent Online - ndb_index_stat_head_event
NDBT_ProgramExit: 0 - OK
[root@gedora mysql]#
[3 Mar 2016 16:42]
Jon Stephens
Documented fix in the NDB 7.4.11 and 7.5.1 changelogs, as follows:
When using CREATE INDEX to add an index on either of two tables
sharing circular foreign keys, the query succeeded but a
temporary table was left on disk, breaking the foreign key
constraints. This issue was also observed when attempting to
create an index on a table in the middle of a chain of foreign
keys—that is, a table having both parent and child keys,
but on different tables. The problem did not occur when using
ALTER TABLE to perform the same index creation operation; and
subsequent analysis revealed unintended differences in the way
such operations were performed by CREATE INDEX.
To fix this problem, we now make sure that operations performed
by a CREATE INDEX statement are always handled internally in the
same way and at the same time that operations performed by ALTER
TABLE or DROP INDEX are handled.
Closed.
Description: When creating an index on two tables with circular foreign keys, the query will succeed but a temporary table from the CREATE INDEX is left on disk and the foreign key constraints are broken on that table. If the InnoDB engine is used, everything works just fine. We've reproduced this on two different setups - one with the .tgz and the other with the rpm package. I haven't attached the error report as we find this very easy to reproduce. How to repeat: CREATE DATABASE testdb; USE testdb; CREATE TABLE TableA ( id INTEGER, tableB_id INTEGER, PRIMARY KEY(id) ) ENGINE = ndbcluster; CREATE TABLE TableB ( id INTEGER, tableA_id INTEGER, PRIMARY KEY(id) ) ENGINE = ndbcluster; ALTER TABLE TableA ADD CONSTRAINT FK_A_TO_B FOREIGN KEY (tableB_id) REFERENCES TableB (id); ALTER TABLE TableB ADD CONSTRAINT FK_B_TO_A FOREIGN KEY (tableA_id) REFERENCES TableA (id); -- This doesnt work here, and will cause a temporary table to persist forever, preventing future ops. -- If we move it to before the two "alter tables" above, it works. CREATE INDEX IDX_A_id ON TableB (tableA_id);