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

DELETE HISTORY may delete current data on system-versioned table

    XMLWordPrintable

Details

    Description

      MDEV-25467 is (tentatively) a documentation bug for DELETE HISTORY on active rows to be documented.
      However, the test case in MDEV-25467, on a non-partitioned table, at least appears to be deterministic, it always acts as described – active rows get "deleted", i.e. moved to history.

      It is not the case for partitioned tables. The same test case as in MDEV-25467, but on a partitioned table, produces non-deterministic results:

      --source include/have_innodb.inc
      --source include/have_partition.inc
       
      SET SESSION TIME_ZONE= '+00:00';
       
      CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY KEY() PARTITIONS 2;
      INSERT INTO t1 VALUES (1),(2);
       
      SELECT pk, row_end FROM t1;
      DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01';
      SELECT pk FROM t1;
      SELECT pk FROM t1 FOR SYSTEM_TIME ALL;
      DROP TABLE t1;
      

      10.3 7588049374

      MariaDB [test]> DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01';
      Query OK, 2 rows affected (0.003 sec)
       
      MariaDB [test]> SELECT pk FROM t1;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT pk FROM t1 FOR SYSTEM_TIME ALL;
      +----+
      | pk |
      +----+
      |  1 |
      |  2 |
      +----+
      2 rows in set (0.000 sec)
      

      10.3 7588049374

      MariaDB [test]> DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01';
      Query OK, 4 rows affected (0.002 sec)
       
      MariaDB [test]> SELECT pk FROM t1;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT pk FROM t1 FOR SYSTEM_TIME ALL;
      Empty set (0.000 sec)
      

      That is, sometimes it moves active rows to history, and sometimes drops them completely. And sometimes, although much less often, it moves one row to history but drops another one completely:

      MariaDB [test]> DELETE HISTORY FROM t1 BEFORE SYSTEM_TIME '2039-01-01';
      Query OK, 3 rows affected (0.008 sec)
       
      MariaDB [test]> SELECT pk FROM t1;
      Empty set (0.000 sec)
       
      MariaDB [test]> SELECT pk FROM t1 FOR SYSTEM_TIME ALL;
      +----+
      | pk |
      +----+
      |  1 |
      +----+
      1 row in set (0.000 sec)
      

      Attachments

        Issue Links

          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.