MySQL Bugs: #35528: Foreign keys: inconsistent error if partitions

Bug #35528	Foreign keys: inconsistent error if partitions
Submitted:	24 Mar 2008 17:44	Modified:	29 Apr 2008 9:25
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I cannot create a table which has a table clause
", foreign key ... references x" and a partition.
But I can create a table which has a column clause
"references x" and a partition.

How to repeat:
mysql> CREATE TABLE t1 (s1 INT,      FOREIGN KEY (s1) REFERENCES t1 (s1))      PARTITION BY LIST (s1)      (PARTITION p1 VALUES IN (1));
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> CREATE TABLE t1 (s1 INT REFERENCES t1 (s1))      PARTITION BY LIST (s1)      (PARTITION p1 VALUES IN (1));
Query OK, 0 rows affected (0.08 sec)

Thank you for the bug report.

Server version: 6.1.0-fk-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (s1 INT,      FOREIGN KEY (s1) REFERENCES t1 (s1))      PARTITION
    -> BY LIST (s1)      (PARTITION p1 VALUES IN (1));
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> CREATE TABLE t1 (s1 INT REFERENCES t1 (s1))      PARTITION BY LIST (s1)     
    -> (PARTITION p1 VALUES IN (1));
Query OK, 0 rows affected (0.01 sec)

mysql>

Workaround: Use column constraint and not table constraint.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45829

ChangeSet@1.2608, 2008-04-22 19:37:18+04:00, dlenev@mockturtle.local +3 -0
  Fix for bug #35528 "Foreign keys: inconsistent error if partitions".
  
  In --foreign-key-all-engines attempt to create partitioned table
  (with non-automatic partitioning) with foreign key constraints
  resulted in error. This contradicts LLD for WL148 "Foreign keys"
  and is not consistent with behavior for column foreign keys in
  the same situation.
  
  The fix for this problem is to avoid check that was causing
  this error in --foreign-key-all-engines mode.
  
  Note that once we implement one of the later milestones of WL148
  we may start emitting error in such cases again. But this behavior
  should be consistent for table and column constraints and will go
  away once WL4246 "Foreign Keys & Partitioning: Implement Storage
  Engine API for foreign key support" is complete.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46176

ChangeSet@1.2613, 2008-04-29 13:04:41+04:00, dlenev@mockturtle.local +3 -0
  Fix for bug #35528 "Foreign keys: inconsistent error if partitions".
  
  In --foreign-key-all-engines attempt to create partitioned table
  (with non-automatic partitioning) with foreign key constraints
  resulted in error. This contradicts LLD for WL148 "Foreign keys"
  and is not consistent with behavior for column foreign keys in
  the same situation.
  
  The fix for this problem is to avoid check that was causing
  this error in --foreign-key-all-engines mode.
  
  Note that once we implement one of the later milestones of WL148
  we may start emitting error in such cases again. But this behavior
  should be consistent for table and column constraints and will go
  away once WL4246 "Foreign Keys & Partitioning: Implement Storage
  Engine API for foreign key support" is complete.

Fix was pushed into mysql-6.1-fk tree. Since this issue was reported against the tree which is not publicly available yet I am simply closing this bug report.