Bug #32948 FKs allowed to reference partitioned table
Submitted: 4 Dec 2007 1:09 Modified: 13 Dec 2007 10:07
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: ALTER TABLE, foreign keys, partitioning
Triage: D2 (Serious)

[4 Dec 2007 1:09] Kolbe Kegel
Description:
According to documentation athttp://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html and bug #14353 it should not be possible to create a foreign key constraint that references a partitioned table.

I assume this is not supported because is can cause some problems. However, it's still possible to create tables in a way that a foreign key references a partitioned table.

Executing the statements in this order causes an error to be issued:

create table t1 (id int, primary key (id)) engine=innodb;
alter table t1 partition by hash(id) partitions 5;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) engine=innodb;

mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade) engine=innodb;
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)

But it's possible to execute the statements in a different order, creating a situation where a partitioned table is referenced by a foreign key:

create table t1 (id int, primary key (id)) engine=innodb;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) on delete cascade engine=innodb;
alter table t1 partition by hash(id) partitions 5;

mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id)) on delete cascade engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

After partitioning t1, inserts into t2 will fail and changes to t1 will not cascade to t2. That's a big problem, and it's unexpected from the user's side since they were able to successfully partition t1.

How to repeat:
create table t1 (id int, primary key (id)) engine=innodb;
create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade ) engine=innodb;
alter table t1 partition by hash(id) partitions 5;

insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);

alter table t1 remove partitioning;

insert into t2 values (1),(2),(3);

alter table t1 partition by hash(id) partitions 5;

delete from t1;

select * from t2;

mysql 5.1.22-rc (root) [test]> create table t1 (id int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1.22-rc (root) [test]> create table t2 (id int, primary key (id), foreign key (id) references t1 (id) on delete cascade ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> insert into t2 values (1),(2),(3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON DELETE CASCADE)

mysql 5.1.22-rc (root) [test]> alter table t1 remove partitioning;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> insert into t2 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> alter table t1 partition by hash(id) partitions 5;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1.22-rc (root) [test]> delete from t1;
Query OK, 3 rows affected (0.00 sec)

mysql 5.1.22-rc (root) [test]> select * from t2;
+----+
| id |
+----+
|  1 | 
|  2 | 
|  3 | 
+----+
3 rows in set (0.00 sec)

Suggested fix:
Since partitioning and foreign keys quite clearly do not "play well" together, it should not be possible to partition a table that is referenced by any foreign key.

Permitting this leads to surprising results when inserting data and serious data integrity issues when updating rows in the parent table.

Possibly related to bugs #17143 and #14353.
[6 Dec 2007 13:20] Mattias Jonsson
Seems to be a small fix. If alter to partitioning, treat it as if it changes engine.
[6 Dec 2007 13:43] 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/39410

ChangeSet@1.2677, 2007-12-06 14:43:06+01:00, mattiasj@witty.ndb.mysql.com +3 -0
  Bug#32948: FKs allowed to reference partitioned table
  
  Problem: when alter to partitioned table,
  it does not see it as change of engine.
  
  Solution: If alter includes partitioning, check if it is possible
  to change engines (eg. is the table referenced by a FK)
[6 Dec 2007 14:05] Mattias Jonsson
Mikeal approved the patch
[12 Dec 2007 23:00] Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02] Bugs System
Pushed into 5.1.23-rc
[13 Dec 2007 10:07] Jon Stephens
Documented bugfix in 5.1.23 and 6.0.5 changelogs as:

        It was possible to partition a table to which a foreign key
        referred.