Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33153

EXCHANGE PARTITION leads to discrepancy in per-partition engine-defined attributes

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.8(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
    • 10.11
    • Partitioning
    • 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

          Task - A task that needs to be done. MDEV-5271 Support engine-defined attributes per partition

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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