Perform online DDL operations with InnoDB in MariaDB Server. Learn how to alter tables without blocking read/write access, ensuring high availability for your applications.
InnoDB tables support online DDL, which permits concurrent DML and uses optimizations to avoid unnecessary table copying.
The ALTER TABLE statement supports two clauses that are used to implement online DDL:
ALGORITHM - This clause controls how the DDL operation is performed.
LOCK - This clause controls how much concurrency is allowed while the DDL operation is being performed.
InnoDB supports multiple algorithms for performing DDL operations. This offers a significant performance improvement over previous versions. The supported algorithms are:
DEFAULT - This implies the default behavior for the specific operation.
COPY
INPLACE
NOCOPY - This was added in MariaDB 10.3.7.
INSTANT - This was added in MariaDB 10.3.7.
The set of alter algorithms can be considered as a hierarchy. The hierarchy is ranked in the following order, with least efficient algorithm at the top, and most efficient algorithm at the bottom:
COPY
INPLACE
NOCOPY
INSTANT
When a user specifies an alter algorithm for a DDL operation, MariaDB does not necessarily use that specific algorithm for the operation. It interprets the choice in the following way:
If the user specifies COPY, then InnoDB uses the COPY algorithm.
If the user specifies any other algorithm, then InnoDB interprets that choice as the least efficient algorithm that the user is willing to accept. This means that if the user specifies INPLACE, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (INPLACE, NOCOPY, INSTANT). Likewise, if the user specifies NOCOPY, then InnoDB will use the most efficient algorithm supported by the specific operation from the set (NOCOPY, INSTANT).
There is also a special value that can be specified:
If the user specifies DEFAULT, then InnoDB uses its default choice for the operation. The default choice is to use the most efficient algorithm supported by the operation. The default choice will also be used if no algorithm is specified. Therefore, if you want InnoDB to use the most efficient algorithm supported by an operation, then you usually do not have to explicitly specify any algorithm at all.
InnoDB supports the ALGORITHM clause.
The ALGORITHM clause can be used to specify the least efficient algorithm that the user is willing to accept. It is supported by the ALTER TABLE and CREATE INDEX statements.
For example, if a user wanted to add a column to a table, but only if the operation used an algorithm that is at least as efficient as the INPLACE, then they could execute the following:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50)
);
ALTER TABLE tab ADD COLUMN c VARCHAR(50), ALGORITHM=INPLACE;The above operation should use the INSTANT algorithm, because the ADD COLUMN operation supports the INSTANT algorithm, and the INSTANT algorithm is more efficient than the INPLACE algorithm.
The alter_algorithm system variable can be used to pick the least efficient algorithm that the user is willing to accept.
For example, if a user wanted to add a column to a table, but only if the operation used an algorithm that is at least as efficient as the INPLACE, then they could execute the following:
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);The above operation would actually use the INSTANT algorithm, because the ADD COLUMN operation supports the INSTANT algorithm, and the INSTANT algorithm is more efficient than the INPLACE algorithm.
<>
The supported algorithms are described in more details below.
The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if ALGORITHM is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.
This means that, if an operation supports the INSTANT algorithm, then it will use that algorithm by default. If an operation does not support the INSTANT algorithm, but it does support the NOCOPY algorithm, then it will use that algorithm by default. If an operation does not support the NOCOPY algorithm, but it does support the INPLACE algorithm, then it will use that algorithm by default.
The COPY algorithm refers to the original ALTER TABLE algorithm.
When the COPY algorithm is used, MariaDB essentially does the following operations:
-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);
-- Copy the data from the original table
INSERT INTO tmp_tab
SELECT * FROM original_tab;
-- Drop the original table
DROP TABLE original_tab;
-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;This algorithm is very inefficient, but it is generic, so it works for all storage engines.
If the COPY algorithm is specified with the ALGORITHM clause or with the alter_algorithm system variable, then the COPY algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple ALTER TABLE operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single ALTER TABLE statement, so that the table is only rebuilt once.
If the COPY algorithm is used with an InnoDB table, then the following statements apply:
The table will be rebuilt using the current values of the innodb_file_per_table, innodb_file_format, and innodb_default_row_format system variables.
The operation will have to create a temporary table to perform the table copy. This temporary table will be in the same directory as the original table, and it's file name will be in the format #sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}, where ${PID} is the process ID of mysqld, ${THREAD_ID} is the connection ID, and ${TMP_TABLE_COUNT} is the number of temporary tables that the connection has open. Therefore, the datadir may contain files with file names like #sql1234_12_1.ibd.
The operation inserts one record at a time into each index, which is very inefficient.
InnoDB does not use a sort buffer.
The table copy operation creates a lot fewer InnoDB undo log writes. See MDEV-11415 for more information.
The table copy operation creates a lot of InnoDB redo log writes.
The COPY algorithm can be incredibly slow, because the whole table has to be copied and rebuilt. The INPLACE algorithm was introduced as a way to avoid this by performing operations in-place and avoiding the table copy and rebuild, when possible.
When the INPLACE algorithm is used, the underlying storage engine uses optimizations to perform the operation while avoiding the table copy and rebuild. However, INPLACE is a bit of a misnomer, since some operations may still require the table to be rebuilt for some storage engines. Regardless, several operations can be performed without a full copy of the table for some storage engines.
A more accurate name for the algorithm would have been the ENGINE algorithm, since the storage engine decides how to implement the algorithm.
If an ALTER TABLE operation supports the INPLACE algorithm, then it can be performed using optimizations by the underlying storage engine, but it may rebuilt.
If the INPLACE algorithm is specified with the ALGORITHM clause or with the alter_algorithm system variable and if the ALTER TABLE operation does not support the INPLACE algorithm, then an error will be raised:
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=COPYIn this case, raising an error is preferable, if the alternative is for the operation to make a copy of the table, and perform unexpectedly slowly.
If the INPLACE algorithm is used with an InnoDB table, then the following statements apply:
The operation might have to write sort files in the directory defined by the innodb_tmpdir system variable.
The operation might also have to write a temporary log file to track data changes by DML queries executed during the operation. The maximum size for this log file is configured by the innodb_online_alter_log_max_size system variable.
Some operations require the table to be rebuilt, even though the algorithm is inaccurately called "in-place". This includes operations such as adding or dropping columns, adding a primary key, changing a column to NULL, etc.
If the operation requires the table to be rebuilt, then the operation might have to create temporary tables.
It may have to create a temporary intermediate table for the actual table rebuild operation.
This temporary table will be in the same directory as the original table, and it's file name will be in the format #sql${PID}_${THREAD_ID}_${TMP_TABLE_COUNT}, where ${PID} is the process ID of mysqld, ${THREAD_ID} is the connection ID, and ${TMP_TABLE_COUNT} is the number of temporary tables that the connection has open. Therefore, the datadir may contain files with file names like #sql1234_12_1.ibd.
When it replaces the original table with the rebuilt table, it may also have to rename the original table using a temporary table name.
The innodb_safe_truncate system variable is set to OFF, then the format will actually be #sql-ib${TABLESPACE_ID}-${RAND}, where ${TABLESPACE_ID} is the table's tablespace ID within InnoDB and ${RAND} is a randomly initialized number. Therefore, the datadir may contain files with file names like #sql-ib230291-1363966925.ibd.
The storage needed for the above items can add up to the size of the original table, or more in some cases.
Some operations are instantaneous, if they only require the table's metadata to be changed. This includes operations such as renaming a column, changing a column's DEFAULT value, etc.
INPLACE AlgorithmWith respect to the allowed operations, the INPLACE algorithm supports a subset of the operations supported by the COPY algorithm, and it supports a superset of the operations supported by the NOCOPY algorithm.
See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more information.
The NOCOPY algorithm is supported. The INPLACE algorithm can sometimes be surprisingly slow in instances where it has to rebuild the clustered index, because when the clustered index has to be rebuilt, the whole table has to be rebuilt. The NOCOPY algorithm was introduced as a way to avoid this.
If an ALTER TABLE operation supports the NOCOPY algorithm, then it can be performed without rebuilding the clustered index.
If the NOCOPY algorithm is specified with the ALGORITHM clause or with the alter_algorithm system variable and if the ALTER TABLE operation does not support the NOCOPY algorithm, then an error will be raised:
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab MODIFY COLUMN c INT;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPYIn this case, raising an error is preferable, if the alternative is for the operation to rebuild the clustered index, and perform unexpectedly slowly.
NOCOPY AlgorithmWith respect to the allowed operations, the NOCOPY algorithm supports a subset of the operations supported by the INPLACE algorithm, and it supports a superset of the operations supported by the INSTANT algorithm.
See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more information.
The INSTANT algorithm is supported. The INPLACE algorithm can sometimes be surprisingly slow in instances where it has to modify data files. The INSTANT algorithm was introduced as a way to avoid this.
If an ALTER TABLE operation supports the INSTANT algorithm, then it can be performed without modifying any data files.
If the INSTANT algorithm is specified with the ALGORITHM clause or with the alter_algorithm system variable and if the ALTER TABLE operation does not support the INSTANT algorithm, then an error will be raised:
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=COPYIn this case, raising an error is preferable, if the alternative is for the operation to modify data files, and perform unexpectedly slowly.
With respect to the allowed operations, the INSTANT algorithm supports a subset of the operations supported by the NOCOPY algorithm.
See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more information.
InnoDB supports multiple locking strategies for performing DDL operations. This offers a significant performance improvement over previous versions. The supported locking strategies are:
DEFAULT - This implies the default behavior for the specific operation.
NONE
SHARED
EXCLUSIVE
Regardless of which locking strategy is used to perform a DDL operation, InnoDB will have to exclusively lock the table for a short time at the start and end of the operation's execution. This means that any active transactions that may have accessed the table must be committed or aborted for the operation to continue. This applies to most DDL statements, such as ALTER TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, RENAME TABLE, etc.
LOCK ClauseThe ALTER TABLE statement supports the LOCK clause.
The LOCK clause can be used to specify the locking strategy that the user is willing to accept. It is supported by the ALTER TABLE and CREATE INDEX statements.
For example, if a user wanted to add a column to a table, but only if the operation is non-locking, then they could execute the following:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50)
);
ALTER TABLE tab ADD COLUMN c VARCHAR(50), ALGORITHM=INPLACE, LOCK=NONE;If the LOCK clause is not explicitly set, then the operation uses LOCK=DEFAULT.
ALTER ONLINE TABLEALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore, the ALTER ONLINE TABLE statement can be used to ensure that your ALTER TABLE operation allows all concurrent DML.
The supported algorithms are described in more details below.
To see which locking strategies InnoDB supports for each operation, see the pages that describe which operations are supported for each algorithm:
The default behavior, which occurs if LOCK=DEFAULT is specified, or if LOCK is not specified at all, acquire the least restrictive lock on the table that is supported for the specific operation. This permits the maximum amount of concurrency that is supported for the specific operation.
The NONE locking strategy performs the operation without acquiring any lock on the table. This permits all concurrent DML.
If this locking strategy is not permitted for an operation, then an error is raised.
The SHARED locking strategy performs the operation after acquiring a read lock on the table. This permit read-only concurrent DML.
If this locking strategy is not permitted for an operation, then an error is raised.
The EXCLUSIVE locking strategy performs the operation after acquiring a write lock on the table. This does not permit concurrent DML.
This page is licensed: CC BY-SA / Gnu FDL
When the ALGORITHM clause is set to INPLACE, the supported operations are a superset of the operations that are supported when the ALGORITHM clause is set to NOCOPY. Similarly, 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 INPLACE, some operations are supported by inheritance. See the following additional pages for more information about these supported operations:
ALTER TABLE ... ADD COLUMNInnoDB supports adding columns to a table with ALGORITHM set to 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, 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:
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)This applies to ALTER TABLE ... ADD COLUMN for InnoDB tables.
ALTER TABLE ... DROP COLUMNInnoDB supports dropping columns from a table with ALGORITHM set to 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.
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:
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)This applies to ALTER TABLE ... DROP COLUMN for InnoDB tables.
ALTER TABLE ... MODIFY COLUMNThis applies to ALTER TABLE ... MODIFY COLUMN for InnoDB tables.
InnoDB supports reordering columns within a table with ALGORITHM set to 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.
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:
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)InnoDB does not support modifying a column's data type with ALGORITHM set to INPLACE in most cases. There are some exceptions:
In MariaDB 10.2.2 and later, InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INPLACE, unless it would require changing the number of bytes requires to represent the column's length. A VARCHAR column that is between 0 and 255 bytes in size requires 1 byte to represent its length, while a VARCHAR column that is 256 bytes or longer requires 2 bytes to represent its length. This means that the length of a column cannot be increased with ALGORITHM set to INPLACE if the original length was less than 256 bytes, and the new length is 256 bytes or more.
In MariaDB 10.4.3 and later, InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INPLACE 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.
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 MODIFY COLUMN c INT;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPYBut this succeeds in MariaDB 10.2.2 and later, because the original length of the column is less than 256 bytes, and the new length is still less than 256 bytes:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) CHARACTER SET=latin1;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(100);
Query OK, 0 rows affected (0.005 sec)But this fails in MariaDB 10.2.2 and later, because the original length of the column is less than 256 bytes, and the new length is greater than 256 bytes:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(255)
) CHARACTER SET=latin1;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(256);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPYInnoDB supports modifying a column to allow NULL values with ALGORITHM set to 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.
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:
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)InnoDB supports modifying a column to not allow NULL values with ALGORITHM set to 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.
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:
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)ENUM OptionInnoDB supports adding a new ENUM option to a column with ALGORITHM set to INPLACE. In order to add a new ENUM option with ALGORITHM set to 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..
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 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=COPYSET OptionInnoDB supports adding a new SET option to a column with ALGORITHM set to INPLACE. In order to add a new SET option with ALGORITHM set to 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..
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 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=COPYIn MariaDB 10.3.8 and later, InnoDB supports removing system versioning from a column with ALGORITHM set to INPLACE. In order for this to work, the system_versioning_alter_history system variable must be set to KEEP. See MDEV-16330 for more information.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50) WITH SYSTEM VERSIONING
);
SET SESSION system_versioning_alter_history='KEEP';
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(50) WITHOUT SYSTEM VERSIONING;
Query OK, 0 rows affected (0.005 sec)ALTER TABLE ... ALTER COLUMNThis applies to ALTER TABLE ... ALTER COLUMN for InnoDB tables.
InnoDB supports modifying a column's DEFAULT value with ALGORITHM set to INPLACE.
This operation only changes the table's metadata, so the table does not have to be rebuilt.
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:
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)InnoDB supports removing a column's DEFAULT value with ALGORITHM set to INPLACE.
This operation only changes the table's metadata, so the table does not have to be rebuilt.
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:
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 COLUMNInnoDB supports renaming a column with ALGORITHM set to 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.
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='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=COPYThis applies to ALTER TABLE ... CHANGE COLUMN for InnoDB tables.
ALTER TABLE ... ADD PRIMARY KEYInnoDB supports adding a primary key to a table with ALGORITHM set to INPLACE.
If the new primary key column is not defined as NOT NULL, then it is highly recommended for strict mode to be enabled in SQL_MODE. Otherwise, NULL values will be silently converted to the default value for the given data type, which is probably not the desired behavior in this scenario.
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.
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,
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 1And 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'This applies to ALTER TABLE ... ADD PRIMARY KEY for InnoDB tables.
ALTER TABLE ... DROP PRIMARY KEYInnoDB does not support dropping a primary key with ALGORITHM set to INPLACE in most cases.
If you try to do so, then you will see an error. InnoDB only supports this operation with ALGORITHM set to COPY. 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 set to INPLACE. 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 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=COPYBut 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)This applies to ALTER TABLE ... DROP PRIMARY KEY for InnoDB tables.
ALTER TABLE ... ADD INDEX and CREATE INDEXThis applies to ALTER TABLE ... ADD INDEX and CREATE INDEX for InnoDB tables.
InnoDB supports adding a plain index to a table with ALGORITHM set to INPLACE. The table is not rebuilt.
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='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)InnoDB supports adding a FULLTEXT index to a table with ALGORITHM set to INPLACE. The table is not rebuilt in some cases.
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. From that point forward, adding additional FULLTEXT indexes to the same table will not require the table to be rebuilt when ALGORITHM is set to INPLACE.
If a table has more than one FULLTEXT index, then it cannot be rebuilt by any ALTER TABLE operations when ALGORITHM is set to INPLACE.
If a table has a FULLTEXT index, then it cannot be rebuilt by any ALTER TABLE operations when the LOCK clause is set to NONE.
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 requires the table to be rebuilt, 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.055 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.041 sec)And this succeeds, and the second command does not require 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.043 sec)
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.017 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)
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c), ADD FULLTEXT INDEX d_index (d);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPYAnd this third command fails, because a table cannot be rebuilt when it has more than one FULLTEXT index:
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.040 sec)
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.015 sec)
ALTER TABLE tab FORCE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPYInnoDB supports adding a SPATIAL index to a table with ALGORITHM set to INPLACE.
However, there are some limitations, such as:
If a table has a SPATIAL index, then it cannot be rebuilt by any ALTER TABLE operations when the LOCK clause is set to NONE.
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='INPLACE';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
Query OK, 0 rows affected (0.006 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='INPLACE';
CREATE SPATIAL INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.006 sec)ALTER TABLE ... DROP INDEX and DROP INDEXInnoDB supports dropping indexes from a table with ALGORITHM set to INPLACE.
This operation only changes the table's metadata, so the table does not have to be rebuilt.
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),
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;This applies to ALTER TABLE ... DROP INDEX and DROP INDEX for InnoDB tables.
ALTER TABLE ... ADD FOREIGN KEYInnoDB supports adding foreign key constraints to a table with ALGORITHM set to INPLACE. In order to add a new foreign key constraint to a table with ALGORITHM set to INPLACE, 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 only changes the table's metadata, so the table does not have to be rebuilt.
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='INPLACE';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPYBut 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='INPLACE';
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 KEYInnoDB supports dropping foreign key constraints from a table with ALGORITHM set to INPLACE.
This operation only changes the table's metadata, so the table does not have to be rebuilt.
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:
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='INPLACE';
ALTER TABLE tab1 DROP FOREIGN KEY tab2_fk;
Query OK, 0 rows affected (0.005 sec)This applies to ALTER TABLE ... DROP FOREIGN KEY for InnoDB tables.
ALTER TABLE ... AUTO_INCREMENT=...InnoDB supports changing a table's AUTO_INCREMENT value with ALGORITHM set to INPLACE. This operation should finish instantly. The table is not rebuilt.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.004 sec)This applies to ALTER TABLE ... AUTO_INCREMENT=... for InnoDB tables.
ALTER TABLE ... ROW_FORMAT=...InnoDB supports changing a table's row format with ALGORITHM set to 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.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) ROW_FORMAT=DYNAMIC;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected (0.025 sec)This applies to ALTER TABLE ... ROW_FORMAT=... for InnoDB tables.
ALTER TABLE ... KEY_BLOCK_SIZE=...InnoDB supports changing a table's KEY_BLOCK_SIZE with ALGORITHM set to 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.
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:
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='INPLACE';
ALTER TABLE tab KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (0.021 sec)This applies to KEY_BLOCK_SIZE=... for InnoDB tables.
ALTER TABLE ... PAGE_COMPRESSED=... 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 INPLACE. InnoDB also supports changing a table's PAGE_COMPRESSED value from 1 to 0 with ALGORITHM set to INPLACE.
In these versions, InnoDB also supports changing a table's PAGE_COMPRESSION_LEVEL value with ALGORITHM set to INPLACE.
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.
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='INPLACE';
ALTER TABLE tab PAGE_COMPRESSED=1;
Query OK, 0 rows affected (0.006 sec)And this succeeds:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) PAGE_COMPRESSED=1;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab PAGE_COMPRESSED=0;
Query OK, 0 rows affected (0.020 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='INPLACE';
ALTER TABLE tab PAGE_COMPRESSION_LEVEL=4;
Query OK, 0 rows affected (0.006 sec)This applies to PAGE_COMPRESSED=... and PAGE_COMPRESSION_LEVEL=... for InnoDB tables.
ALTER TABLE ... DROP SYSTEM VERSIONINGInnoDB supports dropping system versioning from a table with ALGORITHM set to INPLACE.
This operation supports the 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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) WITH SYSTEM VERSIONING;
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP SYSTEM VERSIONING;This applies to ALTER TABLE ... DROP SYSTEM VERSIONING for InnoDB tables.
ALTER TABLE ... DROP CONSTRAINTIn MariaDB 10.3.6 and later, InnoDB supports dropping a CHECK constraint from a table with ALGORITHM set to INPLACE. See MDEV-16331 for more information.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50),
CONSTRAINT b_not_empty CHECK (b != '')
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab DROP CONSTRAINT b_not_empty;
Query OK, 0 rows affected (0.004 sec)This applies to ALTER TABLE ... DROP CONSTRAINT for InnoDB tables.
ALTER TABLE ... FORCEInnoDB supports forcing a table rebuild with ALGORITHM set to 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.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab FORCE;
Query OK, 0 rows affected (0.022 sec)This applies to ALTER TABLE ... FORCE for InnoDB tables.
ALTER TABLE ... ENGINE=InnoDBInnoDB supports forcing a table rebuild with ALGORITHM set to 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.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ENGINE=InnoDB;
Query OK, 0 rows affected (0.022 sec)This applies to ALTER TABLE ... ENGINE=InnoDB for InnoDB tables.
OPTIMIZE TABLE ...InnoDB supports optimizing a table with ALGORITHM set to INPLACE.
If the innodb_defragment system variable is set to OFF, and if the innodb_optimize_fulltext_only system variable is also 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.
For example, this succeeds:
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 |
+-------------------------------+-------+
SET SESSION alter_algorithm='INPLACE';
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 | status | OK |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.026 sec)And this succeeds, but the table is not rebuilt:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET GLOBAL innodb_defragment=ON;
SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_defragment | ON |
| innodb_optimize_fulltext_only | OFF |
+-------------------------------+-------+
SET SESSION alter_algorithm='INPLACE';
OPTIMIZE TABLE tab;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| db1.tab | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (0.004 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 INPLACE.
This operation only changes the table's metadata, so the table does not have to be rebuilt.
This operation supports the exclusive locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to EXCLUSIVE. When this strategy is used, concurrent DML is not 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 RENAME TO old_tab;
Query OK, 0 rows affected (0.011 sec)And this succeeds:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
RENAME TABLE tab TO old_tab;This applies to ALTER TABLE ... RENAME TO and RENAME TABLE for InnoDB tables.
If a table has more than one FULLTEXT index, then it cannot be rebuilt by any ALTER TABLE operations when ALGORITHM is set to INPLACE.
If a table has a FULLTEXT index, then it cannot be rebuilt by any ALTER TABLE operations when the LOCK clause is set to NONE.
If a table has a SPATIAL index, then it cannot be rebuilt by any ALTER TABLE operations when the LOCK clause is set to NONE.
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.
This page is licensed: CC BY-SA / Gnu FDL
ALTER TABLE ... ADD COLUMNIn 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.
When this operation is performed with ALGORITHM set to INSTANT, the tablespace file will have a non-canonical storage format. See Non-canonical Storage Format Caused by Some Operations for more information.
With the exception of adding an auto-increment column, 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)
);
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.
See Instant ADD COLUMN for InnoDB for more information.
ALTER TABLE ... DROP COLUMNIn MariaDB 10.4 and later, InnoDB supports dropping columns from a table with ALGORITHM set to INSTANT. See MDEV-15562 for more information.
When this operation is performed with ALGORITHM set to INSTANT, the tablespace file will have a non-canonical storage format. See Non-canonical Storage Format Caused by Some Operations for more information.
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:
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 COLUMNThis applies to ALTER TABLE ... MODIFY COLUMN for InnoDB tables.
In MariaDB 10.4 and later, InnoDB supports reordering columns within a table with ALGORITHM set to INSTANT. See MDEV-15562 for more information.
When this operation is performed with ALGORITHM set to INSTANT, the tablespace file will have a non-canonical storage format. See Non-canonical Storage Format Caused by Some Operations for more information.
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:
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)InnoDB does not support modifying a column's data type with ALGORITHM set to INSTANT in most cases. There are some exceptions:
InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INSTANT, unless it would require changing the number of bytes requires to represent the column's length. A VARCHAR column that is between 0 and 255 bytes in size requires 1 byte to represent its length, while a VARCHAR column that is 256 bytes or longer requires 2 bytes to represent its length. This means that the length of a column cannot be increased with ALGORITHM set to INSTANT if the original length was less than 256 bytes, and the new length is 256 bytes or more.
In MariaDB 10.4.3 and later, InnoDB supports increasing the length of VARCHAR columns with ALGORITHM set to INSTANT with no restrictions if the ROW_FORMAT table option is set to REDUNDANT. See MDEV-15563 for more information.
In MariaDB 10.4.3 and later, InnoDB also supports increasing the length of VARCHAR columns with ALGORITHM set to INSTANT in a more limited manner if the ROW_FORMAT table option is set to COMPACT, DYNAMIC, or COMPRESSED. In this scenario, the following limitations apply:
The length can be increased with ALGORITHM set to INSTANT if the original length of the column is 127 bytes or less, and the new length of the column is 256 bytes or more.
The length can be increased with ALGORITHM set to INSTANT if the original length of the column is 255 bytes or less, and the new length of the column is still 255 bytes or less.
The length can be increased with ALGORITHM set to INSTANT if the original length of the column is 256 bytes or more, and the new length of the column is still 256 bytes or more.
The length can not be increased with ALGORITHM set to INSTANT if the original length was between 128 bytes and 255 bytes, and the new length is 256 bytes or more.
See MDEV-15563 for more information.
The supported operations in this category support 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 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=COPYBut this succeeds because the original length of the column is less than 256 bytes, and the new length is still less than 256 bytes:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(100);
Query OK, 0 rows affected (0.005 sec)But this fails because the original length of the column is between 128 bytes and 255 bytes, and the new length is greater than 256 bytes:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(255)
) CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(256);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPYBut this succeeds in MariaDB 10.4.3 and later because the table has ROW_FORMAT=REDUNDANT:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(200)
) ROW_FORMAT=REDUNDANT;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(300);
Query OK, 0 rows affected (0.004 sec)And this succeeds in MariaDB 10.4.3 and later because the table has ROW_FORMAT=DYNAMIC and the column's original length is 127 bytes or less:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(127)
) ROW_FORMAT=DYNAMIC
CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(300);
Query OK, 0 rows affected (0.003 sec)And this succeeds in MariaDB 10.4.3 and later because the table has ROW_FORMAT=COMPRESSED and the column's original length is 127 bytes or less:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(127)
) ROW_FORMAT=COMPRESSED
CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(300);
Query OK, 0 rows affected (0.003 sec)But this fails even in MariaDB 10.4.3 and later because the table has ROW_FORMAT=DYNAMIC and the column's original length is between 128 bytes and 255 bytes:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(128)
) ROW_FORMAT=DYNAMIC
CHARACTER SET=latin1;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(300);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPYIn 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.
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:
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)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=INPLACEENUM OptionInnoDB 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.
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 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=COPYSET OptionInnoDB 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.
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 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=COPYIn MariaDB 10.3.8 and later, InnoDB supports removing system versioning from a column with ALGORITHM set to INSTANT. In order for this to work, the system_versioning_alter_history system variable must be set to KEEP. See MDEV-16330 for more information.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50) WITH SYSTEM VERSIONING
);
SET SESSION system_versioning_alter_history='KEEP';
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(50) WITHOUT SYSTEM VERSIONING;
Query OK, 0 rows affected (0.004 sec)ALTER TABLE ... ALTER COLUMNThis applies to ALTER TABLE ... ALTER COLUMN for InnoDB tables.
InnoDB supports modifying a column's DEFAULT value with ALGORITHM set to INSTANT.
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:
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)InnoDB supports removing a column's DEFAULT value with ALGORITHM set to INSTANT.
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.
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 COLUMNInnoDB supports renaming a column with ALGORITHM set to INSTANT, unless the column's data type or attributes changed in addition to the name.
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='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=COPYThis applies to ALTER TABLE ... CHANGE COLUMN for InnoDB tables.
ALTER TABLE ... ADD PRIMARY KEYInnoDB 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=INPLACEThis applies to ALTER TABLE ... ADD PRIMARY KEY for InnoDB tables.
ALTER TABLE ... DROP PRIMARY KEYInnoDB 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=COPYThis applies to ALTER TABLE ... DROP PRIMARY KEY for InnoDB tables.
ALTER TABLE ... ADD INDEX and CREATE INDEXThis applies to ALTER TABLE ... ADD INDEX and CREATE INDEX for InnoDB tables.
InnoDB does not support adding a plain 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=NOCOPYAnd 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=NOCOPYInnoDB does not support adding a FULLTEXT 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='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.042 sec)
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPYAnd this fails:
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.040 sec)
SET SESSION alter_algorithm='INSTANT';
CREATE FULLTEXT INDEX c_index ON tab (c);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPYInnoDB does not support adding a SPATIAL 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 GEOMETRY NOT NULL
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPYAnd this fails:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c GEOMETRY NOT NULL
);
SET SESSION alter_algorithm='INSTANT';
CREATE SPATIAL INDEX c_index ON tab (c);
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPYALTER TABLE ... ADD FOREIGN KEYInnoDB 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=NOCOPYThis applies to ALTER TABLE ... ADD FOREIGN KEY for InnoDB tables.
ALTER TABLE ... DROP FOREIGN KEYInnoDB supports dropping foreign key constraints from a table with ALGORITHM set to INSTANT.
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:
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.
ALTER TABLE ... AUTO_INCREMENT=...InnoDB supports changing a table's AUTO_INCREMENT value with ALGORITHM set to INSTANT.
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:
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=INPLACEThis 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=INPLACEThis 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.
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.
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=INPLACEThis applies to ALTER TABLE ... PAGE_COMPRESSED=... and ALTER TABLE ... PAGE_COMPRESSION_LEVEL=... for InnoDB tables.
ALTER TABLE ... DROP SYSTEM VERSIONINGInnoDB does not support dropping system versioning from a table with ALGORITHM set to INSTANT.
For example:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) WITH SYSTEM VERSIONING;
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab DROP SYSTEM VERSIONING;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACEThis applies to ALTER TABLE ... DROP SYSTEM VERSIONING for InnoDB tables.
ALTER TABLE ... DROP CONSTRAINTIn MariaDB 10.3.6 and later, InnoDB supports dropping a CHECK constraint from a table with ALGORITHM set to INSTANT. See MDEV-16331 for more information.
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:
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50),
CONSTRAINT b_not_empty CHECK (b != '')
);
SET SESSION alter_algorithm='INSTANT';
ALTER TABLE tab DROP CONSTRAINT b_not_empty;
Query OK, 0 rows affected (0.002 sec)This applies to ALTER TABLE ... DROP CONSTRAINT for InnoDB tables.
ALTER TABLE ... FORCEInnoDB 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=INPLACEThis applies to ALTER TABLE ... FORCE for InnoDB tables.
ALTER TABLE ... ENGINE=InnoDBInnoDB 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=INPLACEThis applies to ALTER TABLE ... ENGINE=InnoDB for InnoDB tables.
OPTIMIZE TABLE ...InnoDB does not support optimizing a table 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.
This operation supports the exclusive locking strategy. This strategy can be explicitly chosen by setting the LOCK clause to EXCLUSIVE. When this strategy is used, concurrent DML is not permitted.
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.
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.
Some operations cause a table's tablespace file to use a non-canonical storage format when the INSTANT algorithm is used. The affected operations include:
These operations require the following non-canonical changes to the storage format:
A hidden metadata record at the start of the clustered index is used to store each column's DEFAULT value. This makes it possible to add new columns that have default values without rebuilding the table.
A BLOB in the hidden metadata record is used to store column mappings. This makes it possible to drop or reorder columns without rebuilding the table. This also makes it possible to add columns to any position or drop columns from any position in the table without rebuilding the table.
If a column is dropped, old records will contain garbage in that column's former position, and new records will be written with NULL values, empty strings, or dummy values.
This non-canonical storage format has the potential to incur some performance or storage overhead for all subsequent DML operations. If you notice some issues like this and you want to normalize a table's storage format to avoid this problem, then you can do so by forcing a table rebuild by executing ALTER TABLE ... FORCE with ALGORITHM set to INPLACE:
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab FORCE;
Query OK, 0 rows affected (0.008 sec)However, keep in mind that there are certain scenarios where you may not be able to rebuild the table with ALGORITHM set to INPLACE. See InnoDB Online DDL Operations with ALGORITHM=INPLACE: Limitations for more information on those cases. If you hit one of those scenarios, but you still want to rebuild the table, then you would have to do so with ALGORITHM set to COPY.
There are some known bugs that could lead to issues when an InnoDB DDL operation is performed using the INSTANT algorithm. This algorithm will usually be chosen by default if the operation supports the algorithm.
The effect of many of these bugs is that the table seems to forget that its tablespace file is in the non-canonical storage format.
If you are concerned that a table may be affected by one of these bugs, then your best option would be to normalize the table structure. This can be done by rebuilding the table:
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab FORCE;
Query OK, 0 rows affected (0.008 sec)If you are concerned about these bugs, and you want to perform an operation that supports the INSTANT algorithm, but you want to avoid using that algorithm, then you can set the algorithm to INPLACE and add the FORCE keyword to the ALTER TABLE statement:
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD COLUMN c VARCHAR(50), FORCE;MDEV-20066: This bug could cause a table to become corrupt if a column was added instantly. It is fixed in MariaDB 10.3.18 and MariaDB 10.4.8.
MDEV-20117: This bug could cause a table to become corrupt if a column was dropped instantly. It is fixed in MariaDB 10.4.9.
MDEV-19743: This bug could cause a table to become corrupt during page reorganization if a column was added instantly. It is fixed in MariaDB 10.3.17 and MariaDB 10.4.7.
MDEV-19783: This bug could cause a table to become corrupt if a column was added instantly. It is fixed in MariaDB 10.3.17 and MariaDB 10.4.7
MDEV-20090: This bug could cause a table to become corrupt if columns were added, dropped, or reordered instantly. It is fixed in MariaDB 10.4.9.
MDEV-18519: This bug could cause a table to become corrupt if a column was added instantly. It is fixed in MariaDB 10.6.9, MariaDB 10.7.5, MariaDB 10.8.4 and MariaDB 10.9.2.
MDEV-18519: This bug could cause a table to become corrupt if a column was added instantly. This isn't and won't be fixed in versions less than MariaDB 10.6.
This page is licensed: CC BY-SA / Gnu FDL
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:
ALTER TABLE ... ADD COLUMNIn 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 COLUMNIn 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 COLUMNThis applies to ALTER TABLE ... MODIFY COLUMN for InnoDB tables.
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.
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.
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.
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=INPLACEENUM OptionInnoDB 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.
SET OptionInnoDB 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.
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 COLUMNThis applies to ALTER TABLE ... ALTER COLUMN for InnoDB tables.
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.
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 COLUMNInnoDB 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.
ALTER TABLE ... ADD PRIMARY KEYInnoDB 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=INPLACEThis applies to ALTER TABLE ... ADD PRIMARY KEY for InnoDB tables.
ALTER TABLE ... DROP PRIMARY KEYInnoDB 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=COPYThis applies to ALTER TABLE ... DROP PRIMARY KEY for InnoDB tables.
ALTER TABLE ... ADD INDEX and CREATE INDEXThis applies to ALTER TABLE ... ADD INDEX and CREATE INDEX for InnoDB tables.
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)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.
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=COPYInnoDB 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 INDEXInnoDB 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 KEYInnoDB 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=COPYBut 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 KEYInnoDB 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.
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=INPLACEThis 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=INPLACEThis 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 VERSIONINGInnoDB 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=INPLACEThis applies to ALTER TABLE ... DROP SYSTEM VERSIONING for InnoDB tables.
ALTER TABLE ... DROP CONSTRAINTIn 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 ... FORCEInnoDB 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=INPLACEThis applies to ALTER TABLE ... FORCE for InnoDB tables.
ALTER TABLE ... ENGINE=InnoDBInnoDB 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=INPLACEThis applies to ALTER TABLE ... ENGINE=InnoDB for InnoDB tables.
OPTIMIZE TABLE ...InnoDB does not support optimizing a table 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.
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.
This page is licensed: CC BY-SA / Gnu FDL
Normally, adding a column to a table requires the full table to be rebuilt. The complexity of the operation is proportional to the size of the table, or O(n·m) where n is the number of rows in the table and m is the number of indexes.
In MariaDB 10.0 and later, the ALTER TABLE statement supports online DDL for storage engines that have implemented the relevant online DDL algorithms and locking strategies.
The InnoDB storage engine has implemented online DDL for many operations. These online DDL optimizations allow concurrent DML to the table in many cases, even if the table needs to be rebuilt.
See InnoDB Online DDL Overview for more information about online DDL with InnoDB.
Allowing concurrent DML during the operation does not solve all problems. When a column was added to a table with the older in-place optimization, the resulting table rebuild could still significantly increase the I/O and memory consumption and cause replication lag.
In contrast, with the new instant ALTER TABLE ... ADD COLUMN, all that is needed is an O(1) operation to insert a special hidden record into the table, and an update of the data dictionary. For a large table, instead of taking several hours, the operation would be completed in the blink of an eye. The ALTER TABLE ... ADD COLUMN operation is only slightly more expensive than a regular INSERT, due to locking constraints.
In the past, some developers may have implemented a kind of "instant add column" in the application by encoding multiple columns in a single TEXT or BLOB column. MariaDB Dynamic Columns was an early example of that. A more recent example is JSON and related string manipulation functions.
Adding real columns has the following advantages over encoding columns into a single "expandable" column:
Efficient storage in a native binary format
Data type safety
Indexes can be built natively
Constraints are available: UNIQUE, CHECK, FOREIGN KEY
DEFAULT values can be specified
Triggers can be written more easily
With instant ALTER TABLE ... ADD COLUMN, you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.
Instant ALTER TABLE ... ADD COLUMN is available for both old and new InnoDB tables. Basically you can just upgrade from MySQL 5.x or MariaDB and start adding columns instantly.
Columns instantly added to a table exist in a separate data structure from the main table definition, similar to how InnoDB separates BLOB columns. If the table ever becomes empty, (such as from TRUNCATE or DELETE statements), InnoDB incorporates the instantly added columns into the main table definition. See InnoDB Online DDL Operations with ALGORITHM=INSTANT: Non-canonical Storage Format Caused by Some Operations for more information.
The operation is also crash safe. If the server is killed while executing an instant ALTER TABLE ... ADD COLUMN, when the table is restored InnoDB integrates the new column, flattening the table definition.
In MariaDB 10.3, instant ALTER TABLE ... ADD COLUMN only applies when the added columns appear last in the table. The place specifier LAST is the default. If AFTER col is specified, then col must be the last column, or the operation will require the table to be rebuilt. In MariaDB 10.4, this restriction was lifted.
If the table contains a hidden FTS_DOC_ID column due to a FULLTEXT INDEX, then instant ALTER TABLE ... ADD COLUMN will not be possible.
InnoDB data files after instant ALTER TABLE ... ADD COLUMN cannot be imported to older versions of MariaDB or MySQL without first being rebuilt.
After using Instant ALTER TABLE ... ADD COLUMN, any table-rebuilding operation such as ALTER TABLE … FORCE will incorporate instantaneously added columns into the main table body.
Instant ALTER TABLE ... ADD COLUMN is not available for ROW_FORMAT=COMPRESSED.
In MariaDB 10.3, ALTER TABLE … DROP COLUMN requires the table to be rebuilt. In MariaDB 10.4, this restriction was lifted.
CREATE TABLE t(id INT PRIMARY KEY, u INT UNSIGNED NOT NULL UNIQUE)
ENGINE=InnoDB;
INSERT INTO t(id,u) VALUES(1,1),(2,2),(3,3);
ALTER TABLE t ADD COLUMN
(d DATETIME DEFAULT current_timestamp(),
p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'),
t TEXT CHARSET utf8 DEFAULT 'The quick brown fox jumps over the lazy dog');
UPDATE t SET t=NULL WHERE id=3;
SELECT id,u,d,ST_AsText(p),t FROM t;
SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column';The above example illustrates that when the added columns are declared NOT NULL, a DEFAULT value must be available, either implied by the data type or set explicitly by the user. The expression need not be constant, but it must not refer to the columns of the table, such as DEFAULT u+1 (a MariaDB extension). The DEFAULT current_timestamp() would be evaluated at the time of the ALTER TABLE and apply to each row, like it does for non-instant ALTER TABLE. If a subsequent ALTER TABLE changes the DEFAULT value for subsequent INSERT, the values of the columns in existing records will naturally be unaffected.
The design was brainstormed in April by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was developed by Vin Chen (陈福荣) from the Tencent Game DBA Team.
This page is licensed: CC BY-SA / Gnu FDL