Description:
The partition definition is not included in the *.cfg file. As a result, the 'ALTER TABLE... IMPORT TABLESPACE' command will succeed even if the partitions mismatch. For instance, if table 't2' is partitioned by range into partitions 'p0' containing values less than 500, and 'p1' containing values less than maxvalue, and then imported from a backup with partitions 'p0' containing values less than 100 and 'p1' containing values less than maxvalue. After then, only 100 values will be returned if values less than 500 are selected.
How to repeat:
version 8.0.33
commitId: ea7087d885006918ad54458e7aad215b1650312c
let $MYSQLD_DATADIR= `select @@datadir`;
--exec mkdir -p "$MYSQL_TMP_DIR/backup_test"
create table t1(id int primary key, uid int) engine=innodb
partition by range(id) (
partition p0 values less than (100),
partition p1 values less than maxvalue
);
create table t2(id int primary key, uid int) engine=innodb
partition by range(id) (
partition p0 values less than (500),
partition p1 values less than maxvalue
);
delimiter |;
create procedure test.sp1(in count bigint)
begin
set @i=0;
while (@i < count) do
insert into t1 values(@i, @i);
set @i=@i+1;
end while;
end |
delimiter ;|
call test.sp1(1000);
flush table t1 for export;
copy_files_wildcard $MYSQLD_DATADIR/test/ $MYSQL_TMP_DIR/backup_test/ *;
unlock tables;
alter table t2 discard tablespace;
copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p0.cfg $MYSQLD_DATADIR/test/t2#p#p0.cfg;
copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p0.ibd $MYSQLD_DATADIR/test/t2#p#p0.ibd;
copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p1.cfg $MYSQLD_DATADIR/test/t2#p#p1.cfg;
copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p1.ibd $MYSQLD_DATADIR/test/t2#p#p1.ibd;
alter table t2 import tablespace;
select count(*) from t2 where id < 500;
select count(*) from t2 where id >= 500;
drop table t1;
drop table t2;
drop procedure test.sp1;
force-rmdir $MYSQL_TMP_DIR/backup_test;
Suggested fix:
One possible solution is to extend the *.cfg file format to include partition definition, and response schema mismatch error if the definition differs.
Description: The partition definition is not included in the *.cfg file. As a result, the 'ALTER TABLE... IMPORT TABLESPACE' command will succeed even if the partitions mismatch. For instance, if table 't2' is partitioned by range into partitions 'p0' containing values less than 500, and 'p1' containing values less than maxvalue, and then imported from a backup with partitions 'p0' containing values less than 100 and 'p1' containing values less than maxvalue. After then, only 100 values will be returned if values less than 500 are selected. How to repeat: version 8.0.33 commitId: ea7087d885006918ad54458e7aad215b1650312c let $MYSQLD_DATADIR= `select @@datadir`; --exec mkdir -p "$MYSQL_TMP_DIR/backup_test" create table t1(id int primary key, uid int) engine=innodb partition by range(id) ( partition p0 values less than (100), partition p1 values less than maxvalue ); create table t2(id int primary key, uid int) engine=innodb partition by range(id) ( partition p0 values less than (500), partition p1 values less than maxvalue ); delimiter |; create procedure test.sp1(in count bigint) begin set @i=0; while (@i < count) do insert into t1 values(@i, @i); set @i=@i+1; end while; end | delimiter ;| call test.sp1(1000); flush table t1 for export; copy_files_wildcard $MYSQLD_DATADIR/test/ $MYSQL_TMP_DIR/backup_test/ *; unlock tables; alter table t2 discard tablespace; copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p0.cfg $MYSQLD_DATADIR/test/t2#p#p0.cfg; copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p0.ibd $MYSQLD_DATADIR/test/t2#p#p0.ibd; copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p1.cfg $MYSQLD_DATADIR/test/t2#p#p1.cfg; copy_file $MYSQL_TMP_DIR/backup_test/t1#p#p1.ibd $MYSQLD_DATADIR/test/t2#p#p1.ibd; alter table t2 import tablespace; select count(*) from t2 where id < 500; select count(*) from t2 where id >= 500; drop table t1; drop table t2; drop procedure test.sp1; force-rmdir $MYSQL_TMP_DIR/backup_test; Suggested fix: One possible solution is to extend the *.cfg file format to include partition definition, and response schema mismatch error if the definition differs.