Bug #58929 Foreign key construct is NOT ignored
Submitted: 14 Dec 2010 21:43 Modified: 14 Dec 2010 22:50
Reporter: Ryan Hansen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:7.1.9a OS:Linux (CentOS 5)
Assigned to: CPU Architecture:Any

[14 Dec 2010 21:43] Ryan Hansen
Description:
According to the documentation (http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations-unsupported.html), the foreign key construct is supposed to be ignored "just as it is in MyISAM tables."  However, this is not true.  Attempts to create foreign keys result in the following error:

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

NOTE: we are not intentionally attempting to create foreign keys on ndbcluster tables.  This bug showed up when trying to run Django's syncdb command after having upgraded to MySQL Cluster 7.1.9a.  The syncdb script automatically generates foreign key statements such as the following:

ALTER TABLE `auth_group_permissions` ADD CONSTRAINT `permission_id_refs_id_a7792de1` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`);

As far as I know, there is no way to tell syncdb *not* to try to create foreign keys; it simply expects them to be ignored on engines that do not support them. Either way, the same error results from trying to create a foreign key manually using the MySQL client, not only from Django (no big surprise there).

This bug forced us to revert back to MySQL Cluster 7.0.6 for the time being, which does operate properly (foreign key constructs ignored).

How to repeat:
Create any two tables and try to create a foreign key constraint between them.

Suggested fix:
1- Make foreign key constructs actually ignored, per documentation

OR 

2- Allow a config and/or run-time directive to activate "ignore foreign key statements" behavior
[14 Dec 2010 22:06] Sveta Smirnova
Thank you for the report.

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

Please send output of SHOW CREATE TABLE auth_group_permissions
[14 Dec 2010 22:35] Ryan Hansen
SHOW CREATE TABLE auth_group_permissions

auth_group_permissions | CREATE TABLE `auth_group_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_id` (`group_id`,`permission_id`),
  KEY `permission_id_refs_id_a7792de1` (`permission_id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
[14 Dec 2010 22:51] Sveta Smirnova
Thank you for the feedback.

verified as described. Text of the message looks confusing also.
[20 Dec 2010 15:57] Hartmut Holzgraefe
Looks more like a general server problem to me. When using partitioning with MyISAM tables the same error occurs when trying to add a foreign key.

Looks as if the "partitioning vs. foreign key" check occurs first before even checking whether the storage engine supports FKs at all.

Problem with cluster is that cluster tables are implicitly partitioned across the available data nodes, even if no explicit partitioning declaration exists. So cluster tables with foreign keys will always run into that trap, but that's hardly clusters fault ...
[21 Jul 2011 11:24] Kristian Srensen
Any estimate on when this will be fixed?
[13 Oct 2011 9:20] Lee Cho-Hsuan
I get this bug in 7.1.15a when I use django syncdb.
I change version to 7.0.13 is fine with django.
[1 Feb 2012 9:26] cédric dupui
Hi,
Can you give an estimated date for resolution?
Tkx
[21 Feb 2012 10:46] Kristian Srensen
Any updates?
[10 Apr 2012 20:58] Mirko Raner
Any ETA when this will be resolved or any work-around that doesn't involve getting rid of foreign keys?

Thanks :-)

Mirko
[12 Jun 2012 12:39] Sheeri Cabral
Adding my name to the long list of people who would like to see this bug resolved (looks like at least 5 or 6 folks have commented on it).
[13 Sep 2012 14:31] Thomas CATTY
not yet solved..even with the 7.2 version of mysql-cluster I'm using.