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.

Background information

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 provided.
    • 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.

Prerequisites

You are a database administrator (DBA) or a DMS administrator.

Procedure

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose System > Instance.
  3. Find the instance that you want to edit, move the pointer over More in the Actions column, and then select Edit.
    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.
  4. Click Advanced Information.
  5. 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
  6. Click Submit.

What to do next

You can use the following methods to change schemas. Then, DMS changes schemas based on the value that you selected in Step 5. The value can be Open (DMS OnlineDDL first) or Open (MySQL Native OnlineDDL first).