SQL review optimization in data change tickets

Background

DMS includes default SQL review rules such as A table must have comments, An INSERT statement cannot insert a null value into a NOT NULL column, and Field names cannot be duplicated in an INSERT statement.

For an overview of available SQL compliance checks and optimization suggestions, see Configure SQL review optimization.

Prerequisites

Before you begin, create the migration_job table with the following statement:

CREATE TABLE `migration_job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `gmt_create` datetime NOT NULL COMMENT 'Creation time',
  `ref_id` bigint(20) unsigned NOT NULL COMMENT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Test';

Step 1: Configure security rules

The following steps configure SQL review optimization rules for the Security Collaboration mode. For Flexible Management or Stable Change modes, click SQL review optimization next to the corresponding control mode.

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Security and disaster recovery (DBS) > Security Rules.

   Note

   If you use the DMS console in normal mode, choose Security and disaster recovery (DBS) > Security Rules in the top navigation bar.

3. Find the target security rule set and click Edit in the Actions column.

4. In the left-side pane of the Details page, click the SQL audit optimization recommendations tab.

5. Find the A table must have a primary key rule and click Edit in the Actions column.

   Note

   Click the icon next to Tag, Behavioral action, or Status to filter rules. Tag specifies the rule scope (DDL or DML). Status can be enabled or disabled.

6. In the Rule content configuration dialog box, configure the parameters. For this example, set the behavioral action to Must Improve.

   Note

   The default system rules do not include Must Improve. For available behavioral action options, see System behavioral actions.

7. Click OK.

   If you use the features such as data development, data change, and SQL review, the SQL review optimization feature verifies SQL statements based on the configured security rules.

Step 2: Execute a data change

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 Application page, configure the parameters and click Submit Application.

   Note

   For parameter descriptions, see Normal Data Modify.

   • Select the database instance associated with the target security rule set.

   • The SQL code for the data change is as follows:

     CREATE TABLE test1 ( 
         id bigint COMMENT 'id', 
         name varchar(60) COMMENT 'name'
     ) DEFAULT CHARSET = utf8mb4 COLLATE utf8mb4_bin ENGINE = INNODB;
     
     INSERT INTO migration_job(id, ref_id, gmt_create, gmt_create) 
     VALUES(1, null, now(), now());

4. After you submit the ticket, the system checks the SQL statements against the security rules configured in Step 1.

   The audit finds one Must Improve item, two potential issues, and one Suggested Improvement item.

5. Click View Details. Hover over the SQL review error items to view specific suggestions.

6. Based on the suggestions, click Modify SQL, optimize the SQL, and then click Confirm Change.

   The modifications are as follows:

   • In the CREATE statement, add a primary key and a comment to the test1 table.

   • In the INSERT statement, remove the duplicate gmt_create field and insert a value for the ref_id field.

   The modified SQL code is as follows:

   CREATE TABLE test1 (
       id bigint PRIMARY KEY COMMENT 'id',
       name varchar(60) COMMENT  'name'
   ) DEFAULT  CHARSET = utf8mb4 COLLATE  utf8mb4_bin ENGINE = INNODB  COMMENT  = 'Comment';
   INSERT INTO migration_job(id, ref_id, gmt_create) VALUES(1, 2, now());

7. The system performs the precheck again.

8. Click Submit for Approval and wait for an administrator to approve the ticket.

   After approval, the system generates an execution task.

9. After the ticket is approved, click Execute Change. In the Task Settings dialog box, configure the execution parameters and click Confirm and Execute.

   Note

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

   • If a paused task is restarted, it resumes execution from where it was paused.

   Parameter	Description
   Execution strategy	Execution strategy options: Execute Immediately: Starts the task when you click Confirm and Execute. Scheduled Execution: Runs the task at a specified future time.
   Enable global transaction	Whether to wrap all statements in a global transaction. Disabled by default. Enabled: All changes roll back on failure. Applies to DML statements only, not DDL. Disabled: Statements execute sequentially. On failure, the task stops but previous changes are not rolled back.
   Enable backup	Whether to back up affected rows before execution. Enabled by default. Allows quick data restoration from the backup. Note Backup is supported only for UPDATE and DELETE statements. Backup is not supported for MongoDB and Redis. Enabled: Before executing UPDATE or DELETE statements, the system generates a backup script. For MySQL and MariaDB databases, a REPLACE INTO backup statement is generated. Note MySQL databases include RDS for MySQL, PolarDB for MySQL, PolarDB-X, and other MySQL-compatible sources. For database engines other than MySQL and MariaDB, an INSERT backup statement is generated. Disabled: No backup script is generated.

   Note

   The SQL execution control module in security rules monitors SQL task execution, including database lock timeout, database load, and post-execution sleep policies. To modify defaults, see Configure SQL execution control.

   • After successful execution, click Details in the Actions column to view execution status, number of executions, affected rows, scripts, and logs.

   • You can also connect to the database SQL console to verify the data changes.