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

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

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25476 Auto-create: DML exceeding LIMIT size does not cause a warning

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-17554 Auto-create history partitions for system-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Aleksey Midenkov made transition -
            Open Closed
            		1d 1h 20m 1

            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.