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

Auto-create: LIMIT partitions are not auto-created quite as documented

    XMLWordPrintable

Details

    Description

      According to the description in MDEV-17554,

      Before executing history-generating DML command add N history
      partitions, so that N would be sufficient for potentially generated
      history

      It doesn't happen this way at least for LIMIT partitions.
      (MyISAM is used to avoid unreliable InnoDB statistics, otherwise it is not important for the issue).

      create or replace table t (x int) engine=MyISAM with system versioning partition by system_time limit 1000 auto;
      insert into t select seq from seq_1_to_1500;
      

      At these point there is one history partition, p0, with capacity 1000.

      MariaDB [test]> delete from t limit 800;
      Query OK, 800 rows affected (0.081 sec)
       
      MariaDB [test]> select count(*) from t partition (p0);
      +----------+
      | count(*) |
      +----------+
      |      800 |
      +----------+
      1 row in set (0.012 sec)
      

      Next DELETE of 600 rows should, according to the specification, create a new partition, but it doesn't, it still places all rows into p0, overflowing it (without a warning):

      bb-10.6-midenok-MDEV-17554 edbc73ac48

      MariaDB [test]> delete from t limit 600;
      Query OK, 600 rows affected (0.086 sec)
       
      MariaDB [test]> select count(*) from t partition (p0);
      +----------+
      | count(*) |
      +----------+
      |     1400 |
      +----------+
      1 row in set (0.014 sec)
       
      MariaDB [test]> show create table t;
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                       |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t     | CREATE TABLE `t` (
        `x` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
       PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO
      PARTITIONS 2 |
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      And only the next DELETE, even a small one, creates a new partition:

      MariaDB [test]> delete from t limit 10;
      Query OK, 10 rows affected (0.159 sec)
       
      MariaDB [test]> select count(*) from t partition (p0);
      +----------+
      | count(*) |
      +----------+
      |     1400 |
      +----------+
      1 row in set (0.015 sec)
       
      MariaDB [test]> select count(*) from t partition (p1);
      +----------+
      | count(*) |
      +----------+
      |       10 |
      +----------+
      1 row in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.