Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.7Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 202.2Kb
Man Pages (Zip) - 307.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Limitations of Online DDL

15.13.9 Limitations of Online DDL

Take the following limitations into account when running online DDL operations:

  • An online DDL operation that copies the table can cause an error if the operation uses all of the available disk space on the file system where the data directory (datadir) resides. To avoid this problem, ensure that there is enough disk space to accommodate online ALTER TABLE operations that copy the table. During these operations, MySQL writes temporary sort files to the MySQL 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. Such operations may require temporary space equal to the amount of data in the table plus indexes.

    As of MySQL 5.7.11, you can use the innodb_tmpdir configuration option to define a separate temporary directory for online DDL operations. The innodb_tmpdir option was introduced to help avoid temporary directory overflows that could occur as a result of large temporary sort files created during online ALTER TABLE operations that rebuild the table.

  • 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 B.3, “Server Error Codes and Messages” 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.

  • Depending on the internal workings of the online DDL operation and the LOCK clause of the ALTER TABLE statement, an online DDL operation may require exclusive access to the table for a brief time during the initial and final phases of the DDL operation. Thus, an online DDL operation might wait before finishing if there is a long-running transaction performing inserts, updates, deletes, or SELECT ... FOR UPDATE on the table; and an online DDL operation might wait before finishing if a similar long-running transaction is started while the ALTER TABLE is in progress.

  • When running an online DDL operation, the thread that runs the ALTER TABLE statement 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.7.4, 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.7.4, OPTIMIZE TABLE is supported with the addition of online DDL support for rebuilding regular and partitioned InnoDB tables. For additional information, see Section 15.13.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:

    • 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 Section 15.16.1, “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).

    For additional information related to running online DDL operations on large tables, see Section 15.13.2, “Performance and Concurrency Considerations for Online DDL”.


User Comments
Sign Up Login You must be logged in to post a comment.