79540

Limitations of Online DDL

Take the following considerations into account when creating or dropping InnoDB indexes:

•    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 [571]

configuration variable). Each temporary file is large enough to hołd one column in the new table or

index, and each one is removed as soon as it is merged into the finał table or index.

•    An alter table statement that contains drop index and add INDEX clauses that both name

the same index uses a table copy, not Fast lndex Creation.

•    The table is copied, rather than using Fast lndex 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 forforeign keys. See

section Section 14.2.5.9, "Better Error Handling when Dropping lndexes” for details.

•    The alter table clause lock=none is not allowed if there are

ON...CASCADE orON...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 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,

or SELECT ... FOR

uPdatę on that table; and an online DDL operation might wait before finishing if a similar longrunning

transaction was started while the alter table was in progress.

•    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.