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

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16099 Use alter algorithm=nocopy or algorithm=instant for non-InnoDB engine

          • Major - Major loss of function.
          • Closed
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16288 ALTER TABLE…ALGORITHM=DEFAULT does not override alter_algorithm

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14246 Skip the ha_innobase::prepare_inplace_alter_table() phase if no inplace_alter_table() phase is needed

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13301 Optimize DROP INDEX, ADD INDEX into RENAME INDEX

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14246 Skip the ha_innobase::prepare_inplace_alter_table() phase if no inplace_alter_table() phase is needed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15719 ALTER TABLE…ALGORITHM=INPLACE is unnecessarily refused due to innodb_force_recovery

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

          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-18570 ALGORITHM=INSTANT or NOCOPY fails to fail on MyISAM tables

          • Major - Major loss of function.
          • Confirmed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-18845 Introduce alter_lock to allow refusing non-online ALTER TABLE

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20419 ADD COLUMN with DROP COLUMN wrongly inherits attributes

          • Major - Major loss of function.
          • Confirmed

          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

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

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16830 ALTER TABLE DROP FOREIGN KEY - unexpected end of stream error

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-20590 Introduce a file format constraint to ALTER TABLE

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21855 Document difference between DEFAULT and 'DEFAULT' when setting alter_algorithm or system_versioning_asof

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27940 Instant alter drop index not supported in 10.4.24

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33655 Remove alter_algorithm

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

          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.