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

ALGORITHM=INSTANT does not work for partitioned tables on indexed column

    XMLWordPrintable

Details

    Description

      If a table have partitions and you try to change a column
      (modify or change) with an index, which is not the partitions key,
      algorithm = instant not work.

      MariaDB [l1]> ALTER online TABLE t1 MODIFY COLUMN `f4`  VARCHAR(500) , ALGORITHM=INSTANT, LOCK=NONE;
      		 
      ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY

      To reproduce:

       
      		 
       
      		 
      drop table if exists t1;
      		 
       
      		 
       
      		 
      CREATE TABLE `t1` ( 
          `f1` datetime , 
          `f2` VARCHAR(2)  , 
          `f3` VARCHAR(200) ,
      		 
          `f4` VARCHAR(100)  ,
      		 
          INDEX `i3` (`f4`)  ) 
          /*!50100 PARTITION BY RANGE COLUMNS(`f2`) (PARTITION `p_01` VALUES LESS THAN ('02') ENGINE = InnoDB,  PARTITION `p_31` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
      		 
       
      		 
       
      		 
      ALTER online TABLE t1 MODIFY COLUMN `f4`  VARCHAR(500) , ALGORITHM=INSTANT, LOCK=NONE;

      If you do the testcase without index
      OR !
      without partition term,
      it works.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16282 ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY fails

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables

          • Major - Major loss of function.
          • Closed

          Activity

            People

              ycp Yuchen Pei
              Richard Richard Stracke
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.