|
--source include/have_innodb.inc
|
--source include/have_partition.inc
|
|
--let $restart_parameters= --innodb-sys-tablespaces
|
--source include/restart_mysqld.inc
|
|
create table t (a int) engine=InnoDB
|
partition by list (a) (
|
partition p0 values in (1,2) page_compressed=yes,
|
partition p1 values in (7,8)
|
);
|
insert into t values (1),(8);
|
|
create table t1 (a int) engine=InnoDB;
|
insert into t1 values (1),(2);
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test/t%';
|
alter table t exchange partition p0 with table t1;
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
|
alter table t force;
|
alter table t1 force;
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
|
alter table t force, algorithm=COPY;
|
alter table t1 force, algorithm=COPY;
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
|
show create table t1;
|
show create table t;
|
|
# Cleanup
|
drop table if exists t, t1;
|
In the test case above, we exchange partition p0 of table t with table t1.
Originally the partition p0 has attribute page_compressed=yes. Table t1 doesn't.
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test/t%';
|
name flag
|
test/t#P#p0 1610612789
|
test/t#P#p1 21
|
test/t1 21
|
EXCHANGE does not recognize the difference and allows the operation.
Right after the exchange the flags on tablespaces become
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
name flag
|
test/t1 1610612789
|
test/t#P#p1 21
|
test/t#P#p0 21
|
while the table definitions remain as they were
show create table t1;
|
Table Create Table
|
t1 CREATE TABLE `t1` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
show create table t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
PARTITION BY LIST (`a`)
|
(PARTITION `p0` VALUES IN (1,2) ENGINE = InnoDB page_compressed = yes,
|
PARTITION `p1` VALUES IN (7,8) ENGINE = InnoDB)
|
that is, t1 tablespace has the compressed flag even although it shouldn't, while p0 doesn't have the flag even though it should – which is understandable, as they were merely swapped, but unfortunate.
Further, after the inplace operation ALTER TABLE .. FORCE, the flags become
name flag
|
test/t#P#p0 21
|
test/t#P#p1 21
|
test/t1 21
|
That is, none of tablespaces has the flag anymore.
And only after the copying ALTER, they become synchronized with the table definition
name flag
|
test/t#P#p0 1610612789
|
test/t#P#p1 21
|
test/t1 21
|
show create table t1;
|
Table Create Table
|
t1 CREATE TABLE `t1` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
show create table t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
PARTITION BY LIST (`a`)
|
(PARTITION `p0` VALUES IN (1,2) ENGINE = InnoDB page_compressed = yes,
|
PARTITION `p1` VALUES IN (7,8) ENGINE = InnoDB)
|
Maybe the core problem here is that EXCHANGE only checks that table definitions are identical, but doesn't take into account the possibility of per-partition attributes.
A somewhat similar situation occurs when a table is converted into a partition. A table without a flag is allowed to be converted into a partition with the flag, but the attribute isn't applied until the partition is rebuilt.
--source include/have_innodb.inc
|
--source include/have_partition.inc
|
|
--let $restart_parameters= --innodb-sys-tablespaces
|
--source include/restart_mysqld.inc
|
|
create table t (a int) engine=InnoDB
|
partition by list (a) (
|
partition p0 values in (1,2)
|
);
|
insert into t values (1);
|
|
create table t1 (a int) engine=InnoDB;
|
insert into t1 values (3),(4);
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test/t%';
|
alter table t convert table t1 to partition pn values in (3,4) page_compressed=yes;
|
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
|
alter table t force;
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
alter table t rebuild partition pn;
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
|
show create table t;
|
|
# Cleanup
|
drop table if exists t, t1;
|
|
11.4 875377ad824473774c833b1aff4346ba3133f092
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
name flag
|
test/t#P#p0 21
|
test/t#P#pn 21
|
alter table t force;
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
name flag
|
test/t#P#p0 21
|
test/t#P#pn 21
|
alter table t rebuild partition pn;
|
select name, flag from information_schema.innodb_sys_tablespaces where name like 'test%';
|
name flag
|
test/t#P#p0 21
|
test/t#P#pn 1610612789
|
show create table t;
|
Table Create Table
|
t CREATE TABLE `t` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
PARTITION BY LIST (`a`)
|
(PARTITION `p0` VALUES IN (1,2) ENGINE = InnoDB,
|
PARTITION `pn` VALUES IN (3,4) ENGINE = InnoDB page_compressed = yes)
|
|