An SQL review optimization engine is integrated into the security rules of Data Management (DMS). After you submit SQL statements for data change or schema design, DMS can review the submitted SQL statements and offer optimization suggestions.

Prerequisites

The migration_job table is created. You can use the following SQL statement to create the table:
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';

Configure security rules

Security rules contain default SQL review rules. For example, the table must contain remarks, a NULL value cannot be inserted into a NOT NULL column in the INSERT statement, and the names of fields in the INSERT statement cannot be duplicated. You can customize security rules based on your business requirements. For more information, see SQL review optimization engine.

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose System > Security Rules. The Security Rules tab appears.
  3. Find the security rule set that you want to modify and click Edit in the Actions column.
    SQL review optimization: Edit a security rule
  4. In the left-side pane of the Details tab, click the SQL audit optimization recommendations tab.
  5. Find the Table must have primary key rule and click Edit in the Actions column.
    Edit an SQL review rule
    Note You can click the Filter icon icon to the right of Tag, Behavioral action, or Status to filter rules. The Tag parameter specifies the effective range of a rule. A rule can apply to data definition language (DDL) statements and data manipulation language (DML) statements that are used to insert, delete, update, or query data. The Status parameter specifies whether a rule is enabled or disabled.
  6. In the Rule content configuration dialog box, set the parameters as required. The following table describes the parameters.
    Rule content configuration
    Parameter Description
    Behavioral action In this example, set the Behavioral action parameter to Must Improve.
    Note Default SQL review rules in the system do not contain Must Improve. For more information about the Behavioral action parameter, see Behavioral actions.
    Remarks Enter the description of the behavioral action for this rule, such as the background information.
  7. Click OK.

Change data

  1. Log on to the DMS console.
  2. In the top navigation bar, choose Data Plans > Data Changes > Normal Data Modify.
  3. On the Data Change Ticket Application page, set the parameters as required and click Submit.
    Note For more information about the parameters, see Common data change.
    • Select the database instance that is associated with the security rule set that you want to use.
    • Execute the following SQL statements for data change:
      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());
    Submit a data change ticket
  4. After you submit the ticket, wait until the system verifies that your configurations are valid.
    The SQL review optimization engine reviews the submitted statements and returns suggestions. In this example, the following suggestions are returned: One item must be improved, two potential issues exist, and one item can be improved.
  5. Click View Details. Move the pointer over the items in the SQL Review column to view details.
    Item 1
  6. Click SQL Statements for Modification to optimize the SQL statements based on the suggestions. Then, click Confirm Change.
    You can modify the statements by performing the following operations:
    • In the CREATE TABLE statement, add a primary key and remarks for the test1 table.
    • In the INSERT statement, remove the duplicate field named gmt_create and insert values for the ref_id field.
    The following SQL statements are obtained after modification:
    create table test1 ( 
        id bigint primary key comment 'id', 
        name varchar(60) comment 'name'
    ) default charset = utf8mb4 collate utf8mb4_bin engine = innodb comment = 'Remarks';
    
    insert into migration_job(id, ref_id, gmt_create) 
    values(1, 2, now());
  7. The system reviews the SQL statements again.
    The statements pass the review.The statements pass the review
  8. Click Submit for Approval and wait for approval.
    After approval, a task is generated.
  9. Click Execute Change. In the Task Settings dialog box, set the control items as described in the following table. Then, click Confirm Execution.
    Control item Control value Description
    Execute Immediately on or off (If you set this item to off, you must specify a time for the task to be run.) Valid values:
    • on: The task is immediately run after the task is submitted.
    • off: The task is run at the specified time.
    Default value: on.
    Transaction Control on or off Valid values:
    • on: If an SQL statement fails to be executed, the statements that are executed are rolled back. Note that only data manipulation language (DML) statements can be rolled back. Data definition language (DDL) statements cannot be rolled back.
    • off: The SQL statements are executed one by one. If an SQL statement fails to be executed, the task execution is stopped. The statements that are executed are not rolled back.
    Default value: off.
    Data Backup on or off Valid values:
    • on: The backup file to be created contains the update and delete operations that are performed during this data change. The backup file also contains the `SELECT` clause that represents the affected rows and the INSERT clause that is used for rollback.
    • off: No backup file is created.
    Default value: on.
    Note If you set the Execution Method parameter to After Audit Approved, Auto Execute in Step 3, this step is automatically skipped.

What to do next

After the task is run, you can click Details to view operations logs that contain detailed information such as SQL statements, the execution duration, and scheduling details.