Online DDL is a new feature available to ApsaraDB for RDS instances that run MySQL 5.6.

This feature allows you to perform data definition language (DDL) operations such as index creation on tables without blocking data manipulation language (DML) operations and SELECT queries that run concurrently with the DDL operations.

Note After you update your database engine version from an earlier version such as MySQL 5.5 to MySQL 5.6, you may not be able to perform DDL operations on a table for the first time because the table format is an earlier version. In this case, run the following command to convert the table format:
alter table <The name of the table whose format you want to convert> engine=innodb;

For information about more features supported by ApsaraDB RDS for MySQL, see AliSQL Release Notes.

Limits

DDL operation In-place supported Table-copy required DML concurrency permitted Query concurrency permitted Remarks
Create a common index on a table Yes No Yes Yes None
Create a full-text index on a table Yes No No Yes You must create the first full-text index by using the table-copy method. Then, you can create other full-text indexes on the table by using the in-place method.
Delete an index from a table Yes No Yes Yes This DDL operation only modifies the metadata of the table.
Optimize a table Yes Yes Yes Yes If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.
Set the default value in a column of a table Yes No Yes Yes This DDL operation only modifies the metadata of the table.
Set the default value in an auto-increment column of a table. Yes No Yes Yes This DDL operation only modifies the metadata of the table.
Add a foreign key constraint to a table Yes No Yes Yes You can run the set foreign_key_checks=0; command to disable the foreign_key_checks option, so you do not need to copy the table.
Delete a foreign key constraint from a table Yes No Yes Yes You can enable or disable the foreign_key_checks option.
Rename a column of a table Yes No Yes Yes If this DDL operation only changes the column name without changing the data type, concurrent DML operations are permitted.
Add a column to a table Yes Yes Yes Yes

If the column you want to add is an auto_increment column, you cannot perform concurrent DML operations.

You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.

Delete a column from a table Yes Yes Yes Yes You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Change the sequence of columns in a table Yes Yes Yes Yes You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Modify the Row_Format attribute of a table Yes Yes Yes Yes You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Modify the Key_Block_Size attribute of a table Yes Yes Yes Yes You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Set the values in a column of a table to NULL Yes Yes Yes Yes You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.
Set the values in a column of a table to NOT NULL Yes Yes Yes Yes

This DDL operation only succeeds when the SQL_MODE option is set to STRICT_ALL_TABLES or STRICT_TRANS_TABLES and the column does not contain NULL values.

You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.

Modify the data type of a column in a table No Yes No Yes None
Add a primary key to a table Yes Yes Yes Yes

You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.

In addition, if you set the values in the column to NOT NULL, you cannot set the ALGORITHM option to INPLACE.

Delete the primary key of a table and add a new one Yes Yes Yes Yes

You can only set the ALGORITHM option to INPLACE when you execute one ALTER TABLE statement to delete the existing primary key and add a new one.

You can set the ALGORITHM option to INPLACE, but this setting reorganizes the table data and increases the overhead.

Delete the primary key of a table No Yes No Yes None
Convert the character set of a table No Yes No Yes If the new character set uses a different encoding format, you must rebuild the table.
Specify a new character set for a table No Yes No Yes If the new character set uses a different encoding format, you must rebuild the table.
Rebuild a table with the force option Yes Yes Yes Yes If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.

Rebuild a table

alter table ... engine=innodb

Yes Yes Yes Yes If a full-text index is created on the table, you cannot set the ALGORITHM option to INPLACE.

Set the persistent statistics attribute of a table

Yes No Yes Yes This DDL operation only modifies the metadata of the table.
  • In-place supported: controlled by the ALGORITHM option in the DDL operation. This method consumes less disk space and I/O resources than the copy-table method.
  • Copy-table required: controlled by the ALGORITHM option in the DDL operation. This method consumes more disk space and I/O resources than the in-place method.
  • DML concurrency permitted: controlled by the LOCK option in the DDL operation.
  • Query concurrency permitted: In most cases, queries concurrent with the DDL operation are permitted.
  • For more information, see Online DDL Operations.
  • The DDL operation on a table modifies the table metadata. Therefore, you may wait for the metadata lock on that table. For information about how to handle metadata locks, see Use DMS to release metadata locks.
  • The in-place method works in opposite to the table-copy method. However, even if the in-place method is specified, a DDL operation may still involve table copying, for example, the DDL operation used to add a column to a table.

Configuration suggestions

  • ALGORITHM: When you perform a DDL operation, we recommend that you set this option to INPLACE to avoid performance deterioration caused by disk space usage or I/O issues. If the DDL operation does not support this setting, it returns an error.
    alter table area_bak algorithm=inplace, modify father text;
    
    ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
  • LOCK: When you perform a DDL operation, we recommend that you set this option to NONE. This ensures the smooth execution of DML operations concurrent with the DDL operation. If the DDL operation does not support this setting, it returns an error.
    alter table area ALGORITHM=copy, lock=none,CONVERT TO CHARACTER SET utf8mb4;
    
    ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

By default, ApsaraDB RDS for MySQL sets the ALGORITHM option to INPLACE and the LOCK option to NONE, so you do not need to configure these two options. However, if you are worried that a DDL operation may increase system loads or block DML operations on the target table, we recommend that you set the ALGORITHM option to INPLACE and the LOCK option to NONE separately for testing. Therefore, an error is returned when one of the two settings is not supported.

Example:

alter table area algorithm=inplace, lock=none, add index idx_fa (father);
Note We recommend that you perform all DDL operations during off-peak hours to avoid interruptions to your business.

If your database engine version (for example, MySQL 5.5) does not support online DDL operations, you can use the pt-online-schema-change tool of Percona.

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

Troubleshooting

When you perform an online DDL operation on a large table with concurrent DML operations, the following error may be returned:

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

The system records any concurrent DML operations you perform during a DDL operation by creating a temporary log file when a table is modified or when an index is created. You can enlarge the temporary log file from the size specified by the innodb_sort_buffer_size parameter to the size specified by the nnodb_online_alter_log_max_size parameter.

If the temporary log file exceeds the maximum size, the DDL operation returns a failure message and rolls back all not committed concurrent DML operations. Therefore, we recommend that you use the innodb_online_alter_log_max_size parameter to specify a proper file size, so more concurrent DML operations are permitted. However, a larger file size increases the time taken by the DDL operation to lock the table and apply logged data to the table.

Solution

If your ApsaraDB for RDS instance runs MySQL 5.6 or 5.7, you can reconfigure the innodb_online_alter_log_max_size parameter in the ApsaraDB for RDS console. For more information, see Reconfigure parameters for an RDS MySQL instance.