Limitations of Online DDL for MySQL

During an online DDL operation that copies the table, files are written to the temporary directory ($TMPDIR on Unix, %TEMP% on Windows, or the directory specified by the --tmpdir configuration variable). Each temporary file is large enough to hold one column in the new table or index, and each one is removed as soon as it is merged into the final table or index.

The table is copied, rather than using Fast Index Creation when you create an index on a TEMPORARY TABLE. This has been reported as MySQL Bug #39833.

InnoDB handles error cases when users attempt to drop indexes needed for foreign keys. See Section 14.18.5, “InnoDB Error Codes” for information related to error 1553.

The ALTER TABLE clause LOCK=NONE is not allowed if there are ON...CASCADE or ON...SET NULL constraints on the table.

During each online DDL ALTER TABLE statement, regardless of the LOCK clause, there are brief periods at the beginning and end requiring anexclusive lock on the table (the same kind of lock specified by the LOCK=EXCLUSIVE clause). Thus, an online DDL operation might wait before starting if there is a long-running transaction performing inserts, updates, deletes, or SELECT ... FOR UPDATE on that table; and an online DDL operation might wait before finishing if a similar long-running transaction was started while the ALTER TABLE was in progress.

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply an “online log” of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the “online log”. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. Prior to 5.6.17, there is no online DDL support for this operation. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. As of 5.6.17, OPTIMIZE TABLE is supported with the addition of online DDL support for rebuilding regular and partitioned InnoDB tables. For additional information, see Section 14.10.1, “Overview of Online DDL”.

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns the following error:

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. 
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

These limitations are generally applicable to online DDL operations on large tables where table copying is involved: