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

Auto-create: Pre-existing historical data is not partitioned as specified by ALTER

    XMLWordPrintable

    Details

      Description

      I have a system-versioned table with some historical data, e.g.

      MariaDB [test]> show create table t \G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `pk` int(11) NOT NULL,
        `a` int(11) DEFAULT NULL,
        PRIMARY KEY (`pk`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
      1 row in set (0.000 sec)
       
      MariaDB [test]> select year(row_end) y, count(*) from t for system_time all group by y;
      +------+----------+
      | y    | count(*) |
      +------+----------+
      | 2019 |        8 |
      | 2020 |       12 |
      | 2021 |        4 |
      | 2038 |        2 |
      +------+----------+
      4 rows in set (0.001 sec)
      

      Now I want to make it auto-partitioned, so I run ALTER TABLE:

      MariaDB [test]> alter table t partition by system_time interval 1 year auto;
      Query OK, 26 rows affected (0.017 sec)             
      Records: 26  Duplicates: 0  Warnings: 0
      

      My expectation is that it will auto-create the partition structure suitable for the already existing historical data – that is, 2 or 3 historical partitions starting from the lowest row_end timestamp with 1 year interval. It doesn't happen; instead, the table gets 1 historical partition starting from the current time, and all historical data is put in there:

      bb-10.6-midenok-MDEV-17554 b96b96f9f

      MariaDB [test]> show create table t \G
      *************************** 1. row ***************************
             Table: t
      Create Table: CREATE TABLE `t` (
        `pk` int(11) NOT NULL,
        `a` int(11) DEFAULT NULL,
        PRIMARY KEY (`pk`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
       PARTITION BY SYSTEM_TIME INTERVAL 1 YEAR STARTS TIMESTAMP'2021-04-26 00:00:00' AUTO
      PARTITIONS 2
      1 row in set (0.000 sec)
       
      MariaDB [test]> select count(*) from t partition (p0);
      +----------+
      | count(*) |
      +----------+
      |       24 |
      +----------+
      1 row in set (0.000 sec)
      

      It is even semantically wrong, 2019- and 2020-year data shouldn't be in a partition with start time in 2021.

      Admittedly non-auto partitioning would do it too, but it is somewhat natural to expect more intelligent action from "auto".

      With LIMIT instead of INTERVAL, sufficient partitions aren't created either, but at least with LIMIT there is no "semantically wrong" aspect, the partition simply gets overflowed, which can happen anyway.

      If the behavior is intentional, please document it (and in general, please make sure that the documentation says what happens upon ALTER TABLE .. PARTITION, as currently MDEV-17554 description only mentions CREATE TABLE and DML).

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:

                  Git Integration

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