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.
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.
|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
|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|
|Modify the Key_Block_Size attribute of a table||Yes||Yes||Yes||Yes|
|Set the values in a column of a table to NULL||Yes||Yes||Yes||Yes|
|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.
|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||
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.
|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.
- 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.
alter table area algorithm=inplace, lock=none, add index idx_fa (father);
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.
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.
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.