[MDEV-33153] EXCHANGE PARTITION leads to discrepancy in per-partition engine-defined attributes - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.8, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4
Fix Version/s: 10.11, 11.0, 11.1, 11.2, 11.3
Component/s: Partitioning
Labels:
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

Activity

People

Assignee:: Alexey Botchkov

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2024-01-02 13:43

Updated:: 2024-01-02 14:04

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.