InnoDB Online DDL Operations with ALGORITHM=INSTANT
Contents
- 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 ... [ ADD | DROP ] SYSTEM VERSIONING
- ALTER TABLE ... DROP CONSTRAINT
- ALTER TABLE ... FORCE
- ALTER TABLE ... ENGINE=InnoDB
- OPTIMIZE TABLE ...
- ALTER TABLE ... RENAME TO and RENAME TABLE ...
Column Operations
ALTER TABLE ... ADD COLUMN
In MariaDB 10.3.2 and later, InnoDB supports adding columns to a table with ALGORITHM
set to INSTANT
if the new column is the last column in the table. See MDEV-11369 for more information. If the table has a hidden FTS_DOC_ID
column is present, then this is not supported.
In MariaDB 10.4 and later, InnoDB supports adding columns to a table with ALGORITHM
set to INSTANT
, regardless of where in the column list the new column is added.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ADD COLUMN c varchar(50); Query OK, 0 rows affected (0.004 sec)
And this succeeds in MariaDB 10.4 and later:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ADD COLUMN c varchar(50) AFTER a; Query OK, 0 rows affected (0.004 sec)
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 INSTANT
. See MDEV-15562 for more information.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab DROP COLUMN c; Query OK, 0 rows affected (0.004 sec)
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 INSTANT
. See MDEV-15562 for more information.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c varchar(50) AFTER a; Query OK, 0 rows affected (0.004 sec)
Changing the Data Type of a Column
InnoDB does not support modifying a column's data type with ALGORITHM
set to INSTANT
in most cases. There are a couple exceptions:
- In MariaDB 10.4.3 and later, InnoDB supports increasing the length of
CHAR
orVARCHAR
columns withALGORITHM
set toINSTANT
. See MDEV-15563 for more information.
- In MariaDB 10.4.3 and later, InnoDB supports converting a column from
VARCHAR
toCHAR
, or fromVARBINARY
toBINARY
, or from one integer type to another integer type withALGORITHM
set toINSTANT
, as long as the size is greater than or equal to the original size. See MDEV-15563 for more information.
For example, this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c int; ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
But this succeeds in MariaDB 10.4.3 and later:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c varchar(100); Query OK, 0 rows affected (0.004 sec)
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 INSTANT
if the ROW_FORMAT
table option is set to REDUNDANT
. See MDEV-15563 for more information.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) NOT NULL ) ROW_FORMAT=REDUNDANT; SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c varchar(50) NULL; Query OK, 0 rows affected (0.004 sec)
Changing a Column to NOT NULL
InnoDB does not support modifying a column to not allow NULL
values with ALGORITHM
set to INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ) ROW_FORMAT=REDUNDANT; SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c varchar(50) NOT NULL; ERROR 1845 (0A000): ALGORITHM=INSTANT 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 INSTANT
. In order to add a new ENUM
option with ALGORITHM
set to INSTANT
, the following requirements must be met:
- It must be added to the end of the list.
- The storage requirements must not change.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c ENUM('red', 'green') ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'green', 'blue'); Query OK, 0 rows affected (0.002 sec)
But this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c ENUM('red', 'green') ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'blue', 'green'); ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
Adding a New SET
Option
InnoDB supports adding a new SET
option to a column with ALGORITHM
set to INSTANT
. In order to add a new SET
option with ALGORITHM
set to INSTANT
, the following requirements must be met:
- It must be added to the end of the list.
- The storage requirements must not change.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c SET('red', 'green') ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c SET('red', 'green', 'blue'); Query OK, 0 rows affected (0.002 sec)
But this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c SET('red', 'green') ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab MODIFY COLUMN c SET('red', 'blue', 'green'); ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ALTER COLUMN c SET DEFAULT 'No value explicitly provided.'; Query OK, 0 rows affected (0.003 sec)
Removing a Column's Default Value
InnoDB supports removing a column's DEFAULT
value with ALGORITHM
set to INSTANT
.
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) DEFAULT 'No value explicitly provided.' ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ALTER COLUMN c DROP DEFAULT; Query OK, 0 rows affected (0.002 sec)
ALTER TABLE ... CHANGE COLUMN
InnoDB supports renaming a column with ALGORITHM
set to INSTANT
, unless the column's data type or attributes changed in addition to the name.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab CHANGE COLUMN c str varchar(50); Query OK, 0 rows affected (0.004 sec)
But this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab CHANGE COLUMN c num int; ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY
This applies to ALTER TABLE ... CHANGE COLUMN
for InnoDB tables.
Generated 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.
Index Operations
ALTER TABLE ... ADD PRIMARY KEY
InnoDB does not support adding a primary key to a table with ALGORITHM
set to INSTANT
.
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='INSTANT'; ALTER TABLE tab ADD PRIMARY KEY (a); ERROR 1845 (0A000): ALGORITHM=INSTANT 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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab DROP PRIMARY KEY; ERROR 1846 (0A000): ALGORITHM=INSTANT 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
InnoDB does not support adding an index to a table with ALGORITHM
set to INSTANT
.
For example, this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ADD INDEX b_index (b); ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY
And this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; CREATE INDEX b_index ON tab (b); ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY
This applies to ALTER TABLE ... ADD INDEX
and CREATE INDEX
for InnoDB tables.
ALTER TABLE ... DROP INDEX
and DROP INDEX
InnoDB supports dropping indexes from a table with ALGORITHM
set to INSTANT
.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50), INDEX b_index (b) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab DROP INDEX b_index; Query OK, 0 rows affected (0.008 sec)
And this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50), INDEX b_index (b) ); SET SESSION alter_algorithm='INSTANT'; DROP INDEX b_index ON tab; Query OK, 0 rows affected (0.007 sec)
This applies to ALTER TABLE ... DROP INDEX
and DROP INDEX
for InnoDB tables.
ALTER TABLE ... ADD FOREIGN KEY
InnoDB does not support adding foreign key constraints to a table with ALGORITHM
set to INSTANT
.
For example:
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='INSTANT'; ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a); ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY
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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab2 ( a int PRIMARY KEY, b varchar(50) ); CREATE OR REPLACE TABLE tab1 ( a int PRIMARY KEY, b varchar(50), c varchar(50), d int, FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab1 DROP FOREIGN KEY tab2_fk; Query OK, 0 rows affected (0.004 sec)
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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab AUTO_INCREMENT=100; Query OK, 0 rows affected (0.002 sec)
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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ) ROW_FORMAT=DYNAMIC; SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ROW_FORMAT=COMPRESSED; ERROR 1846 (0A000): ALGORITHM=INSTANT 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 INSTANT
.
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='INSTANT'; ALTER TABLE tab KEY_BLOCK_SIZE=2; ERROR 1846 (0A000): ALGORITHM=INSTANT 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 INSTANT
. InnoDB does not support changing a table's PAGE_COMPRESSED
value from 1
to 0
with ALGORITHM
set to INSTANT
.
In these versions, InnoDB also supports changing a table's PAGE_COMPRESSION_LEVEL
value with ALGORITHM
set to INSTANT
.
See MDEV-16328 for more information.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab PAGE_COMPRESSED=1; Query OK, 0 rows affected (0.004 sec)
And this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ) PAGE_COMPRESSED=1 PAGE_COMPRESSION_LEVEL=5; SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab PAGE_COMPRESSION_LEVEL=4; Query OK, 0 rows affected (0.004 sec)
But this fails:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ) PAGE_COMPRESSED=1; SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab PAGE_COMPRESSED=0; ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACE
This applies to PAGE_COMPRESSED=...
and PAGE_COMPRESSION_LEVEL=...
for InnoDB tables.
ALTER TABLE ... [ ADD | DROP ] SYSTEM VERSIONING
- Altering the
WITH SYSTEM VERSIONING
table option for a table. Added in MariaDB 10.3.8. See MDEV-16330 for more information.
ALTER TABLE ... DROP CONSTRAINT
- Dropping a
CHECK
constraint. See MDEV-16331 for more information.
ALTER TABLE ... FORCE
InnoDB does not support forcing a table rebuild with ALGORITHM
set to INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab FORCE; ERROR 1845 (0A000): ALGORITHM=INSTANT 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 INSTANT
.
For example:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab ENGINE=InnoDB; ERROR 1845 (0A000): ALGORITHM=INSTANT 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 INSTANT
.
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='INSTANT'; 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=INSTANT 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 INSTANT
.
For example, this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; ALTER TABLE tab RENAME TO old_tab; Query OK, 0 rows affected (0.008 sec)
And this succeeds:
CREATE OR REPLACE TABLE tab ( a int PRIMARY KEY, b varchar(50), c varchar(50) ); SET SESSION alter_algorithm='INSTANT'; RENAME TABLE tab TO old_tab; Query OK, 0 rows affected (0.008 sec)
This applies to ALTER TABLE ... RENAME TO
and RENAME TABLE
for InnoDB tables.