Description:
ALTER TABLE t REORGANIZE PARTITION <list of partitions> INTO (<list of partition definitions>) requires the <list of partitions> to be 'adjacent partitions'/'must be in consecutive order', i.e. from the ordering from the [SHOW] CREATE TABLE.
But LIST Partitioning should not be restricted to 'consecutive order', because the different partitions in LIST is just a set, their order should not matter.
This limitation means that non-affected partitions needs to be included in the REORG, resulting in longer time (while the table is blocked) and waste of resources.
How to repeat:
mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p2 values in ("g","y"), partition p3 values in ("o","c"));
Query OK, 0 rows affected (0,04 sec)
mysql> alter table listpart reorganize partition p1, p3 into (partition p1 values in ("b","c"), partition p3 values in ("o", "r"));
ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order
If just the order of the partitions was different, it would work:
mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p3 values in ("o","c"), partition p2 values in ("g","y"));
Query OK, 0 rows affected (0,03 sec)
mysql> alter table listpart reorganize partition p1, p3 into (partition p1 values in ("b","c"), partition p3 values in ("o", "r"));
Query OK, 0 rows affected (0,05 sec)
Records: 0 Duplicates: 0 Warnings: 0
Or if you also recreate the partitions listed in between, it also works, but takes longer time, while the table is locked and it wastes resources.:
mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p2 values in ("g","y"), partition p3 values in ("o","c"));
Query OK, 0 rows affected (0,03 sec)
mysql> alter table listpart reorganize partition p1, p2, p3 into (partition p1 values in ("b","c"), partition p2 values in ("g","y"), partition p3 values in ("o", "r"));
Query OK, 0 rows affected (0,07 sec)
Records: 0 Duplicates: 0 Warnings: 0
Suggested fix:
Remove the artificial limitation of "in consecutive order" for REORGANIZE PARTITION for LIST partitions to allow reorganize a set of partitions that are in any order.
Description: ALTER TABLE t REORGANIZE PARTITION <list of partitions> INTO (<list of partition definitions>) requires the <list of partitions> to be 'adjacent partitions'/'must be in consecutive order', i.e. from the ordering from the [SHOW] CREATE TABLE. But LIST Partitioning should not be restricted to 'consecutive order', because the different partitions in LIST is just a set, their order should not matter. This limitation means that non-affected partitions needs to be included in the REORG, resulting in longer time (while the table is blocked) and waste of resources. How to repeat: mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p2 values in ("g","y"), partition p3 values in ("o","c")); Query OK, 0 rows affected (0,04 sec) mysql> alter table listpart reorganize partition p1, p3 into (partition p1 values in ("b","c"), partition p3 values in ("o", "r")); ERROR 1519 (HY000): When reorganizing a set of partitions they must be in consecutive order If just the order of the partitions was different, it would work: mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p3 values in ("o","c"), partition p2 values in ("g","y")); Query OK, 0 rows affected (0,03 sec) mysql> alter table listpart reorganize partition p1, p3 into (partition p1 values in ("b","c"), partition p3 values in ("o", "r")); Query OK, 0 rows affected (0,05 sec) Records: 0 Duplicates: 0 Warnings: 0 Or if you also recreate the partitions listed in between, it also works, but takes longer time, while the table is locked and it wastes resources.: mysql> create table listpart (a bigint unsigned not null, b char(1) not null, c varchar(255), primary key (a, b), key (c)) partition by list columns (b) (partition p1 values in ("b", "r"), partition p2 values in ("g","y"), partition p3 values in ("o","c")); Query OK, 0 rows affected (0,03 sec) mysql> alter table listpart reorganize partition p1, p2, p3 into (partition p1 values in ("b","c"), partition p2 values in ("g","y"), partition p3 values in ("o", "r")); Query OK, 0 rows affected (0,07 sec) Records: 0 Duplicates: 0 Warnings: 0 Suggested fix: Remove the artificial limitation of "in consecutive order" for REORGANIZE PARTITION for LIST partitions to allow reorganize a set of partitions that are in any order.