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

ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY fails

Details

    Description

      When MDEV-11369 implemented instant ADD COLUMN, it would refuse to do instant ADD together with ADD INDEX.

      Adding an index to an instantly added column does not make much sense, because the index would be useless due to every row initially having the same value for the instantly added column.

      However, it is meaningful to ADD COLUMN together with ADD INDEX on some old column. This will currently cause the table to be rebuilt:

      --source include/have_innodb.inc
      		 
      create table t1(a int) engine=innodb;
      		 
      alter table t1 add column b int, add index(a), algorithm=nocopy;
      		 
      alter table t1 add column c int, add index(c), algorithm=nocopy;
      		 
      drop table t1;

      The first ALTER TABLE should be fixed to allow ALGORITHM=NOCOPY, but the second one can in my opinion continue to rebuild the table with ALGORITHM=INPLACE.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-11369 Instant add column for InnoDB

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-13134 Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15250 UPSERT during ALTER-TABLE results in 'Duplicate entry' error for alter

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

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

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

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä created issue -
            marko Marko Mäkelä made changes -
            Field Original Value New Value
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 10.4 [ 22408 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 87441 ] MariaDB v4 [ 143546 ]
            marko Marko Mäkelä added a comment -

            Because MDEV-15250 (a significant refactoring of InnoDB online ALTER TABLE) was only implemented starting with 10.6, I do not think that it makes sense to fix this in older versions.

            Also, 10.6 is the first release series where InnoDB ALTER TABLE is supposed to be crash-safe. It would be infeasible to test the crash-safety aspects of this fix in older major versions.

            marko Marko Mäkelä added a comment - Because MDEV-15250 (a significant refactoring of InnoDB online ALTER TABLE ) was only implemented starting with 10.6, I do not think that it makes sense to fix this in older versions. Also, 10.6 is the first release series where InnoDB ALTER TABLE is supposed to be crash-safe. It would be infeasible to test the crash-safety aspects of this fix in older major versions.
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Fix Version/s 10.10 [ 27530 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 10.12 [ 28320 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.8 [ 26121 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.10 [ 27530 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 11.0 [ 28320 ]
            marko Marko Mäkelä made changes -
            JIraAutomate JiraAutomate made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            thiru Thirunarayanan Balathandayuthapani made changes -
            thiru Thirunarayanan Balathandayuthapani added a comment -

            This shouldn't be a bug imo. It is a feature and it doesn't block MDEV-34813 like it mentioned. MDEV-34813 is related
            to partition. So decreasing the priority of the issue to Major.

            thiru Thirunarayanan Balathandayuthapani added a comment - This shouldn't be a bug imo. It is a feature and it doesn't block MDEV-34813 like it mentioned. MDEV-34813 is related to partition. So decreasing the priority of the issue to Major.
            thiru Thirunarayanan Balathandayuthapani made changes -
            Priority Critical [ 2 ] Major [ 3 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              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.