How do I use the online DDL feature for an ApsaraDB RDS instances that run MySQL 5.6? - ApsaraDB RDS

ApsaraDB RDS instances that run MySQL 5.6 or later support online DDL. This feature lets you run DDL operations, such as index creation, without blocking concurrent DML operations or SELECT queries.

Prerequisites

An ApsaraDB RDS for MySQL instance that runs MySQL 5.6 or later

After you upgrade from MySQL 5.5 to MySQL 5.6, the first DDL operation on a table may fail because the table still uses an older format. Run the following statement to convert the table format:
ALTER TABLE <table_name> ENGINE=InnoDB;

Best practices

Important

Perform all DDL operations during off-peak hours to avoid business impact.

Set ALGORITHM=INPLACE to reduce disk space and I/O consumption. If the operation does not support in-place processing, MySQL returns an error.
Set LOCK=NONE to allow concurrent DML operations. If the operation does not support this lock mode, MySQL returns an error.

By default, ApsaraDB RDS for MySQL sets ALGORITHM to INPLACE and LOCK to NONE. You do not need to specify these clauses explicitly. However, to verify that an operation supports both settings before you run it in production, specify both clauses. MySQL returns an error if either setting is not supported.

Example

ALTER TABLE area ALGORITHM=INPLACE, LOCK=NONE, ADD INDEX idx_fa (father);

If the ALGORITHM=INPLACE clause is not supported, MySQL returns the following error:

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

If the LOCK=NONE clause is not supported, MySQL returns the following error:

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

If your instance runs MySQL 5.5 or earlier and does not support online DDL, use the pt-online-schema-change tool from Percona Toolkit.

For more information about the ALTER TABLE syntax, see ALTER TABLE Syntax.

DDL operations support matrix

The following table lists DDL operations and their online DDL behavior. Column definitions:

In-place -- Whether the operation supports the ALGORITHM=INPLACE clause. In-place operations consume less disk space and I/O than table copy operations.
Table copy -- Whether the operation requires copying the table, as controlled by the ALGORITHM clause.
DML concurrent -- Whether concurrent DML operations are allowed, as controlled by the LOCK clause.
Query concurrent -- Whether concurrent SELECT queries are allowed. In most cases, concurrent queries are permitted.

DDL operation	In-place	Table copy	DML concurrent	Query concurrent	Remarks
Create a common index	Yes	No	Yes	Yes	--
Create a full-text index	Yes	No	No	Yes	The first full-text index on a table requires the table copy method. Subsequent full-text indexes can use the in-place method.
Delete an index	Yes	No	Yes	Yes	Modifies metadata only.
Optimize a table	Yes	Yes	Yes	Yes	Cannot use `ALGORITHM=INPLACE` if the table has a full-text index.
Set a column default value	Yes	No	Yes	Yes	Modifies metadata only.
Set an auto-increment column default value	Yes	No	Yes	Yes	Modifies metadata only.
Add a foreign key constraint	Yes	No	Yes	Yes	Run `SET foreign_key_checks=0;` to skip table copying.
Delete a foreign key constraint	Yes	No	Yes	Yes	You can enable or disable `foreign_key_checks`.
Rename a column	Yes	No	Yes	Yes	Concurrent DML is allowed only if the data type remains unchanged.
Add a column	Yes	Yes	Yes	Yes	No concurrent DML if the column is auto-increment. `ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Delete a column	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Change column order	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Modify the `ROW_FORMAT` attribute	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Modify the `KEY_BLOCK_SIZE` attribute	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Set a column to NULL	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Set a column to NOT NULL	Yes	Yes	Yes	Yes	Requires the `sql_mode` system variable set to `STRICT_ALL_TABLES` or `STRICT_TRANS_TABLES`. The column must not contain NULL values. `ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Modify a column data type	No	Yes	No	Yes	--
Add a primary key	Yes	Yes	Yes	Yes	`ALGORITHM=INPLACE` reorganizes table data and increases overhead. If the column is set to NOT NULL, `ALGORITHM=INPLACE` is not supported.
Delete and add a primary key	Yes	Yes	Yes	Yes	Delete the existing primary key and add a new one in a single `ALTER TABLE` statement. `ALGORITHM=INPLACE` reorganizes table data and increases overhead.
Delete a primary key	No	Yes	No	Yes	--
Convert a character set	No	Yes	No	Yes	Requires a table rebuild if the new character set uses a different encoding format.
Specify a character set	No	Yes	No	Yes	Requires a table rebuild if the new character set uses a different encoding format.
Rebuild a table (FORCE)	Yes	Yes	Yes	Yes	Cannot use `ALGORITHM=INPLACE` if the table has a full-text index.
Rebuild a table (ENGINE=InnoDB)	Yes	Yes	Yes	Yes	Cannot use `ALGORITHM=INPLACE` if the table has a full-text index.
Set the persistent statistics attribute	Yes	No	Yes	Yes	Modifies metadata only.
Modify a table comment	Yes	No	Yes	Yes	--

Even when ALGORITHM=INPLACE is specified, some operations still involve internal table copying. For example, adding a column in-place still reorganizes table data.

For the complete MySQL reference, see Online DDL Operations.

Metadata lock considerations

DDL operations modify table metadata and may need to wait for the metadata lock on the target table. If a long-running transaction holds the lock, the DDL operation blocks. For guidance on resolving metadata lock waits, see Use DMS to release metadata locks.

Troubleshooting: innodb_online_alter_log_max_size exceeded

When you run an online DDL operation on a large table with concurrent DML activity, you may encounter the following error:

ALTER TABLE rd_order_rec ADD INDEX idx_cr_time_detail (cr_time, detail);

ERROR 1799(HY000): Creating index 'idx_cr_time_detail' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

Cause

During an online DDL operation, MySQL records concurrent DML changes in a temporary log file. The log file is extended as required by the value of innodb_sort_buffer_size but cannot exceed the value of innodb_online_alter_log_max_size.

When the log file exceeds this limit:

The DDL operation fails.
All uncommitted concurrent DML operations roll back.

Solution

Increase the value of innodb_online_alter_log_max_size in the ApsaraDB RDS console. For instructions, see Modify instance parameters.

A larger log file allows more concurrent DML operations during DDL execution, but it also increases the time the DDL operation takes to lock the table and apply the logged changes at the end. Choose a value that balances concurrency with DDL completion time.

ApsaraDB RDS:Use the online DDL feature for an ApsaraDB RDS for MySQL instance