Data Management:Perform lock-free DDL operations

Usage notes

• You must use a database account that has the read and write permissions or a privileged database account. If the database account that you want to use does not have the required permissions, you can perform one of the following operations:

  • Grant permissions to the database account. For more information, see Manage user permissions on MySQL databases.

    Note

    Grant the ALL PRIVILEGES permission or the following read and write permissions to the database account:

    ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE, REPLICATION CLIENT, and REPLICATION SLAVE permissions.

  • Change the database account. For more information, see Modify database instances.

• Make sure that the database on which you want to perform the lock-free DDL operation has sufficient disk space. This is because DMS needs to create a temporary table and copy the data of the original table to the temporary table. You must make sure that the available disk space of the database instance is more than twice the size of the table on which you want to perform the lock-free DDL operation. If the disk space of the database instance is insufficient, the instance is locked. For more information, see View the performance details of a database instance.

• Make sure that the table on which you want to perform the lock-free DDL operation contains a primary key or unique key field. When you perform lock-free schema changes, the primary key or unique key field is used to copy all or part of data from the table and synchronize incremental data.

  Note

  If the table contains only a primary key or unique key field, make sure that the primary key or unique key field is not updated during the schema change. Otherwise, the schema change task fails.

• The table name can be up to 56 characters in length.

• The lock-free schema change feature of DMS does not support non-MySQL databases, such as PolarDB-X 1.0, PolarDB-X 2.0, and ApsaraDB RDS for PostgreSQL.

Procedure

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Database Development > Data Change > Lock-free Change.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the console and choose All Features > Database Development > Data Change > Lock-free Change.

3. On the page that appears, configure the following parameters.

   For more information about parameters, see Procedure.

   Parameter	Description
   Database	Select the database on which you want to perform a schema change from the drop-down list. You can also enter a keyword to search for the database. You can specify one or more databases. Have Permission: You can search for or select only databases on which you have change permissions. All: You can search for or select all databases except the databases for which metadata access control is enabled. Note If you do not have change permissions on a database, choose Security and Specifications > Permission Center > Permission Tickets in the top navigation bar. On the Permission Tickets tab, choose Access apply > Database-Permission in the upper-right corner. On the Access apply Tickets page, apply for the required permissions.
   Execution Method	The execution method of the ticket. Valid values: Ticket Submitter Executes Upon Approval Automatically Execute Upon Approval Last Approver Executes Note If you are a DMS administrator, you can modify execution methods on the Configuration Management page of the O&M module. For more information, see Configuration management.
   Affected Rows	The estimated number of data rows that may be affected by the data change.
   SQL Statements for Change	Enter DDL statements in the field, such as ALTER TABLE or OPTIMIZE. Note You can also enter DML statements to perform lock-free DML operations. For more information, see Perform lock-free DML operations.

4. Click Submit.

   DMS prechecks the SQL statements. If the precheck fails, click SQL Statements for Modification in the Precheck step to modify the SQL statements and try again.

5. After the ticket is approved, click Execute Change in the Execute step.

6. Configure the parameters that are described in the following table for the task.

   Parameter	Description
   Execution Strategy	Running immediately: This is the default value. After you click Confirm Execution, the task is immediately run. Schedule: If you select this option, you must specify the start time for the task. After you click Confirm Execution, the task is run at the specified point in time.
   Specify End Time	on: Specify the time when the task ends. The system stops the task at the specified end time regardless of whether the task is complete. This prevents the task from affecting your business during peak hours. off: This is the default value.

7. Click Confirm Execution.

   Note

   You can pause a task at any point in time when DMS is executing the task. The paused task will be restarted from the beginning when resumed.

   • You can view the status, settings, and details of the task in the Execute step. You can also view the scheduling logs of the task.

   • Alternatively, you can choose O&M > Task in the top navigation bar. On the Task tab, find the task and view the progress of the lock-free schema change task.