Bug #67501 Storage engine allowed to change for NDB tables with FK references

• Submitted: 7 Nov 2012 14:29
• Modified: 15 Mar 2013 17:34
• Reporter: Matthew Montgomery
• Status: Duplicate
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 7.3.0
• OS: Any
• CPU Architecture: Any

[7 Nov 2012 14:29] Matthew Montgomery

Description:
NDB incorrectly allows ALTER TABLE... ENGINE on a table which has or references foreign key constraints.  This ALTER to another engine is blocked under InnoDB

How to repeat:
mysql> create table t1 (a int unsigned auto_increment primary key, b varchar(20)) engine=ndbcluster; 
Query OK, 0 rows affected (0.32 sec)

mysql> create table t2 (a int unsigned auto_increment primary key, t1a int unsigned, b varchar(20), foreign key `fk1` (t1a) references t1 (a) on delete cascade) engine=ndbcluster; 
Query OK, 0 rows affected (0.46 sec)

mysql> insert into t1 (b) values('mysql'); insert into t2 (t1a,b) values(last_insert_id(),'cluster');
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> delete from t1 where a = 1 ; 
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2; 
Empty set (0.00 sec)

mysql> insert into t1 (b) values('mysql'); insert into t2 (t1a,b) values(last_insert_id(),'cluster');
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.02 sec)

mysql> select * from t1 ; select  * from t2;
+---+-------+
| a | b     |
+---+-------+
| 2 | mysql |
+---+-------+
1 row in set (0.01 sec)

+---+------+---------+
| a | t1a  | b       |
+---+------+---------+
| 2 |    2 | cluster |
+---+------+---------+
1 row in set (0.00 sec)

mysql> alter table t1 engine=innodb; 
Query OK, 1 row affected (0.50 sec)
Records: 1  Duplicates: 0  Warnings: 0

/**  NO ERROR  **/ 

mysql> delete from t1 where a = 2; 
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1; 
Empty set (0.00 sec)

mysql> select * from t2;
+---+------+---------+
| a | t1a  | b       |
+---+------+---------+
| 2 |    2 | cluster |
+---+------+---------+
1 row in set (0.00 sec)

/** ORPHANED ROW **/

Suggested fix:
Perform same checks for NDB tables as InnoDB.

mysql> create table t1 (a int unsigned auto_increment primary key, b varchar(20)) engine=innodb; 
Query OK, 0 rows affected (0.08 sec)
mysql> create table t2 (a int unsigned auto_increment primary key, t1a int unsigned, b varchar(20), foreign key `fk1` (t1a) references t1 (a) on delete cascade) engine=innodb; 
Query OK, 0 rows affected (0.17 sec)
mysql> alter table t1 engine=myisam; 
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails