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

SYSTEM VERSIONING with LIMIT clause does not work as expected

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.9.8
    • N/A
    • Versioned Tables
    • None

    Description

      As per the KB, https://mariadb.com/kb/en/system-versioned-tables/#storing-the-history-separately

      Partitioning by SYSTEM_TIME also supports automatic partition rotation. One can rotate historical partitions by time or by size. This example shows how to rotate partitions by size:

      CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
      PARTITION BY SYSTEM_TIME LIMIT 100000 (
      PARTITION p0 HISTORY,
      PARTITION p1 HISTORY,
      PARTITION pcur CURRENT
      );
      MariaDB will start writing history rows into partition p0, and when it reaches a size of 100000 rows, MariaDB will switch to partition p1. There are only two historical partitions, so when p1 overflows, MariaDB will issue a warning, but will continue writing into it.


      But this does not work as expected.
      Although it generates appropriate warning when the number of historical rows are more than the LIMIT.

      MariaDB [test]> CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
          ->   PARTITION BY SYSTEM_TIME LIMIT 5 (
          ->     PARTITION p0 HISTORY,
          ->     PARTITION p1 HISTORY,
          ->     PARTITION pcur CURRENT
          -> );
      Query OK, 0 rows affected (0.029 sec)
       
      MariaDB [test]> INSERT INTO t VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
      Query OK, 10 rows affected (0.005 sec)
      Records: 10  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> DELETE FROM t;
      Query OK, 10 rows affected (0.010 sec)
       
      MariaDB [test]> select * from t partition(p0);
      +------+
      | x    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      |    8 |
      |    9 |
      |   10 |
      +------+
      10 rows in set (0.000 sec)
       
      MariaDB [test]> INSERT INTO t VALUES(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
      Query OK, 10 rows affected (0.008 sec)
      Records: 10  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> DELETE FROM t;
      Query OK, 10 rows affected, 1 warning (0.003 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+---------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                 |
      +---------+------+---------------------------------------------------------------------------------------------------------+
      | Warning | 4114 | Versioned table `test`.`t`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions |
      +---------+------+---------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from t partition(p1);
      +------+
      | x    |
      +------+
      |   11 |
      |   12 |
      |   13 |
      |   14 |
      |   15 |
      |   16 |
      |   17 |
      |   18 |
      |   19 |
      |   20 |
      +------+
      10 rows in set (0.001 sec)
      

      I tried to trigger a reorganize but that made it even worse

      MariaDB [test]> ALTER TABLE t PARTITION BY SYSTEM_TIME LIMIT 5 (
          ->     PARTITION p0 HISTORY,
          ->     PARTITION p1 HISTORY,
          ->     PARTITION pcur CURRENT
          -> );
      Query OK, 20 rows affected (0.104 sec)
      Records: 20  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t partition(p0);
      +------+
      | x    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      |    8 |
      |    9 |
      |   10 |
      |   11 |
      |   12 |
      |   13 |
      |   14 |
      |   15 |
      |   16 |
      |   17 |
      |   18 |
      |   19 |
      |   20 |
      +------+
      20 rows in set (0.013 sec)
       
      MariaDB [test]> select * from t partition(p1);
      Empty set (0.003 sec)
      
      

      Attachments

        Issue Links

          Activity

            This is not a bug. MariaDB checks the partition size limit only once per statement, not for every row (which would've been prohibitively expensive). I've amended the manual as

            MariaDB will start writing history rows into partition p0, and at the end of the statement that wrote the 100000th row, MariaDB will switch to partition p1.

            serg Sergei Golubchik added a comment - This is not a bug. MariaDB checks the partition size limit only once per statement, not for every row (which would've been prohibitively expensive). I've amended the manual as MariaDB will start writing history rows into partition p0 , and at the end of the statement that wrote the 100000th row, MariaDB will switch to partition p1 .

            People

              serg Sergei Golubchik
              susmeet.khaire Susmeet Khaire
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.