Data Management:Standard data change

Step 1: Configure and submit the ticket

1. Log in to DMS 5.0.

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

   Note

   If you use the DMS console in normal mode, choose Database Development > Data Change > Normal Data Modify in the top navigation bar.

3. On the Data Change Ticket Request page, configure the ticket parameters. The following table describes some of the parameters.

   Note

   • The following example shows how to configure parameters for an instance in Security Collaboration mode. The parameters may vary if you select an instance in Stable Change mode.

   • The feature to change data source instances is being rolled out in phases.

   Configuration item	Required	Description
   Change Target	Yes	You can select Database or Data Source Instance. Note Currently, you can only select MySQL instances of the following types for data source instance changes: RDS MySQL, PolarDB for MySQL, and AnalyticDB for MySQL.
   Database or Data Source Instance	Yes	Search for and select a database or instance for which you have change permissions. Note Currently, you can select only one instance for a data source instance change.
   Reason Category	Yes	Select a reason for the data change to make it easier to find later. Note Administrators can configure the list of reason categories in the Operations Management > Configuration Management section. For more information, see Configuration management.
   Business Background	Yes	Provide a detailed description of the reason or goal for the change to reduce communication overhead.
   Execution Method	Yes	Select an execution method for the ticket: After approval, the submitter executes the operation. Execute automatically after approval. Execute by last approver. Note Administrators can modify the list of execution methods in the Operations Management > Configuration Management section. For more information, see Configuration management.
   Affected Rows	Yes	Estimate the number of data rows that this change will affect.
   Change SQL	Yes	You can select Text or Attachment.
   SQL Text	Yes	This configuration item appears only if you set Change SQL to Text. In the SQL text box, enter the SQL statements that can be directly executed. Note Separate multiple SQL statements with a semicolon (;). If the change target is an instance, you must add the database name before each table name in the SQL statements. Use the format ${dbName}.${tableName}. When you submit the ticket, the system automatically checks the SQL syntax. If the syntax is incorrect, you cannot submit the ticket.
   Attachment	Yes	This configuration item appears only if you set Change SQL to Attachment. Upload the SQL attachment for the change. Note The attachment can be a .txt, .zip, or .sql file. The maximum file size is 15 MB.
   Rollback SQL	No	You can select Text or Attachment. Note DMS automatically fills in the rollback SQL information when creating a rollback ticket only if you enter the rollback SQL or upload a rollback file. Otherwise, you must enter the information yourself.
   SQL Text	No	This configuration item appears only if you set Rollback SQL to Text. Enter the rollback SQL statements. The rollback SQL is the reverse script of the change SQL.
   Attachment	No	This configuration item appears only if you set Rollback SQL to Attachment. Click Upload File to upload the rollback SQL attachment. Note The attachment can be a .txt, .zip, or .sql file. The maximum file size is 15 MB.
   Stakeholders	No	The specified stakeholders can view the ticket and collaborate on it. Users who are not stakeholders cannot view the ticket, except for administrators and DBAs.
   Ticket Attachment	No	Upload an attachment to provide additional information for the current ticket.

4. Click Submit Request.

Step 2: Precheck the SQL

After you submit the ticket, the system automatically performs a precheck. If the precheck fails, modify the SQL statements based on the prompt and try again.

After the precheck passes, the status of each check item (SQL Splitting, Type Check, Permission Check, SQL Review, and Check Scanned Rows) is Passed. The Affected Rows are also displayed. The actual number of affected rows depends on the SQL execution behavior.

Step 3: Approve the ticket

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

The Notice dialog box displays validation information, such as the number of physical databases, the number of SQL statements, and the estimated number of affected rows. If the number of affected rows verified by the system is different from the number you entered, a red warning message appears. Confirm the information before you submit the ticket.

Step 4: Execute the change

