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

LIMIT partitioning does not respect ROLLBACK

    XMLWordPrintable

Details

    Description

      LIMIT partition temporarily populated in transaction is not used anymore after ROLLBACK

      Note: There can be a race condition/non-determinism during MTR execution of the test case, as statistics on an InnoDB table is not precise (described in not-a-bug MDEV-23640). I can't put ANALYZE in the test case, as it terminates the transaction. For reproducing, just re-run or put sleeps if necessary. For a regression suite, something smarter would need to be done to maintain determinism.

      If we have several empty (not full) LIMIT partitions, rows are put in the first one first, when it's overflows then next, etc – that's the expected behavior.
      Same happens when partition population happens inside a transaction.
      However if the transaction is rolled back and partitions again become empty, the orderly population does not happen anymore – partitions which were once (over)filled remain ignored, and rows are put into the next ones.

      It affects both explicit and implicit (including auto-created) partitions. The test case below is for explicit ones, to be applicable to all versions which support system versioning.

      --source include/have_partition.inc
      --source include/have_sequence.inc
      --source include/have_innodb.inc
       
      create or replace table t1 (pk int primary key) with system versioning
      partition by system_time limit 100 (
        partition p0 history,
        partition p1 history,
        partition pn current);
      insert into t1 select seq from seq_1_to_90;
       
      start transaction;
      # Puts 80 rows into p0
      replace into t1 select seq from seq_1_to_80;
      # Puts another 70 rows into p0
      replace into t1 select seq from seq_1_to_70;
      # Puts 60 rows into p1
      replace into t1 select seq from seq_1_to_60;
       
      select partition_name, table_rows
      from information_schema.partitions
      where table_name = 't1'; 
      rollback;
       
      select partition_name, table_rows
      from information_schema.partitions
      where table_name = 't1';
       
      # Should put 10 rows into the empty partition p0,
      # but instead they go to p1
      replace into t1 select seq from seq_1_to_10;
      select partition_name, table_rows
      from information_schema.partitions
      where table_name = 't1';
       # Cleanup
      drop table t1;
      

      10.3 4d412e98

      replace into t1 select seq from seq_1_to_80;
      replace into t1 select seq from seq_1_to_70;
      replace into t1 select seq from seq_1_to_60;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      partition_name	table_rows
      p0	150
      p1	60
      pn	90
      rollback;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      partition_name	table_rows
      p0	0
      p1	0
      pn	90
      replace into t1 select seq from seq_1_to_10;
      select partition_name, table_rows from information_schema.partitions where table_name = 't1';
      partition_name	table_rows
      p0	0
      p1	10
      pn	90
      

      Attachments

        Activity

          People

            midenok Aleksey Midenkov
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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