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

Introduce ALTER TABLE attributes ALGORITHM=NOCOPY and ALGORITHM=INSTANT

    XMLWordPrintable

Details

    Description

      When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.

      This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild.

      The following was originally written in MDEV-11369, which would make certain cases of ADD COLUMN an instantaneous operation:

      It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation.

      ALGORITHM=INSTANT will refuse any operation that must modify any data in files. (Updates to metadata are possible.)

      ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table).

      With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY.

      Example:

      # No data file change (MDEV-11369 instant ADD COLUMN)
      ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT;
      # The following will change data files (ADD INDEX), but not rebuild the table:
      ALTER TABLE t ADD COLUMN c INT, ADD INDEX(c), ALGORITHM=NOCOPY;
      # The following are changing data files (not instant operation)
      --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
      ALTER TABLE t DROP INDEX c, ALGORITHM=INSTANT;
      --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
      ALTER TABLE t ADD COLUMN d INT, ADD INDEX(d), ALGORITHM=INSTANT;
      # All of the above should be allowed with any other ALGORITHM.
      # With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient
      # available algorithm will be used.
      

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              12 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.