InnoDB Online DDL Operations with ALGORITHM=INPLACE

Column Operations

ALTER TABLE ... ADD COLUMN

InnoDB supports adding columns to a table with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

With the exception of adding an auto-increment column, concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD COLUMN c varchar(50);
Query OK, 0 rows affected (0.006 sec)

ALTER TABLE ... DROP COLUMN

InnoDB supports dropping columns from a table with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP COLUMN c;
Query OK, 0 rows affected (0.021 sec)

ALTER TABLE ... MODIFY COLUMN

Reordering Columns

InnoDB supports reordering columns within a table with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) AFTER a;
Query OK, 0 rows affected (0.022 sec)

Changing the Data Type of a Column

InnoDB does not support modifying a column's data type with ALGORITHM=INPLACE in most cases. If you try to do so, then you will see an error. For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c int;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

In MariaDB 10.2.2 and later, the only exception is that increasing the size of a VARCHAR column, which is performed as an instantaneous metadata update. For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(100);
Query OK, 0 rows affected (0.005 sec)

Changing a Column to NULL

InnoDB supports modifying a column to allow NULL values with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) NOT NULL
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) NULL;
Query OK, 0 rows affected (0.021 sec)

Changing a Column to NOT NULL

InnoDB supports modifying a column to not allow NULL values with ALGORITHM=INPLACE. It is required for strict mode to be enabled in SQL_MODE. The operation will fail if the column contains any NULL values. Changes that would interfere with referential integrity are also not permitted.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c varchar(50) NOT NULL;
Query OK, 0 rows affected (0.021 sec)

Adding a New ENUM Option

InnoDB supports adding a new ENUM option to a column with ALGORITHM=INPLACE. In order to add a new ENUM option with ALGORITHM=INPLACE, the following requirements must be met:

• It must be added to the end of the list.
• The storage requirements must not change.

This operation only changes the table's metadata, so the table does not have to be rebuilt..

Concurrent DML is permitted.

For example, this succeeds:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c ENUM('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'green', 'blue');
Query OK, 0 rows affected (0.004 sec)

But this fails:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c ENUM('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c ENUM('red', 'blue', 'green');
ERROR 1846 (0A000): ALGORITHM=INPLACE 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=INPLACE. In order to add a new SET option with ALGORITHM=INPLACE, the following requirements must be met:

• It must be added to the end of the list.
• The storage requirements must not change.

This operation only changes the table's metadata, so the table does not have to be rebuilt..

Concurrent DML is permitted.

For example, this succeeds:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c SET('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c SET('red', 'green', 'blue');
Query OK, 0 rows affected (0.004 sec)

But this fails:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c SET('red', 'green')
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c SET('red', 'blue', 'green');
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

ALTER TABLE ... ALTER COLUMN

Setting a Column's Default Value

InnoDB supports modifying a column's default value with ALGORITHM=INPLACE.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ALTER COLUMN c SET DEFAULT 'No value explicitly provided.';
Query OK, 0 rows affected (0.005 sec)

Removing a Column's Default Value

InnoDB supports removing a column's default value with ALGORITHM=INPLACE.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.

For example:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50) DEFAULT 'No value explicitly provided.'
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ALTER COLUMN c DROP DEFAULT;
Query OK, 0 rows affected (0.005 sec)

ALTER TABLE ... CHANGE COLUMN

InnoDB supports renaming a column with ALGORITHM=INPLACE, unless the column's data type or attributes changed in addition to the name.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

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='INPLACE';
ALTER TABLE tab CHANGE COLUMN c str varchar(50);
Query OK, 0 rows affected (0.006 sec)

But this fails:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab CHANGE COLUMN c num int;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY

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 supports adding a primary key to a table with ALGORITHM=INPLACE. If the new primary key column has to be modified to NOT NULL, then it is required for strict mode to be enabled in SQL_MODE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

For example, this succeeds:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
Query OK, 0 rows affected (0.021 sec)

But this fails:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

INSERT INTO tab VALUES (NULL, NULL, NULL);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1265 (01000): Data truncated for column 'a' at row 1

And this fails:

CREATE OR REPLACE TABLE tab (
   a int,
   b varchar(50),
   c varchar(50)
);

INSERT INTO tab VALUES (1, NULL, NULL);
INSERT INTO tab VALUES (1, NULL, NULL);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

ALTER TABLE ... DROP PRIMARY KEY

InnoDB does not support dropping a primary key with ALGORITHM=INPLACE in most cases. If you try to do so, then you will see an error. Only ALGORITHM=COPY is supported. Concurrent DML is *not* permitted.

However, there is an exception. If you are dropping a primary key, and adding a new one at the same time, then that operation can be performed with ALGORITHM=INPLACE. Concurrent DML is permitted.

For example, this fails:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

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

But this succeeds:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP PRIMARY KEY, ADD PRIMARY KEY (b);
Query OK, 0 rows affected (0.020 sec)

ALTER TABLE ... ADD INDEX and CREATE INDEX

InnoDB supports adding an index to a table with ALGORITHM=INPLACE in most cases. The table is not rebuilt. However, there is an exception. 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. From that point forward, adding additional FULLTEXT indexes to the same table will not require the table to be rebuilt.

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='INPLACE';
ALTER TABLE tab ADD INDEX b_index (b);
Query OK, 0 rows affected (0.010 sec)

And this succeeds:

CREATE OR REPLACE TABLE tab (
   a int PRIMARY KEY,
   b varchar(50),
   c varchar(50)
);

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

And this succeeds, but requires the table to be rebuilt:

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.055 sec)

ALTER TABLE ... DROP INDEX and DROP INDEX

InnoDB supports dropping indexes from a table with ALGORITHM=INPLACE.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.

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='INPLACE';
ALTER TABLE tab DROP INDEX b_index;

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='INPLACE';
DROP INDEX b_index ON tab;

ALTER TABLE ... ADD FOREIGN KEY

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

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.

ALTER TABLE ... DROP FOREIGN KEY

InnoDB supports dropping foreign key constraints from a table with ALGORITHM=INPLACE.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.

Table Operations

ALTER TABLE ... AUTO_INCREMENT=...

InnoDB supports changing a table's AUTO_INCREMENT value with ALGORITHM=INPLACE. This operation should finish instantly. The table is not rebuilt.

ALTER TABLE ... ROW_FORMAT=...

InnoDB supports changing a table's row format with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

ALTER TABLE ... KEY_BLOCK_SIZE=...

InnoDB supports changing a table's KEY_BLOCK_SIZE with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

ALTER TABLE ... FORCE

InnoDB supports forcing a table rebuild with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

ALTER TABLE ... ENGINE=InnoDB

InnoDB supports forcing a table rebuild with ALGORITHM=INPLACE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

Concurrent DML is permitted.

OPTIMIZE TABLE ...

InnoDB supports optimizing a table with ALGORITHM=INPLACE.

If the innodb_defragment system variable is set to OFF and the innodb_optimize_fulltext_only system variable is set to OFF, then OPTIMIZE TABLE will be equivalent to ALTER TABLE … FORCE.

The table is rebuilt, which means that all of the data is reorganized substantially, and the indexes are rebuilt. As a result, the operation is quite expensive.

If either of the previously mentioned system variables is set to ON, then OPTIMIZE TABLE will optimize some data without rebuilding the table. However, the file size will not be reduced.

Concurrent DML is permitted.

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

InnoDB supports renaming a table with ALGORITHM=INPLACE.

This operation only changes the table's metadata, so the table does not have to be rebuilt.

Concurrent DML is permitted.