MySQL Bugs: #70460: Mysql Cluster and constraints

Bug #70460	Mysql Cluster and constraints
Submitted:	28 Sep 2013 15:17	Modified:	28 Sep 2013 17:10
Reporter:	Rikhi Singh	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.6.11-ndb-7.3.2-cluster-gpl-log	OS:	Linux (CentOS release 6.4 (Final))
Assigned to:		CPU Architecture:	Any

Description:
I am having problem with constraints for ndb. I am porting an appication from innodb to ndb. Few of the constraints don't get added on ndb. I have noticed that constraint to a column that’s part of a composite primary key index doesn't work on ndb.

Following example for innodb works very well.

mysql> CREATE TABLE `new_products` (
-> `id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
-> `min_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `max_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `default_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `min_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `max_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `default_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `desctype` int(10) unsigned NOT NULL,
-> `type` enum(‘new’,'renewal’,'refund’) COLLATE latin1_general_ci DEFAULT NULL,
-> `renewal_service` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
-> `product_group_id` int(10) unsigned DEFAULT NULL,
-> `navision_code` varchar(32) COLLATE latin1_general_ci DEFAULT ”,
-> `pretty_name` varchar(255) COLLATE latin1_general_ci DEFAULT ”,
-> PRIMARY KEY (`id`,`extension_id`),
-> KEY `name` (`name`),
-> KEY `type` (`type`),
-> KEY `desctype` (`desctype`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
-> ;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `customer_referral_commission` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ’0.00′,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products` (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.02 sec)

Following for ndb doesnt work at all.

mysql> CREATE TABLE `new_products_ndb` (
-> `id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `name` varchar(255) COLLATE latin1_general_ci NOT NULL,
-> `min_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `max_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `default_months` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `min_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `max_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `default_years` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `desctype` int(10) unsigned NOT NULL,
-> `type` enum(‘new’,'renewal’,'refund’) COLLATE latin1_general_ci DEFAULT NULL,
-> `renewal_service` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
-> `product_group_id` int(10) unsigned DEFAULT NULL,
-> `navision_code` varchar(32) COLLATE latin1_general_ci DEFAULT ”,
-> `pretty_name` varchar(255) COLLATE latin1_general_ci DEFAULT ”,
-> PRIMARY KEY (`id`,`extension_id`),
-> KEY `name` (`name`),
-> KEY `type` (`type`),
-> KEY `desctype` (`desctype`)
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
-> ;
Query OK, 0 rows affected (0.41 sec)

mysql> CREATE TABLE `customer_referral_commission_ndb` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ’0.00′,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products_ndb` (`id`)
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> CREATE TABLE `customer_referral_commission_ndb` (
-> `product_id` int(10) unsigned NOT NULL,
-> `extension_id` int(10) unsigned NOT NULL DEFAULT ’0′,
-> `customer_commission` decimal(9,2) unsigned DEFAULT ’0.00′,
-> `date_entered` datetime DEFAULT NULL,
-> `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`product_id`,`extension_id`),
-> KEY `product_id` (`product_id`),
-> KEY `extension_id` (`extension_id`),
-> CONSTRAINT `customer_referral_commission_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `new_products_ndb` (`id`) on delete cascade on update restrict
-> ) ENGINE=ndb DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
ERROR 1215 (HY000): Cannot add foreign key constraint

Also, another example where child table primary key is not composite but the master table has composite index, still doesn't work.

CREATE TABLE `webmail_quota` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `mailbox_id` int(10) unsigned DEFAULT NULL,
  `expires` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `product_id` (`product_id`),
  KEY `mailbox_id` (`mailbox_id`),
 CONSTRAINT `webmail_quota_ibfk_1` FOREIGN KEY(`customer_id`) REFERENCES `cust` (`uid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster AUTO_INCREMENT=2932 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

mysql> alter table webmail_quota add   CONSTRAINT `webmail_quota_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `new_products_ndb` (`id`) ON DELETE CASCADE ON UPDATE restrict;
ERROR 1215 (HY000): Cannot add foreign key constraint

This is very well supported in innodb.  Can this be added in ndb as well.

How to repeat:
Just try the above on cluster and you can prove it yourself.

Suggested fix:
Please enable the same functionality as documentation says that this is not a limitation between innodb ad cluster.

Hello Rikhi,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

This is also affecting me. I noticed it is ONLY on UPDATE constraints having CASCADE. At least that's all i've found it effecting. See below:

mysql> use test;
Database changed
mysql> create table fk_test (x int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`x`)) engine=ndbcluster;
Query OK, 0 rows affected (0.15 sec)

mysql> show create table fk_test\G
*************************** 1. row ***************************
       Table: fk_test
Create Table: CREATE TABLE `fk_test` (
  `x` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`x`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> create table fk_child (y int(11) not null auto_increment, x int(11) NOT NULL, PRIMARY KEY (`y`), constraint `fk_test_id` foreign key (`x`) references `fk_test` (`x`) ON DELETE CASCADE ON UPDATE CASCADE) engine=NDBCLUSTER;
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> create table fk_child (y int(11) not null auto_increment, x int(11) NOT NULL, PRIMARY KEY (`y`), constraint `fk_test_id` foreign key (`x`) references `fk_test` (`x`) ON DELETE CASCADE ON UPDATE NO ACTION) engine=NDBCLUSTER;
Query OK, 0 rows affected (0.14 sec)

Any ETA on this bugs resolution?

Sorry, the version used on my test case was CentOS 6.4
ndb 7.3.3 - at time of download a few days ago it was the latest available version in RPM format.

mysql-cluster-gpl-7.4.5 on Debian has the same problem:

mysql> create table test1 (
    -> `test1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (`test1_id`)
    -> ) ENGINE=NDB;
Query OK, 0 rows affected (0.23 sec)

mysql> create table test4 (
    -> `test1_id` int(10) unsigned NOT NULL,
    -> PRIMARY KEY (`test1_id`),
    -> CONSTRAINT `FK_test1` FOREIGN KEY (`test1_id`) REFERENCES `test1` (`test1_id`) ON UPDATE CASCADE
    -> ) ENGINE=NDB;
ERROR 1215 (HY000): Cannot add foreign key constraint

it works ON DELETE CASCADE....

Best regards,
Darek

This is mentioned already in MySQL Cluster documentation as below:
"ON UPDATE CASCADE is not supported when the reference is to the parent table's primary key."

http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations-syntax.html