Description:
As described on the official website, in MySQL 5.6, 5.7 and 8.0, foreign keys not supported for partitioned InnoDB tables. From https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html
==============5.6=============
1. In the instance of version 5.6, we can use "set foreign_key_checks=off" to skip this limitation. Like:
SET NAMES utf8;
SET foreign_key_checks=off;
CREATE TABLE t1 (
a int,
PRIMARY KEY (a)
) PARTITION BY RANGE(a)
(PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION P3 VALUES LESS THAN MAXVALUE);
CREATE TABLE t2 (
a int,
b int,
PRIMARY KEY (a),
KEY (b),
FOREIGN KEY(b) REFERENCES t1 (a));
Note, at this time, t2 can be created, but it cannot be created when foreign_key_checks is on.
==============5.7=============
2. If the instance is upgraded to version 5.7 and handled by msyql_upgrade, error messages can be found and t1 will be "repair" automatically. However the limitation about partition and FK is still skipped and mysqld_server is started normally. Messages like:
...
test.t1
error : Partitioning upgrade required. Please dump/reload to fix it or do: ALTER TABLE `test`.`t1` UPGRADE PARTITIONING
warning : The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead.
test.t2 OK
Upgrading tables
Running : ALTER TABLE `test`.`t1` UPGRADE PARTITIONING
status : OK
Upgrade process completed successfully.
Checking if update is needed.
==============8.0=============
3. During the upgrade process from 5.7 to 8.0, this limitation caused the upgrade to fail. Error can be found in master-error.log, like:
...
2023-05-24T09:14:06.176645Z 2 [ERROR] [MY-013140] [Server] Foreign keys are not yet supported in conjunction with partitioning
2023-05-24T09:14:06.280307Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2023-05-24T09:14:06.280337Z 0 [ERROR] [MY-010119] [Server] Aborting
...
How to repeat:
step 1 ==============5.6=============
SET NAMES utf8;
SET foreign_key_checks=off;
CREATE TABLE t1 (
a int,
PRIMARY KEY (a)
) PARTITION BY RANGE(a)
(PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION P3 VALUES LESS THAN MAXVALUE);
CREATE TABLE t2 (
a int,
b int,
PRIMARY KEY (a),
KEY (b),
FOREIGN KEY(b) REFERENCES t1 (a));
step 2 ==============5.7=============
Start with MySQL5.7 and do msyql_upgrade.
step 3 ==============8.0=============
Start with MySQL8.0 and error happens.
Suggested fix:
When upgrade from 5.6 to 5.7 more checks should be done.At least the user should be made aware of the coexistence of partition and FK, which escapes the limitation.
I think it is more appropriate to enrich the error log information to inform user of this problem, which will case the upgrade to 80 failed.
