Data Management:Data change

Error message:

SQL type check failed. Based on security rule settings, direct execution of command type 'XXXX' is prohibited. To proceed, contact a DBA or administrator to adjust the rules. You can find the rules at: Security and Disaster Recovery -> Security Rules -> Edit -> SQL Correct -> SQL Execution Rules. No rule was found for this command type, so execution is denied by default.

Solution: SQL commands that are blocked can be categorized as KNOWN (commands that DMS recognizes) and UNKNOWN (commands that DMS does not recognize). Choose a solution based on the command type.

• KNOWN

  DMS recognizes this command, but the current security rule denies its execution by default. Contact your Database Administrator (DBA) or administrator to adjust the security rule. Follow these steps to adjust the rule:

  1. Find the name of the security rule set.

     Go to the Data Change Ticket Details page and click Operation Logs in the upper-right corner. In the Operation Logs dialog box, check the Action Details column to find the name of the associated security rule set, for example, POC Production Rules.

  2. Adjust the security rule.

     1. Log on to DMS.

     2. In the top menu bar, choose security and disaster recovery (DBS) > security rules.

     3. On the security rules tab, find the target rule set and click Edit in the Actions column.

     4. In the left-side navigation pane, click SQL Correct.

     5. Select a Check Point based on the error message. For example, in the preceding error example, set Check Point to SQL Execution Rules.

     6. Select the security rule to adjust and click Edit in the Actions column.

        Note

        Select the security rule based on the SQL language type of the command in the error message. For example, if the command type is CREATE_INDEX, you must select a DDL-related security rule.

     7. Add the SQL type from the error message to the corresponding security rule's DSL.

        For example, if the error indicates that the CREATE_INDEX command type is prohibited, you need to add this command type to the relevant rule for DDL ticket submission.

        In the Edit Rule dialog box, set Checkpoints to SQL Execution Rules and find the rule named DDL must be submitted through a ticket. In the rule's DSL, verify that the @fac.sql_type list includes DDL types such as CREATE_INDEX and that the corresponding action is @act.allow_submit, which allows the SQL type to be executed through a ticket. Then, click Submit.

     8. Click Submit.

  3. After you save the changes, return to the ticket details page. In the precheck section, click Retry. DMS then runs the type check again.

• UNKNOWN

  1. Check the submitted SQL statement for errors. If there are errors, click Modify SQL in the precheck section. After you correct the statement, click Retry to run the check again.

  2. If the SQL statement is correct, click View Details in the precheck section to identify the statement marked as UNKNOWN. Contact your DBA or administrator to add the statement to a security rule that permits its execution in DMS. After the rule is updated, click Retry. For instructions on how to adjust the rule, see Adjust the security rule.

Error message:

Failed to check the number of affected rows. Syntax check failed. Error message: ERROR. pos 37, line 1, column 31, token WHERE

Solution: Check the syntax of the submitted SQL statement.

• If the SQL syntax is incorrect, modify the statement and then click Retry in the precheck section. DMS will then re-check the number of scanned rows.

• If you are certain that the SQL syntax is correct, you can click Skip next to Check the number of scanned rows in the precheck section to bypass this error.