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

Range partition: allow ADD PARTITION into the middle of ranges list

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Partitioning
    • None

    Description

      1. Fast ADD case: existing rows do not match new partition range

      Reproduce

      create or replace table t1 (x int)
      		 
      partition by range columns (x) (
      		 
        partition p0 values less than (10),
      		 
        partition p1 values less than (20),
      		 
        partition pn values less than maxvalue);
      		 
       
      		 
      insert into t1 values (1);
      		 
      insert into t1 values (11);
      		 
      insert into t1 values (31);
      		 
       
      		 
      alter table t1 add partition (partition p2 values less than (30));

      Result

      alter table t1 add partition (partition p2 values less than (30));
      		 
      ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

      Expected

      Partition p2 added between p1 and pn.

      Workaround via CONVERT PARTITION

      create or replace table t1 (x int)
      		 
      partition by range columns (x) (
      		 
        partition p0 values less than (10),
      		 
        partition p1 values less than (20),
      		 
        partition pn values less than maxvalue);
      		 
       
      		 
      insert into t1 values (1);
      		 
      insert into t1 values (11);
      		 
      insert into t1 values (31);
      		 
       
      		 
      alter table t1 convert partition pn to table pn;
      		 
      alter table t1 add partition (partition p2 values less than (30));
      		 
      alter table t1 convert table pn to partition pn values less than maxvalue;

      Workaround result

      CREATE TABLE `t1` (
      		 
        `x` int(11) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
      		 
       PARTITION BY RANGE  COLUMNS(`x`)
      		 
      (PARTITION `p0` VALUES LESS THAN (10) ENGINE = InnoDB,
      		 
       PARTITION `p1` VALUES LESS THAN (20) ENGINE = InnoDB,
      		 
       PARTITION `p2` VALUES LESS THAN (30) ENGINE = InnoDB,
      		 
       PARTITION `pn` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

      2. Copy case: existing rows match new partition range

      Reproduce

      create or replace table t1 (x int)
      		 
      partition by range columns (x) (
      		 
        partition p0 values less than (10),
      		 
        partition p1 values less than (20),
      		 
        partition pn values less than maxvalue);
      		 
       
      		 
      insert into t1 values (21);
      		 
       
      		 
      alter table t1 add partition (partition p2 values less than (30));

      Expected

      Partition p2 is added between p1 and pn; row 21 is moved to partition p2.

      Workaround via CONVERT PARTITION does not work (MDEV-31213)

      create or replace table t1 (x int)
      		 
      partition by range columns (x) (
      		 
        partition p0 values less than (10),
      		 
        partition p1 values less than (20),
      		 
        partition pn values less than maxvalue);
      		 
       
      		 
      insert into t1 values (21);
      		 
       
      		 
      alter table t1 convert partition pn to table pn;
      		 
      alter table t1 add partition (partition p2 values less than (30));
      		 
      alter table t1 convert table pn to partition pn values less than maxvalue;

      Result

      alter table t1 convert table pn to partition pn values less than maxvalue;
      		 
      ERROR 1737 (HY000): Found a row that does not match the partition

      Attachments

        Issue Links

          is blocked by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-27180 Fully atomic partitioning DDL operations

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-25495 Auto-drop old history partition for system-versioned tables that use auto-creation

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

          Task - A task that needs to be done. MDEV-31213 CONVERT TABLE TO PARTITION: allow copy rows to avoid partition restrictions

          • Major - Major loss of function.
          • Open

          Activity

            People

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