Bug #67492 user-defined partitioning and foreign keys in cluster
Submitted: 6 Nov 2012 21:21 Modified: 22 Jan 2013 13:46
Reporter: Matthew Montgomery Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:ndb-7.3.0 OS:Any
Assigned to: CPU Architecture:Any

[6 Nov 2012 21:21] Matthew Montgomery
Description:
Currently InnoDB does not support foreign keys on partitioned tables.  NDB has no such limitation.  MySQL is incorrectly applying this rule preventing partitioned tables to have foreign keys against NDB also. However is doing so inconsistently causing a potential problem for restore. 

How to repeat:
mysql> create table t1 (a int unsigned auto_increment primary key, b varchar(20)) engine=ndbcluster; 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 partition by key () partitions 2;
Query OK, 0 rows affected (0.26 sec)

ERROR 1506 (HY000): Foreign key clause is not yet supported in conjunction with partitioning

<Workaround> 

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.49 sec)

mysql> alter table t2 partition by key () partitions 2;
Query OK, 0 rows affected (1.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t1a` int(10) unsigned DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `fk1` (`t1a`),
 CONSTRAINT `fk1` FOREIGN KEY(`t1a`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY ()
PARTITIONS 2 */
1 row in set (0.01 sec)

This leaves the NDB table definition unable to be restored via mysqldump.

InnoDB is not susceptible to this workaround.

Suggested fix:
Enforce the "no FK on partitioned tables" rule only for InnoDB tables allowing NDB tables to be created with FKs and user-defined partitioning.
[22 Jan 2013 13:46] Paul DuBois
Noted in 5.6.10, 5.7.1 changelogs.

It is not permitted to use CREATE TABLE to create an NDB table with
user-defined partitioning and a foreign key. However, it was possible
to create an NDB table with a foreign key, then add partitioning to
it using ALTER TABLE, thus creating a table which was impossible to
backup/restore using mysqldump. Now the prohibition is enforced
consistently.