To prevent data loss due to misoperations, PolarDB-X 1.0 prohibits high-risk SQL statements by default, such as a DELETE statement without a WHERE or LIMIT clause and an UPDATE statement without a WHERE or LIMIT clause. If you need to perform full-table deletion or update, you can skip the preceding protection by adding a hint to the corresponding statement.

Statements

You can add the following hint to an UPDATE or DELETE statement to perform full-table deletion or update: 

HINT: /! TDDL:FORBID_EXECUTE_DML_ALL=false*/

Examples

  • If a DELETE statement does not contain any WHERE or LIMIT clauses, the execution of this statement is intercepted and the following error message appears:
    mysql> delete from tt;
ERR-CODE: [TDDL-4620][ERR_FORBID_EXECUTE_DML_ALL] Forbid execute DELETE ALL or UPDATE ALL sql. More: [http://example.aliyundoc.com/faq/faqByFaqCode.html?faqCode=TDDL-4620]
    The operation is successful after the following hint is added to the statement:
    mysql> /! TDDL:FORBID_EXECUTE_DML_ALL=false*/delete from tt;
Query OK, 10 row affected (0.21 sec)
  • If an UPDATE statement does not contain any WHERE or LIMIT clauses, the execution of this statement is intercepted and the following error message appears:
    mysql> update tt set id = 1;
ERR-CODE: [TDDL-4620][ERR_FORBID_EXECUTE_DML_ALL] Forbid execute DELETE ALL or UPDATE ALL sql. More: [http://example.aliyundoc.com/faq/faqByFaqCode.html?faqCode=TDDL-4620]
    The operation is successful after the following hint is added to the statement:
    mysql> /! TDDL:FORBID_EXECUTE_DML_ALL=false*/update tt set id = 1;
Query OK, 10 row affected (0.21 sec)