InnoDB Online DDL Operations with the NOCOPY Alter Algorithm
Contents
- Supported Operations by Inheritance
- Column Operations
- Index Operations
- 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
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:
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 hiddenFTS_DOC_ID
column. This initial operation will have to be performed with ALGORITHM set toINPLACE
.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 toNOCOPY
.
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 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.