Description:
When I import the wrong partition tablespace, MySQL does not validate the data, leading to incorrect results.
Assuming a partition table includes two partitions p0 and p1, MySQL allows me to replace the p0 with p1.
CREATE TABLE `t1` (
`id` int NOT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 2 */;
Although the id is unique, I can get duplicated records.
mysql> select * from t1;
+----+------+
| id | d |
+----+------+
| 1 | 1 |
| 1 | 1 |
+----+------+
2 rows in set (0.01 sec)
How to repeat:
create database test;
use test
CREATE TABLE `t1` (
`id` int NOT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 2 */;
insert into t1 values (1,1), (2,2);
flush tables t1 for export;
system cp -av /var/lib/mysql/test/t1#p#p1.* /tmp/
unlock tables;
alter table t1 discard partition p0 tablespace;
system cp -av /tmp/t1#p#p1.cfg /var/lib/mysql/test/t1#p#p0.cfg
system cp -av /tmp/t1#p#p1.ibd /var/lib/mysql/test/t1#p#p0.ibd
alter table t1 import partition p0 tablespace;
select * from t1;
