InnoDB Online DDL Operations with the NOCOPY Alter Algorithm

Understand the NOCOPY algorithm, which avoids rebuilding the clustered index for certain operations like adding secondary indexes, significantly reducing I/O.

Supported Operations by Inheritance

When the ALGORITHM clause is set to NOCOPY, the supported operations are a superset of the operations that are supported when the ALGORITHM clause is set to INSTANT.

Therefore, when the ALGORITHM clause is set to NOCOPY, some operations are supported by inheritance. See the following additional pages for more information about these supported operations:

• InnoDB Online DDL Operations with ALGORITHM=INSTANT

Column Operations

ALTER TABLE ... ADD COLUMN

In MariaDB 10.3.2 and later, InnoDB supports adding columns to a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... ADD COLUMN for more information.

This applies to ALTER TABLE ... ADD COLUMN for InnoDB tables.

ALTER TABLE ... DROP COLUMN

In MariaDB 10.4 and later, InnoDB supports dropping columns from a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP COLUMN for more information.

This applies to ALTER TABLE ... DROP COLUMN for InnoDB tables.

ALTER TABLE ... MODIFY COLUMN

This applies to ALTER TABLE ... MODIFY COLUMN for InnoDB tables.

Reordering Columns

In MariaDB 10.4 and later, InnoDB supports reordering columns within a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Reordering Columns for more information.

Changing the Data Type of a Column

InnoDB does not support modifying a column's data type with ALGORITHM set to NOCOPY in most cases. There are a few exceptions in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Changing the Data Type of a Column for more information.

Changing a Column to NULL

In MariaDB 10.4.3 and later, InnoDB supports modifying a column to allow NULL values with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Changing a Column to NULL for more information.

Changing a Column to NOT NULL

InnoDB does not support modifying a column to not allow NULL values with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=REDUNDANT;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(50) NOT NULL;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

Adding a New ENUM Option

InnoDB supports adding a new ENUM option to a column with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Adding a New ENUM Option for more information.

Adding a New SET Option

InnoDB supports adding a new SET option to a column with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Adding a New SET Option for more information.

Removing System Versioning from a Column

In MariaDB 10.3.8 and later, InnoDB supports removing system versioning from a column with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Removing System Versioning from a Column for more information.

ALTER TABLE ... ALTER COLUMN

This applies to ALTER TABLE ... ALTER COLUMN for InnoDB tables.

Setting a Column's Default Value

InnoDB supports modifying a column's DEFAULT value with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Setting a Column's Default Value for more information.

Removing a Column's Default Value

InnoDB supports removing a column's DEFAULT value with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Removing a Column's Default Value for more information.

ALTER TABLE ... CHANGE COLUMN

InnoDB supports renaming a column with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... CHANGE COLUMN for more information.

This applies to ALTER TABLE ... CHANGE COLUMN for InnoDB tables.

Index Operations

ALTER TABLE ... ADD PRIMARY KEY

InnoDB does not support adding a primary key to a table with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

This applies to ALTER TABLE ... ADD PRIMARY KEY for InnoDB tables.

ALTER TABLE ... DROP PRIMARY KEY

InnoDB does not support dropping a primary key with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP PRIMARY KEY;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY

This applies to ALTER TABLE ... DROP PRIMARY KEY for InnoDB tables.

ALTER TABLE ... ADD INDEX and CREATE INDEX

This applies to ALTER TABLE ... ADD INDEX and CREATE INDEX for InnoDB tables.

Adding a Plain Index

InnoDB supports adding a plain index to a table with ALGORITHM set to NOCOPY.

This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to NONE. When this strategy is used, all concurrent DML is permitted.

For example, this succeeds:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD INDEX b_index (b);
Query OK, 0 rows affected (0.009 sec)

And this succeeds:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
CREATE INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.009 sec)

Adding a Fulltext Index

InnoDB supports adding a FULLTEXT index to a table with ALGORITHM set to NOCOPY.

However, there are some limitations, such as:

• Adding a FULLTEXT index to a table that does not have a user-defined FTS_DOC_ID column will require the table to be rebuilt once. When the table is rebuilt, the system adds a hidden FTS_DOC_ID column. This initial operation will have to be performed with ALGORITHM set to INPLACE.From that point forward, adding additional FULLTEXT indexes to the same table will not require the table to be rebuilt, and ALGORITHM can be set to NOCOPY.

• Only one FULLTEXT index may be added at a time when ALGORITHM is set to NOCOPY.

This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to SHARED. When this strategy is used, read-only concurrent DML is permitted.

For example, this succeeds, but the first operation requires the table to be rebuilt ALGORITHM set to INPLACE, so that the hidden FTS_DOC_ID column can be added:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.043 sec)

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.017 sec)

And this succeeds in the same way as above:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
CREATE FULLTEXT INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.048 sec)

SET SESSION alter_algorithm='NOCOPY';
CREATE FULLTEXT INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.016 sec)

But this second command fails, because only one FULLTEXT index can be added at a time:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d VARCHAR(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.041 sec)

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c), ADD FULLTEXT INDEX d_index (d);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY

Adding a Spatial Index

InnoDB supports adding a SPATIAL index to a table with ALGORITHM set to NOCOPY.

This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to SHARED. When this strategy is used, read-only concurrent DML is permitted.

For example, this succeeds:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
Query OK, 0 rows affected (0.005 sec)

And this succeeds in the same way as above:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c GEOMETRY NOT NULL
);

SET SESSION alter_algorithm='NOCOPY';
CREATE SPATIAL INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.005 sec)

ALTER TABLE ... DROP INDEX and DROP INDEX

InnoDB supports dropping indexes from a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP INDEX and DROP INDEX for more information.

This applies to ALTER TABLE ... DROP INDEX and DROP INDEX for InnoDB tables.

ALTER TABLE ... ADD FOREIGN KEY

InnoDB does supports adding foreign key constraints to a table with ALGORITHM set to NOCOPY. In order to add a new foreign key constraint to a table with ALGORITHM set to NOCOPY, the foreign_key_checks system variable needs to be set to OFF. If it is set to ON, then ALGORITHM=COPY is required.

This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to NONE. When this strategy is used, all concurrent DML is permitted.

For example, this fails:

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY

But this succeeds:

CREATE OR REPLACE TABLE tab1 (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50),
   d INT
);

CREATE OR REPLACE TABLE tab2 (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SET SESSION foreign_key_checks=OFF;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
Query OK, 0 rows affected (0.011 sec)

This applies to ALTER TABLE ... ADD FOREIGN KEY for InnoDB tables.

ALTER TABLE ... DROP FOREIGN KEY

InnoDB supports dropping foreign key constraints from a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP FOREIGN KEY for more information.

This applies to ALTER TABLE ... DROP FOREIGN KEY for InnoDB tables.

Table Operations

ALTER TABLE ... AUTO_INCREMENT=...

InnoDB supports changing a table's AUTO_INCREMENT value with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... AUTO_INCREMENT=... for more information.

This applies to ALTER TABLE ... AUTO_INCREMENT=... for InnoDB tables.

ALTER TABLE ... ROW_FORMAT=...

InnoDB does not support changing a table's row format with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=DYNAMIC;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE

This applies to ALTER TABLE ... ROW_FORMAT=... for InnoDB tables.

ALTER TABLE ... KEY_BLOCK_SIZE=...

InnoDB does not support changing a table's KEY_BLOCK_SIZE with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) ROW_FORMAT=COMPRESSED
  KEY_BLOCK_SIZE=4;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab KEY_BLOCK_SIZE=2;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE

This applies to KEY_BLOCK_SIZE=... for InnoDB tables.

ALTER TABLE ... PAGE_COMPRESSED=1 and ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...

In MariaDB 10.3.10 and later, InnoDB supports setting a table's PAGE_COMPRESSED value to 1 with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

InnoDB does not support changing a table's PAGE_COMPRESSED value from 1 to 0 with ALGORITHM set to NOCOPY.

In these versions, InnoDB also supports changing a table's PAGE_COMPRESSION_LEVEL value with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause is set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... PAGE_COMPRESSED=1 and ALTER TABLE ... PAGE_COMPRESSION_LEVEL=... for more information.

This applies to ALTER TABLE ... PAGE_COMPRESSED=... and ALTER TABLE ... PAGE_COMPRESSION_LEVEL=... for InnoDB tables.

ALTER TABLE ... DROP SYSTEM VERSIONING

InnoDB does not support dropping system versioning from a table with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
) WITH SYSTEM VERSIONING;

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP SYSTEM VERSIONING;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

This applies to ALTER TABLE ... DROP SYSTEM VERSIONING for InnoDB tables.

ALTER TABLE ... DROP CONSTRAINT

In MariaDB 10.3.6 and later, InnoDB supports dropping a CHECK constraint from a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... DROP CONSTRAINT for more information.

This applies to ALTER TABLE ... DROP CONSTRAINT for InnoDB tables.

ALTER TABLE ... FORCE

InnoDB does not support forcing a table rebuild with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab FORCE;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

This applies to ALTER TABLE ... FORCE for InnoDB tables.

ALTER TABLE ... ENGINE=InnoDB

InnoDB does not support forcing a table rebuild with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ENGINE=InnoDB;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

This applies to ALTER TABLE ... ENGINE=InnoDB for InnoDB tables.

OPTIMIZE TABLE ...

InnoDB does not support optimizing a table with ALGORITHM set to NOCOPY.

For example:

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50),
   c VARCHAR(50)
);

SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_defragment             | OFF   |
| innodb_optimize_fulltext_only | OFF   |
+-------------------------------+-------+
2 rows in set (0.001 sec)

SET SESSION alter_algorithm='NOCOPY';
OPTIMIZE TABLE tab;
+---------+----------+----------+-----------------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                                    |
+---------+----------+----------+-----------------------------------------------------------------------------+
| db1.tab | optimize | note     | Table does not support optimize, doing recreate + analyze instead           |
| db1.tab | optimize | error    | ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE |
| db1.tab | optimize | status   | Operation failed                                                            |
+---------+----------+----------+-----------------------------------------------------------------------------+
3 rows in set, 1 warning (0.002 sec)

This applies to OPTIMIZE TABLE for InnoDB tables.

ALTER TABLE ... RENAME TO and RENAME TABLE ...

InnoDB supports renaming a table with ALGORITHM set to NOCOPY in the cases where the operation supports having the ALGORITHM clause set to INSTANT.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT: ALTER TABLE ... RENAME TO and RENAME TABLE ... for more information.

This applies to ALTER TABLE ... RENAME TO and RENAME TABLE for InnoDB tables.

Limitations

Limitations Related to Generated (Virtual and Persistent/Stored) Columns

Generated columns do not currently support online DDL for all of the same operations that are supported for "real" columns.

See Generated (Virtual and Persistent/Stored) Columns: Statement Support for more information on the limitations.

_{This page is licensed: CC BY-SA / Gnu FDL}