Bug #50104 Partitioned table with just 1 partion works with fk
Submitted: 6 Jan 2010 13:31 Modified: 20 Jun 2010 22:59
Reporter: Oli Sennhauser Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: foreign key, innodb, partition, table
Triage: Triaged: D3 (Medium)

[6 Jan 2010 13:31] Oli Sennhauser
Description:
When I create a partitioned table with only ONE partition and a FK this works. But should not according to the documentation:

Foreign keys not supported.
Partitioned tables do not support foreign keys. This means that:
   1. Definitions of tables employing user-defined partitioning may not contain foreign key references to other tables.
   2. No table definition may contain a foreign key reference to a partitioned table.
The scope of these restrictions includes tables that use the InnoDB storage engine.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

This is either a Innodb/Ptn bug or a docu bug. Please decide

How to repeat:
CREATE TABLE `parent` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

ALTER TABLE test
 PARTITION BY HASH (id) partitions 1
;

ALTER TABLE test
  ADD CONSTRAINT `test_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
;

SHOW CREATE TABLE test\G

Suggested fix:
Either change docu or give warning depending on the decision from above.
[8 Jan 2010 6:11] Jimmy Yang
This should send to doc team. There is no problem to create fk on single partitioned table. It is up to doc team to see whether it is necessary to document such behavior, depending on whether we differentiate "single partitioned table" and "unpartitioned table". We only restrict fk on partitioned table with more than one partition.

The actual error we hitting when creating foreign constraints on partitioned table (more than one partition) is actually a dup key error, when we try to create the same foreign key on individual partitions (we call ha_innobase::create(), dict_create_foreign_constraints() and dict_create_add_foreign_to_dictionary() for each partition). The dup key error is raised when we insert dup foreign key define into system table SYS_FOREIGN (for each partition). 

If table is single partitioned, we will certainly not hitting this dup key error as a single partition is created, and single define of foreign key inserted into SYS_FOREIGN.  

Normally, partition table refers to table with multiple partitions, table with single (especially hash) partition is no different from normal table.  And there is no consistency issue for having a foreign constraint on a single partitioned table.

Any alter table operation to increase the table from single partition to multiple partition on this table will hitting similar error as that of normal table for foreign key issues. So the behavior is correct.

So it is up to doc team to see whether it is necessary to document it, depending on how we differentiate "single partitioned table" and "unpartitioned table".

Thanks
Jimmy
[17 Jan 2010 14:08] Jon Stephens
Just because something happens to work after we've been told that is not supported does not mean we document it as supported behaviour. (Docs doesn't determine what is supported.)

The fact that partitioning + FK is not disallowed in this case is (as far as I can tell) a Server:Partitioning bug, and the number of partitions is not of consequence.

Category/Status/Lead/Assignee -> Server:Partitioning/Open/none/none

Partitioning team need to fix this, or confirm that an exception is to be made in the "partitioning means no FK" rule.

cheers.
[18 Jan 2010 22:26] Mattias Jonsson
Should be prohibited in mysql_alter_table. Currently only prohibited when creating a new table (which is seems not to be done with this statement).

Assigning myself
[4 Mar 2010 11:30] Bugs System
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/102289

3369 Mattias Jonsson	2010-03-04
      Bug#50104: Partitioned table with just 1 partion works with fk
      
      There was no check for foreign keys when altering partitioned
      tables.
      
      Added check for FK when altering partitioned tables.
     @ mysql-test/r/partition_innodb.result
        Bug#50104: Partitioned table with just 1 partion works with fk
        
        Updated test result
     @ mysql-test/t/partition_innodb.test
        Bug#50104: Partitioned table with just 1 partion works with fk
        
        Added test for adding FK on partitioned tables (both 1 and 2
        partitions)
     @ sql/sql_partition.cc
        Bug#50104: Partitioned table with just 1 partion works with fk
        
        Disabled adding foreign key when altering a partitioned table.
[16 Mar 2010 16:06] Mattias Jonsson
pushed into mysql-5.1-bugteam and mysql-pe
[26 Mar 2010 8:21] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:25] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:30] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[29 Mar 2010 9:13] Jon Stephens
Documented in the 5.5.4 and 6.0.14 changelogs as follows:

        Foreign keys are not supported on partitioned tables. However,
        it was possible via an ALTER TABLE statement to set a foreign
        key on a partitioned table; it was also possible to partition a
        table with a single foreign key.

Set to NM, waiting for 5.1 merge.
[31 Mar 2010 16:02] Paul Dubois
5.5.4 changelog entry was moved to 5.5.5.
[6 Apr 2010 7:58] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:martin.hansson@sun.com-20100316162138-u9724fhm54cj3or0) (merge vers: 5.1.46) (pib:16)
[6 Apr 2010 10:28] Jon Stephens
Also documented in the 5.1.46 changelog. Closed.
[17 Jun 2010 12:05] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:49] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:32] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)