MySQL Bugs: #50104: Partitioned table with just 1 partion works with fk

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

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.

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

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.

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

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.

pushed into mysql-5.1-bugteam and mysql-pe

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

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)

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.

5.5.4 changelog entry was moved to 5.5.5.

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)

Also documented in the 5.1.46 changelog. Closed.

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)

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)

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)