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

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

    XMLWordPrintable

Details

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

          Activity

            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.