Data Management (DMS) introduces a new feature to change schemas without locking tables. This feature prevents your business from being blocked by table locks due to schema changes. This feature also minimizes the latency that is caused by the online DDL feature of MySQL during primary-secondary replication. This feature creates one or more temporary tables that use the new schema, replicates full data and incremental binary logs to the temporary tables, and then stores the temporary tables as permanent tables.

Prerequisites

This feature is enabled for an ApsaraDB RDS for MySQL instance. For more information, see Enable the Open (DMS OnlineDDL first) feature.
Note In the following example, you must enable this feature for the ApsaraDB RDS for MySQL instance that simulates the development environment in advance.

Procedure

In the following example, the feature changes the data type of the long_text_a column in the big_table table from varchar(1024) to text(1024).

  1. Submit a ticket as a regular user.
    1. Log on to the DMS console as a regular user.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Data Change > Normal Data Modify.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Plans > Change > Normal Data Modify.
    3. Set the parameters that are described in the following table and click Submit.
      Data Change Ticket Application
      Parameter Description
      Database Required. The database in which you want to change data. In this example, select the poc_dev database.
      Reason Category Required. The reason for this data change. This helps you find the ticket in subsequent operations.
      Business Background Required. The purpose or objective of this data change. This reduces unnecessary communication.
      Execution Method Required. The method to be used to execute the data change. In this example, select Last Auditor Execute.
      Affected Rows Required. The estimated number of data rows to be affected by this data change. To obtain the actual number of affected rows, you can use the COUNT function in SQL statements and execute the SQL statements on the SQLConsole tab.
      SQL Statements for Change Required. The SQL statement that you want to execute to change data. Enter the following ALTER statement:
      ALTER TABLE `big_table`
        MODIFY COLUMN `long_text_a` text(1024) NULL AFTER `name`;
      Note This statement changes the data type of the long_text_a column from varchar(1024) to text(1024).
      SQL Statements for Rollback Optional. The SQL statement that you can execute to roll back the data change.
      Change Stakeholder Optional. The stakeholders of the data change. All the specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
    4. On the Ticket Details page, confirm the ticket details.
      Note You can view the ticket details on the Ticket Details page. DMS prechecks the SQL statement that you want to execute. The precheck items include the syntax of the SQL statement, whether the type of the SQL statement matches the type that you configured in the security rules, the permissions of the user who submitted the ticket, and the estimated number of rows to scan.
    5. After the ticket details are confirmed and the precheck is complete, click Submit for Approval. In the message that appears, click OK.
      Submit for Approval
      Note You can modify the ticket details only before you submit the ticket for approval.
  2. Approve and handle the ticket as a DMS administrator.
    1. Log on to the DMS console as a DMS administrator.
      Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. On the Home page of the DMS console, click Pending Tickets in the My Tickets section.
    3. On the My Tickets page, find the ticket that you want to handle and click the ticket number in the Ticket Number column.
    4. In the Ticket Details panel, confirm the data change information in the ticket and click Approve.
    5. In the dialog box that appears, enter comments and click Submit.
      Confirm and approve the data change ticket
    6. In the Execute step, click Execute Change. In the dialog box that appears, set the parameters that are described in the following table and click Confirm Execution.
      Set the parameters in the Task Settings dialog box
      Parameter Description
      Execute Immediately Specifies whether to run the task immediately or at a scheduled time. Valid values:
      • Running immediately: DMS runs the task immediately after you submit the task.
      • Schedule: DMS runs the task at the scheduled time that you specify.
      Default value: Running immediately.
      Transaction Control Specifies whether to enable transaction control. Valid values:
      • on: If an SQL statement fails to be executed, all the executed DML statements in the same transaction are rolled back. DDL statements cannot be rolled back.
      • off: One SQL statement is executed at a time. If an SQL statement fails to be executed, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back.
      Default value: off.
      Data Backup Specifies whether to back up data. Valid values:
      • on: DMS generates INSERT scripts to back up the data that will be affected when UPDATE or DELETE statements are executed.
      • off: No backup scripts are generated for the preceding data.
      Default value: on.
      Note After you click Confirm Execution, DMS starts to run the task.

View the task progress and verify the schema as a DMS administrator

  1. Log on to the DMS console as a DMS administrator.
    Note To switch to the previous version of the DMS console, click the Return to old version icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. In the top navigation bar, click SQL Console. The SQL Console tab appears.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
  3. In the Please select the database first dialog box, select the poc_dev database from the drop-down list or enter a keyword in the field to search for the database. Then, click Confirm.
  4. On the SQLConsole tab, enter the following SQL statement and click Execute to view the tables in the database:
    SHOW TABLES;
    Query temporary tables
    Note When DMS changes the schema of a table without locking tables, two temporary tables are generated.
  5. On the Task page, click the task number of the task that you want to view. The Execution details dialog box appears.
  6. Click Progress in the Operation column.
  7. In the Lock-Free structure change dialog box, view the task progress.
  8. After the task is complete, verify the schema.
    The schema of the big_table table is changed, and the temporary tables are deleted.