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

ALGORITHM=INSTANT does not work for partitioned tables

    XMLWordPrintable

    Details

      Description

      KB article (https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/#alter-table-modify-column) does not list any restrictions related to partitioned tables, while in reality ALGORITHM=INSTANT can not be applied to them.

      Consider the following primitive example:

      MariaDB [test]> create table tpa(id int, c1 varchar(10)) partition by range(id) (partition p1 values less than (10), partition p2 values less than (1000));
      Query OK, 0 rows affected (0,310 sec)
       
      MariaDB [test]> ALTER TABLE tpa MODIFY COLUMN c1 VARCHAR(30), LOCK=NONE, ALGORITHM=INSTANT;
      ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
      MariaDB [test]> ALTER TABLE tpa remove partitioning;                            
      Query OK, 0 rows affected (0,757 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> ALTER TABLE tpa MODIFY COLUMN c1 VARCHAR(30), LOCK=NONE, ALGORITHM=INSTANT;
      Query OK, 0 rows affected (0,076 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.4.12-MariaDB |
      +-----------------+
      1 row in set (0,000 sec)
      

      As soon as remove partitioning, ALTER works as expected.

      So, please, either document this limitation or fix the bug in the code that does not allow to do INSTANT alters for partitioned tables even in a simplest case like presented above.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: