You can use the Normal Data Modify feature of Data Management (DMS) to change data in databases by executing SQL statements, such as INSERT, UPDATE, DELETE, TRUNCATE, and CREATE TABLE. This feature allows you to initialize data, delete historical data, fix issues, or run tests. This topic describes how to perform regular data changein DMS.
Prerequisites
The database instance you use is managed in the Stable Change or Security Collaboration mode in DMS. For more information, see Control modes.
Usage notes
After you submit a data change ticket for approval, you can close the ticket even when the ticket has not been approved or is rejected. This prevents the task for the ticket from being accidentally executed after the ticket is approved.
We recommend that you submit tickets for data changes in the test environment. This way, the system checks the number of rows to be affected and generates a backup file for each data change. This allows you to restore data if the data is not changed as expected.
NoteTo ensure high R&D efficiency, you can specify that no approval is required for data change tickets in the test environment. For more information, see SQL Correct.
If you have configured logical databases, logical tables, and routing algorithms in DMS, you can submit a single ticket to shard databases and tables. This way, you do not need to submit a ticket for each physical database or table.
If you have configured a routing algorithm, and a routing field is included in the SQL statement that is used to shard databases and tables, the routing algorithm routes the statement to the corresponding physical table for execution.
If you have not configured a routing algorithm, no routing field is included in the SQL statement, or the data type of the routing field does not match the data type specified in the routing algorithm, the SQL statement is executed on each table in each database individually. This process takes a longer time.
Procedure
- Log on to the DMS console V5.0.
In the top navigation bar, choose .
NoteIf you use the DMS console in simple mode, move the pointer over the
icon in the upper-left corner of the DMS console and choose .
In the Apply step of the Ticket Application wizard, set the parameters for submitting a data change ticket. The following table describes some parameters that are displayed.
NoteIn this example, an ApsaraDB RDS for MySQL database in the Security Collaboration mode is used.
Parameter
Required
Description
Database
Yes
The database for which you want to submit the ticket. You must select a database on which you have the change permission.
Reason Category
Yes
The reason for the data change. This helps you find the ticket in subsequent operations.
NoteIf you are a DMS administrator, you can modify reason categories on the Configuration Management page of the Configuration management.
module. For more information, seeBusiness Background
Yes
The purposes or objectives of the data change for approvers' information.
Execution Method
Yes
The execution method of the ticket. Valid values:
After Audit Approved, Order Submitter Execute
After Audit Approved, Auto Execute
Last Auditor Execute
NoteIf you are a DMS administrator, you can modify execution methods on the Configuration Management page of the Configuration management.
module. For more information, seeAffected Rows
Yes
The estimated number of data rows that may be affected by the data change.
SQL Statements for Change
Yes
The SQL statements that are used for the data change. You can select Text or Attachment.
SQL Text
Yes
This parameter is displayed only if you specify Text for SQL Statements for Change. Enter SQL statements in the SQL Text field.
NoteSeparate multiple SQL statements with semicolons (;).
DMS checks whether the syntax of the SQL statements is valid when you submit the ticket. If the syntax is invalid, you cannot submit the ticket.
Attachment
Yes
This parameter is displayed only if you specify Attachment for SQL Statements for Change. Upload the file that stores the SQL statements used for data change.
NoteThe file to be uploaded can be a TXT, ZIP, or SQL file. The size of the file cannot exceed 15 MB.
SQL Statements for Rollback
No
The SQL statements that are used for rollback. You can select Text or Attachment.
SQL Text
No
This parameter is displayed only if you specify Text for SQL Statements for Rollback. Enter the SQL statements for rollback. The SQL statements that you specify for this parameter are used to roll back the data change.
Attachment
No
This parameter is displayed only if you specify Attachment for SQL Statements for Rollback. Click Upload a file to upload the file that contains the SQL statements you want to use for rollback.
NoteThe file to be uploaded can be a TXT, ZIP, or SQL file. The size of the file cannot exceed 15 MB.
Change Stakeholder
No
The stakeholders involved in the data change. All the specified stakeholders can view the ticket details and participate in the approval process. Irrelevant users, except for DMS administrators and database administrators (DBAs), do not have access to the ticket details.
Attachments
No
The file used as an attachment of the ticket to provide additional information about the data change.
Click Submit.
After you submit a ticket, the system automatically performs a precheck on the ticket. If the ticket fails to pass the precheck, modify the SQL statements as prompted and submit the ticket again.
After the ticket passes the precheck, click Submit for Approval. In the Prompt message, click OK.
NoteBy default, data change tickets are approved by DBAs. For more information about how to change the default approval template, see the "Change the default approval template" section of the SQL Correct topic.
After the application is approved, click Execute Change. In the Task Settings dialog box, set the task execution parameters and click OK.
NoteIf you set the Execution Method parameter to After Audit Approved, Auto Execute in the Apply step, this step is automatically skipped.
After a suspended task is resumed, the script can be executed from the position at which the script was suspended.
Parameter
Description
Execution Strategy
Specifies whether to run the task for the ticket immediately or at a scheduled time. Valid values:
Running immediately: If you select this option and click Confirm Execution, DMS immediately runs the task.
Schedule: If you select this option, you need to specify a time. After you click Confirm Execution, DMS runs the task at the scheduled time.
Enable Submit as Single Transaction
Specifies whether to control the statements as a single transaction. Default value: off. Valid values:
on: If an SQL statement fails, all the executed data manipulation language (DML) statements in the same transaction are rolled back. DDL statements cannot be rolled back.
off: The specified SQL statements are executed individually. If an SQL statement fails, the transaction is stopped. The other executed SQL statements in the same transaction are not rolled back.
Enable Backup
Specifies whether to back up data. Default value: on. Valid values:
NoteData backup is supported for only UPDATE and DELETE statements.
You cannot back up data for MongoDB or Redis databases.
on: DMS generates specific statements to back up the data that is affected when
UPDATE
orDELETE
statements are executed.If the database is a MySQL or MariaDB database, DMS generates a
REPLACE INTO
statement.NoteThe supported MySQL database types include ApsaraDB RDS for MySQL, PolarDB for MySQL, PolarDB-X, and MySQL databases that are not on Alibaba Cloud.
If the database is not a MySQL or MariaDB database, DMS generates an
INSERT
statement.
off: DMS does not generate statements for data backup.
NoteThe execution of SQL tasks is monitored by the checkpoints that are configured in security rules to control the SQL execution. Examples of the checkpoints include Database lock timeout mechanism before SQL execution, Database load check before SQL execution, and sleep policy after SQL execution. To check the checkpoints that are configured in a security rule to control SQL execution, go to the details page of the security rule, and click SQL execution control in the left-side pane. For more information about how to modify the default checkpoint settings, see Configure the control on SQL execution.
After the task is complete, you can click Details in the Actions column of the ticket to view the status of the ticket, number of times the task is run, number of affected rows, executed statements, and logs.
After the task is complete, you can go to the SQL console of the database to check whether the data is changed as expected.
Optional: If data changes do not meet expectations and data backup is enabled when you set the task execution parameters, perform the following steps to restore the source data.
In the Execute step of the Ticket Details wizard for the ticket, click Download Backup in the Actions column to download the backup file.
A backup file contains the following information:
The original SQL statements executed to change data
The SELECT clause in the original SQL statements
The SQL statement that is used for data backup
Examples:
/* [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 SQL statement for data backup from the backup file. After you confirm that the statement is valid, submit a regular data change ticket to restore the database data based on the actual situation.
NoteIf an improper
UPDATE
statement is executed for the data change and the system generates anINSERT
statement for data backup, you must manually restore the data.