Take the following limitations into account when running online DDL operations:
During an online DDL operation that copies the table, files
are written to the temporary directory
$TMPDIR on Unix,
on Windows, or the directory specified by the
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
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
ALTER TABLE clause
LOCK=NONE is not allowed if there are
NULL constraints on the table.
During each online DDL
TABLE statement, regardless of the
LOCK clause, there are brief periods at the
beginning and end requiring an
exclusive 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,
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
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
TABLE ... ALGORITHM=INPLACE for tables that include
temporal columns (
TIMESTAMP) and have not been
ALTER TABLE ...
ALGORITHM=COPY. In this case, an
ALTER TABLE ...
ALGORITHM=INPLACE operation returns the following
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:
There is no mechanism to pause an online DDL operation or to throttle I/O or CPU usage for an online DDL operation.
Progress monitoring capability for online DDL operations
is limited until MySQL 5.7.6, which introduces Performance
Schema stage events for monitoring
ALTER TABLE progress. See
Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema.
Rollback of an online DDL operation can be expensive should the operation fail.
Long running online DDL operations can cause replication lag. An online DDL operation must finish running on the master before it is run on the slave. Also, DML that was processed concurrently on the master is only processed on the slave after the DDL operation on the slave is completed (Bug #73196).
operation can cause a server exit if the operation uses
all of the available disk space on the file system where
the data directory
datadir) resides (Bug
#77497). To avoid this problem, ensure that there is
enough disk space to accommodate operations that copy the
table. During these operations, MySQL writes temporary
sort files to the temporary directory
For additional information related to running DDL operations on large tables, see Section 14.10.2, “Performance and Concurrency Considerations for Online DDL”.