W3cubDocs

/MariaDB

InnoDB Online DDL Operations with the NOCOPY Alter Algorithm

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.

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 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

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/innodb-online-ddl-operations-with-algorithmnocopy/

InnoDB Online DDL Operations with the NOCOPY Alter Algorithm

Supported Operations by Inheritance

Column Operations

ALTER TABLE ... ADD COLUMN

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... MODIFY COLUMN

Reordering Columns

Changing the Data Type of a Column

Changing a Column to NULL

Changing a Column to NOT NULL

Adding a New ENUM Option

Adding a New SET Option

Removing System Versioning from a Column

ALTER TABLE ... ALTER COLUMN

Setting a Column's Default Value

Removing a Column's Default Value

ALTER TABLE ... CHANGE COLUMN

Index Operations

ALTER TABLE ... ADD PRIMARY KEY

ALTER TABLE ... DROP PRIMARY KEY

ALTER TABLE ... ADD INDEX and CREATE INDEX

Adding a Plain Index

Adding a Fulltext Index

Adding a Spatial Index

ALTER TABLE ... DROP INDEX and DROP INDEX

ALTER TABLE ... ADD FOREIGN KEY

ALTER TABLE ... DROP FOREIGN KEY

Table Operations

ALTER TABLE ... AUTO_INCREMENT=...

ALTER TABLE ... ROW_FORMAT=...

ALTER TABLE ... KEY_BLOCK_SIZE=...

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

ALTER TABLE ... DROP SYSTEM VERSIONING

ALTER TABLE ... DROP CONSTRAINT

ALTER TABLE ... FORCE

ALTER TABLE ... ENGINE=InnoDB

OPTIMIZE TABLE ...

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

Limitations

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

`ALTER TABLE ... ADD COLUMN`

`ALTER TABLE ... DROP COLUMN`

`ALTER TABLE ... MODIFY COLUMN`

Adding a New `ENUM` Option

Adding a New `SET` Option

`ALTER TABLE ... ALTER COLUMN`

`ALTER TABLE ... CHANGE COLUMN`

`ALTER TABLE ... ADD PRIMARY KEY`

`ALTER TABLE ... DROP PRIMARY KEY`

`ALTER TABLE ... ADD INDEX` and `CREATE INDEX`

`ALTER TABLE ... DROP INDEX` and `DROP INDEX`

`ALTER TABLE ... ADD FOREIGN KEY`

`ALTER TABLE ... DROP FOREIGN KEY`

`ALTER TABLE ... AUTO_INCREMENT=...`

`ALTER TABLE ... ROW_FORMAT=...`

`ALTER TABLE ... KEY_BLOCK_SIZE=...`

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

`ALTER TABLE ... DROP SYSTEM VERSIONING`

`ALTER TABLE ... DROP CONSTRAINT`

`ALTER TABLE ... FORCE`

`ALTER TABLE ... ENGINE=InnoDB`

`OPTIMIZE TABLE ...`

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