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

CONVERT TABLE: move in partition from existing table

Details

    Description

      Adding a new table to a partitioned table is now quite cumbersome:
      ('archive' is here a partitioned table and p1 is a normal table)

      ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200));
      ALTER TABLE archive EXCHANGE PARTITION p1 WITH TABLE p1;
      DROP TABLE p1;

      It would be nice to be able to do this in one step:
      ALTER TABLE archive CONVERT TABLE tp1 TO PARTITION p1 VALUES LESS THAN (200);

      The operation crash safety must be protected by DDL logging.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29841 Partition by system_time can be converted into table but not back

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31014 Database privileges are insufficient for CONVERT TABLE TO PARTITION

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33131 Server crash or assertion failure upon CONVERT <temporary table> TO PARTITION

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33146 CONVERT TABLE TO PARTITION does not work for default list partition

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33154 Confusing error ER_PARTITION_INSTEAD_OF_SUBPARTITION upon trying to convert table to partition

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33170 ASAN errors upon CONVERT TABLE TO PARTITION with query cache

          • Major - Major loss of function.
          • Open
          is blocked by

          Task - A task that needs to be done. MDEV-17567 Atomic DDL

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

          Task - A task that needs to be done. MDEV-22162 Make partitions more flexible to use

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-17567 Atomic DDL

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTITION/CONVERT IN

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

          Task - A task that needs to be done. MDEV-22166 CONVERT PARTITION: move out partition into a table

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

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31014 Database privileges are insufficient for CONVERT TABLE TO PARTITION

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 7 older comments
            f_razzoli Federico Razzoli added a comment -

            What happens if the two tables are different in one of these aspects?

            • Indexes
            • Same visible indexes, but different IGNORED indexes
            • INVISIBLE columns
            • Compressed columns
            • CHECK
            • Type size (SMALLINT -> INT)
            • NULL / NOT NULL
            • Storage engine

            Indexes differences are not unlikely, in my opinion.

            What happens if the original table has triggers?

            f_razzoli Federico Razzoli added a comment - What happens if the two tables are different in one of these aspects? Indexes Same visible indexes, but different IGNORED indexes INVISIBLE columns Compressed columns CHECK Type size (SMALLINT -> INT) NULL / NOT NULL Storage engine Indexes differences are not unlikely, in my opinion. What happens if the original table has triggers?
            serg Sergei Golubchik added a comment -

            Somehow I don't see how the original

            ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) FROM TABLE p1;

            is worse or more confusing than

            ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) MIGRATE FROM TABLE p1;

            if anything, the former is shorter and only uses familiar keywords, it doesn't introduce a new keyword into the grammar

            serg Sergei Golubchik added a comment - Somehow I don't see how the original ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) FROM TABLE p1; is worse or more confusing than ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)) MIGRATE FROM TABLE p1; if anything, the former is shorter and only uses familiar keywords, it doesn't introduce a new keyword into the grammar
            serg Sergei Golubchik added a comment -

            f_razzoli, this feature introduces a convenience command to replace the following sql script:

            ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200));
            		 
            ALTER TABLE archive EXCHANGE PARTITION p1 WITH TABLE p1;
            		 
            DROP TABLE p1;

            it does not allow to do anything that wasn't possible before and it should generally behave exactly as the script above. Also in regards to indexes, invisible columns, constraints, etc.

            serg Sergei Golubchik added a comment - f_razzoli , this feature introduces a convenience command to replace the following sql script: ALTER TABLE archive ADD PARTITION (PARTITION p1 VALUES LESS THAN (200)); ALTER TABLE archive EXCHANGE PARTITION p1 WITH TABLE p1; DROP TABLE p1; it does not allow to do anything that wasn't possible before and it should generally behave exactly as the script above. Also in regards to indexes, invisible columns, constraints, etc.
            midenok Aleksey Midenkov added a comment -

            Please review bb-10.7-midenok-MDEV-22165

            midenok Aleksey Midenkov added a comment - Please review bb-10.7-midenok-MDEV-22165
            serg Sergei Golubchik added a comment -

            pushed together with MDEV-22166

            serg Sergei Golubchik added a comment - pushed together with MDEV-22166

            People

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