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 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 or VARCHAR columns with ALGORITHM set to INSTANT. See MDEV-15563 for more information.

• In MariaDB 10.4.3 and later, InnoDB supports converting a column from VARCHAR to CHAR, or from VARBINARY to BINARY, or from one integer type to another integer type with ALGORITHM set to INSTANT, 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

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

Table Operations

ALTER TABLE ... AUTO_INCREMENT=...

ALTER TABLE ... ROW_FORMAT=...

ALTER TABLE ... KEY_BLOCK_SIZE=...

ALTER TABLE ... PAGE_COMPRESSED=...

• Altering the PAGE_COMPRESSED or PAGE_COMPRESSION_LEVEL table options. Added in MariaDB 10.3.10. See MDEV-16328 for more information.

ALTER TABLE ... [ ADD | DROP ] SYSTEM VERSIONING

ALTER TABLE ... DROP CONSTRAINT

• Dropping a CHECK constraint. See MDEV-16331 for more information.

ALTER TABLE ... FORCE

ALTER TABLE ... ENGINE=InnoDB

OPTIMIZE TABLE ...

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

• Altering the WITH SYSTEM VERSIONING table option for a table. Added in MariaDB 10.3.8. See MDEV-16330 for more information.