In Data Management (DMS), you must enable the Open (DMS OnlineDDL first) feature for an instance before you perform data definition language (DDL) operations to change schemas without locking tables in the instance.
When you perform native online DDL operations of MySQL to change schemas, tables may be locked.
- In versions earlier than MySQL 5.5, only the Table-Copy algorithm can be used in DDL
statements for schema changes. In MySQL 5.5 and later, the In-Place algorithm is also
- Table-Copy: You can change the schema of a table by generating a temporary table and copying data from the original table to the temporary table. In this process, the table is locked and you cannot write data to the table.
- In-Place: When you add or modify indexes, you can still read data from and write data to a table in a database.
- In MySQL 5.6 and later, online DDL operations can be performed on InnoDB tables. The online DDL operations contain various types of DDL operations. For example, you can perform online DDL operations to add, delete, and rename columns, or add and modify indexes. However, you cannot perform specific common DDL operations by using online DDL operations. For example, you cannot perform online DDL operations to change column data types, column lengths, or character sets.
To resolve this issue, Data Management (DMS) allows you to perform DDL operations to change schemas without locking tables. This prevents your business from being affected by table locks that are caused due to schema changes. This also avoids the latency in synchronization between primary and secondary databases that occurs when schemas are changed by using native online DDL operations. For more information, see DDL-based lockless change.
You are a database administrator (DBA) or a DMS administrator.
- Log on to the DMS console.
- , move the pointer over the More icon and choose .
- Find the instance that you want to edit, move the pointer over More in the Actions column, and then select Edit instance. Note You can also right-click the instance that you want to edit in the left-side navigation pane of the DMS console and select Edit instance.
- Click Advanced information.
- Select Open (DMS OnlineDDL first) from the Lock-free Schema Change drop-down list.
Value Description Remarks Open (DMS OnlineDDL first) DMS uses the in-house feature to change schemas without locking tables. In this case, the schema change takes longer than it takes by using native online DDL operations of MySQL. However, this feature does not affect data copy and generates no latency. Open (MySQL Native OnlineDDL first) DMS preferentially uses native online DDL operations of MySQL to change schemas. If tables are locked in this case, DMS uses the in-house feature to change schemas to ensure that tables are not locked. In this case, the schema change is faster, but parallel threads for replica transactions may become serial threads. This leads to latency in synchronization between primary and secondary databases. Close DMS routes SQL statements to the MySQL instance for execution without processing the statements. N/A
- Click Submit.