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

Auto-create: If XA transaction is rolled back the created partitions are kept empty and not reused

    XMLWordPrintable

    Details

      Description

      Test:

      -- source include/have_partition.inc
      -- source include/have_sequence.inc
      -- source include/have_innodb.inc
       
      SET @save_persistent=@@GLOBAL.innodb_stats_persistent;
      SET GLOBAL innodb_stats_persistent=0;
       
      set @@system_versioning_alter_history = keep;
       
      create table t2 (
        x int,
        y int
      ) engine=InnoDB with system versioning partition by system_time interval 1 minute auto;
       
      insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5);
       
      delete from t2 where x=1;
      sleep 62;
      update t2 set x=10 where y=4;
       
      select * from t2 partition (p0);
      select * from t2 partition (p1);
       
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
       
       
      XA START 'xid';
      sleep 62;
      update t2 set y=213 where x=2;
       
      sleep 62;
      update t2 set y=919 where x=5;
       
      sleep 62;
      update t2 set y=2828 where x=5;
      select * from t2 partition (p0);
      select * from t2 partition (p1);
      select * from t2 partition (p2);
      select * from t2 partition (p3);
      select * from t2 partition (p4);
       
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
       
      XA END 'xid';
      XA ROLLBACK 'xid';
       
      select * from t2 partition (p0);
      select * from t2 partition (p1);
      select * from t2 partition (p2);
      select * from t2 partition (p3);
      select * from t2 partition (p4);
       
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
       
      sleep 62;
      update t2 set y=9009 where x=2;
      select * from t2 partition (p0);
      select * from t2 partition (p1);
      select * from t2 partition (p2);
      select * from t2 partition (p3);
      select * from t2 partition (p4);
       
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
       
      drop table t2;
       
      SET GLOBAL innodb_stats_persistent=@save_persistent;
      

      Result:

      SET @save_persistent=@@GLOBAL.innodb_stats_persistent;
      SET GLOBAL innodb_stats_persistent=0;
      set @@system_versioning_alter_history = keep;
      create table t2 (
      x int,
      y int
      ) engine=InnoDB with system versioning partition by system_time interval 1 minute auto;
      insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5);
      delete from t2 where x=1;
      update t2 set x=10 where y=4;
      select * from t2 partition (p0);
      x	y
      1	1
      select * from t2 partition (p1);
      x	y
      4	4
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
      partition_name	table_rows
      p0	1
      p1	1
      pn	4
      XA START 'xid';
      update t2 set y=213 where x=2;
      update t2 set y=919 where x=5;
      update t2 set y=2828 where x=5;
      select * from t2 partition (p0);
      x	y
      1	1
      select * from t2 partition (p1);
      x	y
      4	4
      select * from t2 partition (p2);
      x	y
      2	2
      select * from t2 partition (p3);
      x	y
      5	5
      select * from t2 partition (p4);
      x	y
      5	919
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
      partition_name	table_rows
      p0	1
      p1	1
      p2	1
      p3	1
      p4	1
      pn	4
      XA END 'xid';
      XA ROLLBACK 'xid';
      select * from t2 partition (p0);
      x	y
      1	1
      select * from t2 partition (p1);
      x	y
      4	4
      select * from t2 partition (p2);
      x	y
      select * from t2 partition (p3);
      x	y
      select * from t2 partition (p4);
      x	y
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
      partition_name	table_rows
      p0	1
      p1	1
      p2	0
      p3	0
      p4	0
      pn	4
      update t2 set y=9009 where x=2;
      select * from t2 partition (p0);
      x	y
      1	1
      select * from t2 partition (p1);
      x	y
      4	4
      select * from t2 partition (p2);
      x	y
      select * from t2 partition (p3);
      x	y
      select * from t2 partition (p4);
      x	y
      select partition_name, table_rows from information_schema.partitions where table_name = 't2';
      partition_name	table_rows
      p0	1
      p1	1
      p2	0
      p3	0
      p4	0
      p5	1
      pn	4
      drop table t2;
      SET GLOBAL innodb_stats_persistent=@save_persistent;
      

      Partitions p2, p3, p4 are kept and empty

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.