Data change - Data| Alibaba Cloud Documentation Center

Background

During deployment of a project, database need to be updated for data initialization, historical data cleaning, bug fix and testing
- Operatons includs but not limited to insert, update, delete, truncate etc.

1. On the menu of navigation bar on the top of product page: [Data changes], can create new ticket
2. Ticket Submission

Input	Input method	Value	Comment
Reason	Drop-down		Administrator and DBA can use [System management] - [Configuration management] to config accordingly
Business background	Manual input		Description of the reason of this change, the goal of the implementation, etc.
Relevant personnel	Drop-down		Relevant personnel can view the work order and cooperate the ticket; non-related people(except for system administrator and DBA) cannot view the work order
Operation method	Drop-down		Default “Upon approval, the submitter executes”，optional choice “Upon approval, automatic execution” and “Laster apporver executes”
Database	Drop-down	Database with “change” privilege	Cannot submit if only have “query” privilege or onlhy have “update” privilege of table
Number of records affected	Manual input		Estimation of the number of records affected by this operation, may use “count” method through SQL Console
SQL - Operation	Manual input or Attachment		Executable SQL statement(s). SQL syntax will be automatically checked upon submission, and rejcted if fails. It is user’s resposibility to ensure the correctness of business logic
SQL - Rollback	Manual input or Attachment		Executable SQL statement(s) for rollback

3. Submission Confirmation
- Focus on the difference between the actual number of rows affected and the expected number of rows affected to avoid mistakes that cause the update to not meet expectations

check

4. Ticket Approval
- The instance and operation of this case do not require approval, because the execution method is “Upon approval, the submitter executes”. Hence the execution is carried out by submitter “Change execution”, the screenshot is from submitter’s perspective
  - If ticket requires approval and is rejected, then the ticket is closed；
  - If the script needs to be adjusted, one can withdraw the changes before committing and re-submit.

execute

Note:[System management] -[Safety policy]Supports flexible configuration of policy and approval procedure based on enterprise instance level management and control needs

rules

5. Ticket execution
- Execution method: for ticket of “Upon approval, the submitter executes”, submitter will tirgger “change opereation”

Control	Value	Comment
Execute immediately	Yes / No（Date/Time required）	Default “Yes”, execution upon submission；”No” Execution at specific time according to business requirement
Control by transaction	Yes / No	Default “No”, submit one by one, terminate if fails but no rollbackup; “Yes”, terminate and rollback upon failure(for DML only, not DDL)
Data backup	Yes / No	Default “Yes”, to backup ,general Insert script for data operations such as update and delete

check2

6. Confirmation of execution result
- Upon completion of execution, execution log can be found in “Execution detail”, including information such as number of rows affected and execution time

[Note] In the case of the result not meet expectation，“Check Backup ” provide following backup files, including:
- 1.Origianl SQL of the operation
- 2.Inquiry SQL for backup logic
- 3.Insert SQL for backup data[for restore]

1）For repeatable operation，the history ticket can be found at the upper right corner - Create Sub-ticket to quick clone the ticket
2）For abnormaly and failure during execution and rollback is necessary, on the right side of ticket page - Ticket operation history - Check backup datadownload script, and submit another ticket
3）In case of requirement changes, ticket can be withdrawn regardless approval or not, to avoid operation mistake.
4）Ticket approval procedure is setup in Administrator and DBA System Management - Instance Management - Safety Policy . It is recommended that the daily test environment also leverages the ticket management system (and may config as no approval required to development efficency). So that all changes will have safe guard for backup, verification check and restore
5）For the scenario of sharding over databases and tables, by configing logic database, logic table、routing algorithm, one ticket can conveniently operate all shards instead of every physical tables
- Routing algorithm configuration and change condition of columns/tables can quickly route operations to target phyiscal databases and tables
- If the algorithm doesn’t contain correct column/structure/datatype, operational SQL may need to scan all databases and tables, which will be inefficient and much longer execution duration [not recommended]
  - Unless in the rare situation, that operation applies to all data