1. After the ticket is approved, click Execute Change. In the Task Settings dialog box, set the task execution parameters, and click Execute.

   Note

   • When you create the ticket, if you set Execution Method to Execute automatically after approval, the system skips this step.

   • If you restart a paused task, the script resumes execution from where it was paused.

   Configuration item	Description
   Execution Policy	Select an execution policy: Execute Immediately: The task starts immediately after you click Execute. Scheduled Execution: Specify a start time for the task. For example, run the task at 00:00:00 on May 22, 2024. Note The actual execution time of a scheduled task may have a margin of error of ±1 minute.
   Specify End Time	Specify an end time for the task. If the task is not complete by the specified end time, the system stops running the remaining SQL tasks. This prevents tasks from running during peak hours and affecting business operations. Note The actual end time of the task may have a margin of error of ±1 minute.
   Enable Global Transaction	Select whether to enable global transactions. This feature is disabled by default. Enable: If the execution fails, all changes are rolled back. This applies only to DML statements, not DDL statements. Disable: SQL tasks are submitted one by one. If a task fails, the execution stops, but no changes are rolled back.
   Enable Backup	Select whether to enable backups. This feature is enabled by default. If you enable this feature, you can use the backup file to quickly restore data later. Note Data backup is supported only when you execute UPDATE and DELETE statements. Data backup is not supported for MongoDB and Redis. Enable: Before executing UPDATE or DELETE statements, the system automatically generates a backup script attachment. If the database is MySQL or MariaDB, a REPLACE INTO backup statement is generated. Note MySQL includes: RDS MySQL, PolarDB for MySQL, PolarDB-X, and other MySQL sources. If the database is an engine other than MySQL or MariaDB, an INSERT backup statement is generated. Disable: No backup attachment is generated.
   Enable Primary/Standby Check	After you enable the primary/standby check for the database, you can ensure real-time data synchronization between the primary and standby instances. This improves database high availability and fast disaster recovery.
   Grayscale Type	The policy for executing SQL statements in batches. No Grayscale: DMS automatically executes all SQL statements in the task. Pause after the first SQL statement succeeds: After the first SQL statement is successfully executed, DMS automatically pauses the execution. To continue, click Retry. The remaining SQL statements will be executed at once without any further pauses. Pause after each SQL statement succeeds: The execution pauses after each SQL statement. You must manually click Retry to execute the next SQL statement.

   Note

   The execution of SQL tasks is monitored by the Security Rules module, specifically the SQL Execution Control section. This includes mechanisms such as database lock timeout before SQL execution, database load checks, and sleep policies after SQL execution. To modify the default detection point settings, see Configure SQL execution control.

   • After the ticket is successfully executed, in the Actions column, click Details to view details such as the execution status, number of executions, number of affected rows, execution script, and logs.

   • After the ticket is successfully executed, you can navigate to the SQL window of the target database to check whether the data change meets your expectations.

2. Optional: Create a rollback ticket to quickly restore the original data.

   If the data change does not meet your expectations, you can create a rollback ticket or manually submit a standard data change ticket to quickly restore the original data. The following steps show how to create a rollback ticket:

   1. In the Execution area of the ticket, click Generate Rollback Ticket to the right of the target task.

   2. Select a rollback Source and click OK. The following three rollback sources are supported:

      • Rollback text: The rollback text that you entered when you configured the ticket.

      • Rollback attachment: The rollback attachment that you uploaded when you configured the ticket.

      • Backup file: The backup attachment generated during ticket execution.

        Note

        To restore original data using a backup file, you must enable backups before executing the change.

   3. (Optional): You can modify the rollback text, attachment, or backup file as needed.

      • Rollback text: You can modify it directly in the SQL text box.

      • Rollback attachment or backup file: You can download the attachment to your local computer, modify it, and then re-upload it to the rollback ticket.

   4. After you confirm that the rollback information is correct, click Submit Request. The subsequent ticket process is the same as that for a standard data change ticket.

3. Download the backup file.

   You can download the backup file to save it or modify it before you upload it to DMS.

   1. In the Actions column, click Download Backup. The backup file contains the following main parts:

      • The original SQL statement for the change.

      • The query SQL statement for the data change.

      • The data change backup SQL.

      For 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');
                                          

   2. Extract the backup statement from the backup file. After you confirm that the backup statement is correct, submit a new standard data change ticket to restore the data as needed.

      Note

      If an UPDATE statement was executed by mistake and the backup statement is an INSERT statement, you must determine the recovery method yourself.

4. (Optional) Verify that the change meets expectations.

   1. On the ticket details page, in the Basic Information area, move the mouse pointer over the database name and click Query.

   2. You are redirected to the SQL Console. Query the table data to check whether it meets your expectations.