MySQL Bugs: #69008: Drop database not working in presence of foreign keys

Bug #69008	Drop database not working in presence of foreign keys
Submitted:	19 Apr 2013 9:48	Modified:	29 May 2013 16:45
Reporter:	Sébastien Barré	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	7.3.1 m2	OS:	Windows ( (Server 2008 R2 64-bit))
Assigned to:		CPU Architecture:	Any

Description:
If I create two simple tables linked with foreign keys, I am then unable to drop the database (error 23000: Cannot delete or update a parent row: a foreign key constraint fails).

After the drop instruction, the db is left with a subset of the tables removed.
Trying to remove one table gives me "Unknown table", although the table is indeed deleted.

Below in "how to repeat" section, I'll give a simple example that reproduces that and finally allows to drop the database. However with our real-world database, that has more tables and referential constraints, I have not been able to drop the database, the only way around being to remove the database files from the filesystem (that said, I realize that probably removing the foreign key constraints one by one, then dropping the DB would have probably been a workaround).

As a final note, I tried "set foreign_key_checks=0", but that has no effect. I guess it is not propagated to the ndb engine.

Our cluster setup is exactly the same as described here: http://bugs.mysql.com/bug.php?id=68224 (obviously ported to the 7.3.1m2 release of mysql cluster)

How to repeat:
mysql>create database test;
Query OK, 1 row affected (0.06 sec)
mysql>use test;
Database changed
mysql>create fktest (id int primary key) engine=ndb;
Query OK, 0 rows affected (0.23 sec)
mysql>create fktest2 (id int, foreign(id) references fktest(id)) engine=ndb;
Query OK, 0 rows affected (0.27 sec)
mysql>drop database test;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>show tables;
+----------------+
| Tables_in_test |
+----------------+
| fktest         |
+----------------+
1 row in set, 1 warning (0.00 sec)

#(note that fktest2 did disappear)
#Then 2 branches with different behaviours:
#->branch 1
#============
mysql>drop database test; (again)
Query OK, 1 row affected (0.13 sec)
#Yes, second time works, given part of the db had been removed by the first call.
#->branch2
#============
mysql>drop table fktest;
ERROR 1051 (42S02): Unknown table 'test.fktest'
mysql>show tables;
Empty set (0.00 sec) #Mysql removed it despite pretending that it did not exist.

Suggested fix:
drop database looks implemented something like the following (pseudo-code):
--------
set foreign_key_checks=0
drop all tables in any order
set foreign_key_checks=1
--------

If that's true, the point is simply to propagate the foreign_key_checks to the ndb nodes, that currently are not aware of it.

Regarding the "Unkown table" error, I assume this is because it had been removed already by the drop database from some internal tables. If so, it would be fixed by the above suggestion.

Hello Sébastien Barré,

Thank you for the report.
Verified as described.

Thanks,
Umesh

## How to repeat

create database defect_69008;
use defect_69008
create table fktest (id int primary key) engine=ndb;
create table fktest2 (id int, name varchar(30), foreign key(id) references fktest(id)) engine=ndb;
drop database defect69008;
show tables;
show warnings;
create database defect69008;

### using NDB tables

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.6.10-ndb-7.3.1-cluster-gpl |
+------------------------------+
1 row in set (0.00 sec)

mysql> use defect_69008
Database changed
mysql> create table fktest (id int primary key) engine=ndb;
Query OK, 0 rows affected (1.02 sec)

mysql> create table fktest2 (id int, name varchar(30), foreign key(id) references fktest(id)) engine=ndb;
Query OK, 0 rows affected (1.11 sec)

mysql>
mysql> drop database defect_69008;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
mysql> show errors;
+-------+------+----------------------------------------------------------------------+
| Level | Code | Message                                                              |
+-------+------+----------------------------------------------------------------------+
| Error | 1217 | Cannot delete or update a parent row: a foreign key constraint fails |
+-------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 21080 'Drop table not allowed in NDB - referenced by foreign key on another table' from NDB |
| Error   | 1217 | Cannot delete or update a parent row: a foreign key constraint fails                                  |
+---------+------+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| defect_69008       |
| mysql              |
| ndbinfo            |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

mysql>

### using Innodb tables - works as expected

mysql> create database defect_69008in;
Query OK, 1 row affected (0.00 sec)

mysql> use defect_69008in
Database changed
mysql> create table fktest (id int primary key) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table fktest2 (id int, name varchar(30), foreign key(id) references fktest(id)) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql>
mysql> drop database defect_69008in;
Query OK, 2 rows affected (0.03 sec)

mysql>

bug #69116 looks the same

Posted by developer:
 
Committed fix for 7.3.2

Documented fix in the NDB 7.3.2 changelog as follows:

      DROP DATABASE failed to work correctly when executed against a database
      containing NDB tables joined by foreign keys, leaving these tables in
      place while dropping the remaining tables in the database and reporting
      failure.

Closed.

This bug is still present in MySQL Cluster 7.3.3, though it is intermittent.

James.