Data Management:Normal data change

Step 1: Configure and submit a ticket

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

2. Move the pointer over the icon in the upper-left corner and choose All Features > Database Development > Data Change > Normal Data Modify.

   In normal mode, choose Database Development > Data Change > Normal Data Modify in the top navigation bar.

3. In the Apply step of the Data Change Tickets wizard, fill in the ticket details.

   The parameters below reflect an instance managed in Security Collaboration mode. Instances in Stable Change mode may have slightly different options. The data source instance change feature is in canary release.

   Parameter	Required	Description
   Change Target	Yes	Select Database or Data Source Instance. Data source instance changes are supported only for ApsaraDB RDS for MySQL, PolarDB for MySQL, and AnalyticDB for MySQL instances.
   Database or Instances	Yes	The database or instance to change. Select one that you have change permissions on. You can select only a single data source instance.
   Reason Category	Yes	The reason for the data change. Helps you locate the ticket later. DMS administrators can add or modify reason categories in O&M > Configuration Management. For details, see Configuration management.
   Business Background	Yes	The purpose of the change. A detailed description speeds up the approval process.
   Execution Method	Yes	Who runs the task after approval. Options: Ticket Submitter Executes Upon Approval, Automatically Execute Upon Approval, or Last Approver Executes. DMS administrators can modify available options in O&M > Configuration Management. For details, see Configuration management.
   Affected Rows	Yes	The estimated number of rows affected by the change.
   SQL Statements for Change	Yes	How to provide the SQL: Text (enter directly) or Attachment (upload a file).
   SQL Text	Yes (if Text)	The SQL statements to run. Separate multiple statements with semicolons (;). DMS validates syntax on submit — invalid SQL blocks submission. If Change Target is Data Source Instance, prefix all table names with ${dbName}.${tableName}.
   Attachment	Yes (if Attachment)	The file containing SQL statements. Supported formats: TXT, ZIP, or SQL. Maximum size: 15 MB.
   SQL Statements for Rollback	No	Rollback SQL as Text or Attachment. If provided, DMS pre-fills these statements when you create a rollback ticket. If not provided, you must enter them manually.
   SQL Text (rollback)	No (if Text)	The SQL statements used to roll back the change.
   Attachment (rollback)	No (if Attachment)	A file containing the rollback SQL. Supported formats: TXT, ZIP, or SQL. Maximum size: 15 MB.
   Change Stakeholder	No	Additional users who can view ticket details and participate in the approval process. Users not listed here (other than DMS administrators and DBAs) cannot access ticket details.
   Attachments	No	Supporting files that provide additional context for the change.

4. Click Submit.

Step 2: Precheck SQL statements

After you submit the ticket, DMS automatically runs a precheck to validate the SQL syntax, confirm the statement types are supported, and verify that you have the required permissions to execute the SQL statement. If the precheck fails, update the SQL as indicated and resubmit.

Step 3: Approve the ticket

After the precheck passes, click Submit for Approval. In the Prompt dialog, click OK.

By default, DBA approval is required. To change the default approval template, see the Change the default approval template section of the "SQL Correct" topic.

Step 4: Execute the change

1. After the ticket is approved, click Execute Change. In the Task Settings dialog, configure the execution options and click Confirm Execution.

   This step is skipped automatically if you selected Automatically Execute Upon Approval in Step 1. After a suspended task is resumed, execution continues from where it was paused.

   SQL execution is also governed by checkpoints in your security rules — such as lock timeout checks, database load checks, and post-execution sleep policies. To review the checkpoints for a security rule, open the rule's details page and click SQL execution control. To modify checkpoint settings, see Configure the control on SQL execution.

   Parameter	Description
   Execution Strategy	When to run the task. Running immediately starts the task as soon as you click Confirm Execution. Schedule lets you specify a date and time (for example, 00:00:00 on May 22, 2024).
   Specify End Time	The time at which DMS stops the task, regardless of completion status. Use this to prevent long-running changes from overlapping with peak hours. The actual stop time may differ by up to ±1 minute.
   Enable Submit as Single Transaction	Default: off. When on, all SQL statements run as a single transaction. If any statement fails, all DML statements in the transaction are rolled back. DDL statements cannot be rolled back. When off, each statement is its own transaction, and a failure stops execution without rolling back earlier statements.
   Enable Backup	Default: off. When on, DMS generates backup SQL before running UPDATE or DELETE statements — a REPLACE INTO statement for MySQL and MariaDB databases, or an INSERT statement for other databases. Backup is not supported for MongoDB or Redis. Supported MySQL database types: ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and MySQL databases that are not on Alibaba Cloud.
   Primary/Secondary Node Check	Monitors data synchronization between primary and secondary instances to maintain high availability.
   Canary Release Type	Controls how DMS steps through SQL execution. No Canary Release: runs all statements automatically. Suspend after Executing the First SQL Statement: pauses after the first statement succeeds; click Retry to continue. Suspend after Executing an SQL Statement: pauses after each statement; click Retry to run the next one.

2. After the task completes, click Details in the Actions column to review the ticket status, run count, number of affected rows, executed statements, and logs.

Verify the change

In the Basic Information section of the ticket details panel, move the pointer over the database name and click Query.

On the SQL Console tab, check that the table data matches your expectations.

Backup support matrix

Before rolling back, confirm that your scenario is supported:

Backup is not supported for MongoDB or Redis databases.

Create a rollback ticket

1. In the Execute section, click Generate Rollback Ticket in the Actions column for the database.

2. Select a rollback source and click OK:

   • Rollback text — Pre-filled from the rollback SQL you entered when submitting the ticket

   • Rollback attachment — The rollback file you uploaded when submitting the ticket

   • Backup file — The backup file generated during execution (available only if backup was enabled)

3. Optionally edit the rollback content:

   • Rollback text — Modify the SQL statements directly in the editor.

   • Rollback attachment or backup file — Download the file, edit the SQL, and re-upload it to the rollback ticket.

4. Review the rollback details and click Submit. The rollback ticket follows the same workflow as a Normal Data Modify ticket.

Download the backup file

To save the backup file or upload it to DMS after the change, click Download Backup in the Actions column.

A backup file contains:

• The original SQL statements executed for the change

• The SELECT clause from the original SQL

• The SQL statements generated for data backup

Example:

/*
[Database]:   rds@rm-bp144d5ky4l4rli0417****.mysql.rds.aliyuncs.com:3306[rds mysql]
*/

/*
[SQL]:


UPDATE t_order
SET product_id = 88
WHERE id = 10054
[BACKUP SQL]:    SELECT *
FROM t_order
WHERE id = 10054
*/
REPLACE INTO `t_order`(`id`,`product_id`,`gmt_create`,`gmt_modified`,`customer_id`,`price`,`status`,`province`) VALUES
(10054,81,'2021-12-14 09:44:44','2021-12-14 09:44:44',71,63.45,'Success','Hangzhou');

Extract the backup SQL from the file, verify it is correct, then submit a Normal Data Modify ticket to restore the data.

If an incorrect UPDATE statement was executed and DMS generated an INSERT statement for backup instead of a REPLACE INTO statement, restore the data manually.