Description:
Case 1:
When reorganize partition operation changed partitioning ranges or list values,it would delete the data which does not ment new partitioning definition.
Specific examples are as follows:
mysql> create table t2 (a int, b int) partition by list(a) (
-> partition p1 values in (1,2,3),
-> partition p2 values in (4,5),
-> partition p3 values in (6)
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> insert into t2 values(1,5),(4,5);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
// New partition reduced list values range and value(4,5) does not meet the definition of the new partition. This query should report error but, actually, it was successful.
mysql> alter table t2 reorganize partition p1,p2 into (
-> partition p1 values in (1,2,3,5)
-> );
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
// value (4,5) lossed.
mysql> select * from t2;
+------+------+
| a | b |
+------+------+
| 1 | 5 |
+------+------+
1 row in set (0.00 sec)
Case 2: When the partition includes invalid data and this data meet other partition definition, and reorganize partition operation report error.
mysql> CREATE TABLE `tsp` (
-> `a` int NOT NULL,
-> `b` varchar(55) DEFAULT NULL,
-> PRIMARY KEY (`a`)
-> ) PARTITION BY RANGE (`a`)
-> SUBPARTITION BY HASH (`a`)
-> (PARTITION p0 VALUES LESS THAN (100)
-> (SUBPARTITION sp0 ENGINE = InnoDB,
-> SUBPARTITION sp1 ENGINE = InnoDB),
-> PARTITION p1 VALUES LESS THAN MAXVALUE
-> (SUBPARTITION sp2 ENGINE = InnoDB,
-> SUBPARTITION sp3 ENGINE = InnoDB));
Query OK, 0 rows affected (0.16 sec)
mysql> create table t1 like tsp;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into t1(a) values(162),(164),(200),(1),(3);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> alter table t1 remove partitioning;
Query OK, 5 rows affected (0.26 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t1 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.08 sec)
mysql> ALTER TABLE tsp REORGANIZE PARTITION p1 INTO
-> (PARTITION p1 VALUES LESS THAN (162),
-> PARTITION p2 VALUES LESS THAN MAXVALUE);
ERROR 1863 (HY000): Found a row in wrong partition from REBUILD/REORGANIZED partition: 1 to non included partition (new definition): 1 a:1 b:NULL
I suggest that the data of reorganized partitions only needs to be checked within the new partition definition, and if it does not meet the requirements, an error is reported, instead of checking on all partitions.
How to repeat:
create table t2 (a int, b int) partition by list(a) (
partition p1 values in (1,2,3),
partition p2 values in (4,5),
partition p3 values in (6)
);
insert into t2 values(1,5),(4,5);
alter table t2 reorganize partition p1,p2 into (
partition p1 values in (1,2,3,5)
);
select * from t2;
Suggested fix:
I suggest that the data of reorganized partitions only needs to be checked within the new partition definition, and if it does not meet the requirements, an error is reported, instead of checking on all partitions. Errors must be reported in the preceding two scenarios and the error information must be consistent.
Description: Case 1: When reorganize partition operation changed partitioning ranges or list values,it would delete the data which does not ment new partitioning definition. Specific examples are as follows: mysql> create table t2 (a int, b int) partition by list(a) ( -> partition p1 values in (1,2,3), -> partition p2 values in (4,5), -> partition p3 values in (6) -> ); Query OK, 0 rows affected (0.14 sec) mysql> insert into t2 values(1,5),(4,5); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 // New partition reduced list values range and value(4,5) does not meet the definition of the new partition. This query should report error but, actually, it was successful. mysql> alter table t2 reorganize partition p1,p2 into ( -> partition p1 values in (1,2,3,5) -> ); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 // value (4,5) lossed. mysql> select * from t2; +------+------+ | a | b | +------+------+ | 1 | 5 | +------+------+ 1 row in set (0.00 sec) Case 2: When the partition includes invalid data and this data meet other partition definition, and reorganize partition operation report error. mysql> CREATE TABLE `tsp` ( -> `a` int NOT NULL, -> `b` varchar(55) DEFAULT NULL, -> PRIMARY KEY (`a`) -> ) PARTITION BY RANGE (`a`) -> SUBPARTITION BY HASH (`a`) -> (PARTITION p0 VALUES LESS THAN (100) -> (SUBPARTITION sp0 ENGINE = InnoDB, -> SUBPARTITION sp1 ENGINE = InnoDB), -> PARTITION p1 VALUES LESS THAN MAXVALUE -> (SUBPARTITION sp2 ENGINE = InnoDB, -> SUBPARTITION sp3 ENGINE = InnoDB)); Query OK, 0 rows affected (0.16 sec) mysql> create table t1 like tsp; Query OK, 0 rows affected (0.15 sec) mysql> insert into t1(a) values(162),(164),(200),(1),(3); Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> alter table t1 remove partitioning; Query OK, 5 rows affected (0.26 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tsp EXCHANGE PARTITION sp3 WITH TABLE t1 WITHOUT VALIDATION; Query OK, 0 rows affected (0.08 sec) mysql> ALTER TABLE tsp REORGANIZE PARTITION p1 INTO -> (PARTITION p1 VALUES LESS THAN (162), -> PARTITION p2 VALUES LESS THAN MAXVALUE); ERROR 1863 (HY000): Found a row in wrong partition from REBUILD/REORGANIZED partition: 1 to non included partition (new definition): 1 a:1 b:NULL I suggest that the data of reorganized partitions only needs to be checked within the new partition definition, and if it does not meet the requirements, an error is reported, instead of checking on all partitions. How to repeat: create table t2 (a int, b int) partition by list(a) ( partition p1 values in (1,2,3), partition p2 values in (4,5), partition p3 values in (6) ); insert into t2 values(1,5),(4,5); alter table t2 reorganize partition p1,p2 into ( partition p1 values in (1,2,3,5) ); select * from t2; Suggested fix: I suggest that the data of reorganized partitions only needs to be checked within the new partition definition, and if it does not meet the requirements, an error is reported, instead of checking on all partitions. Errors must be reported in the preceding two scenarios and the error information must be consistent.