Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.8(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2, 11.3(EOL), 11.4
-
None
Description
--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) |
Attachments
Issue Links
- is caused by
-
MDEV-5271 Support engine-defined attributes per partition
-
- Closed
-