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.
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.