1. Fast ADD case: existing rows do not match new partition range
Reproduce
create or replace table t1 (x int)
|
partition by range columns (x) (
|
partition p0 values less than (10),
|
partition p1 values less than (20),
|
partition pn values less than maxvalue);
|
|
insert into t1 values (1);
|
insert into t1 values (11);
|
insert into t1 values (31);
|
|
alter table t1 add partition (partition p2 values less than (30));
|
Result
alter table t1 add partition (partition p2 values less than (30));
|
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
|
Expected
Partition p2 added between p1 and pn.
Workaround via CONVERT PARTITION
create or replace table t1 (x int)
|
partition by range columns (x) (
|
partition p0 values less than (10),
|
partition p1 values less than (20),
|
partition pn values less than maxvalue);
|
|
insert into t1 values (1);
|
insert into t1 values (11);
|
insert into t1 values (31);
|
|
alter table t1 convert partition pn to table pn;
|
alter table t1 add partition (partition p2 values less than (30));
|
alter table t1 convert table pn to partition pn values less than maxvalue;
|
Workaround result
CREATE TABLE `t1` (
|
`x` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
PARTITION BY RANGE COLUMNS(`x`)
|
(PARTITION `p0` VALUES LESS THAN (10) ENGINE = InnoDB,
|
PARTITION `p1` VALUES LESS THAN (20) ENGINE = InnoDB,
|
PARTITION `p2` VALUES LESS THAN (30) ENGINE = InnoDB,
|
PARTITION `pn` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)
|
2. Copy case: existing rows match new partition range
Reproduce
create or replace table t1 (x int)
|
partition by range columns (x) (
|
partition p0 values less than (10),
|
partition p1 values less than (20),
|
partition pn values less than maxvalue);
|
|
insert into t1 values (21);
|
|
alter table t1 add partition (partition p2 values less than (30));
|
Expected
Partition p2 is added between p1 and pn; row 21 is moved to partition p2.
Workaround via CONVERT PARTITION does not work (MDEV-31213)
create or replace table t1 (x int)
|
partition by range columns (x) (
|
partition p0 values less than (10),
|
partition p1 values less than (20),
|
partition pn values less than maxvalue);
|
|
insert into t1 values (21);
|
|
alter table t1 convert partition pn to table pn;
|
alter table t1 add partition (partition p2 values less than (30));
|
alter table t1 convert table pn to partition pn values less than maxvalue;
|
Result
alter table t1 convert table pn to partition pn values less than maxvalue;
|
ERROR 1737 (HY000): Found a row that does not match the partition
|
|