Handle DDL exceptions

This topic describes common methods that you can use to handle DDL exceptions.

Overview

PolarDB-X is a distributed database service. A DDL statement that is executed in PolarDB-X involves sophisticated data processing. For example, if you create a sharded table, multiple physical MySQL tables are created in multiple data nodes. The DDL processing framework of PolarDB-X provides fault tolerance capabilities to ensure that data in the table is consistent and the table scheme is valid. In special cases, you must manually handle DDL exceptions.

Procedure

This topic uses a statement that fails to create a unique secondary index as an example to show how to handle DDL exceptions. When you use a DDL statement to create a unique global secondary index, the system checks whether data in the columns on which the unique global secondary index is created is unique. If the data is not unique, the DDL statement fails to be executed. In this case, the state of the DDL statement changes to PAUSED or ROLLBACK_PAUSED.

1. View the state of your DDL statement

   Execute the SHOW DDL statement to view the state of your DDL statement. You must manually handle a DDL exception only if the DDL statement is in the PAUSED state or ROLLBACK_PAUSED state.

   SHOW DDL\G;
   *************************** 1. row ***************************
              JOB_ID: 1359992239576580096
       OBJECT_SCHEMA: d1
         OBJECT_NAME: t1
              ENGINE: DAG
            DDL_TYPE: ALTER_TABLE
               STATE: PAUSED
   BACKFILL_PROGRESS: 0%
    PHY_DDL_PROGRESS: 100%
            PROGRESS: 80%
          START_TIME: 2021-08-05 13:57:57.852
            END_TIME: 2021-08-05 13:58:30.804
    ELAPSED_TIME(MS): 32952
         PHY_PROCESS: 
          CANCELABLE: true
   1 row in set (0.03 sec)
   

1. Identify the cause of the DDL exception

   In most cases, if a DDL exception occurs, an error message is returned. In specific scenarios, the error message returned for a DDL statement may be hard to obtain. One of the scenarios is that the DDL statement is executed in an asynchronous manner. In this scenario, you can execute the SHOW DDL RESULT statement to query the DDL statements that were recently executed. In the following example, an error message is returned because the data in the UNIQUE KEY column is not unique.

   ``plaintext ALTER TABLE t1 ADD unique global INDEXidx_c2(c2) DBPARTITION BY HASH(c2`) tbpartition by hash(c2) tbpartitions 3; ERROR 3009 (HY000): [12dfa9bc5c800000][30.39.226.95:8527][d1]Failed to execute the DDL task. Caused by: ERR-CODE: [TDDL-5321][ERR_GLOBAL_SECONDARY_INDEX_BACKFILL_DUPLICATE_ENTRY] Duplicated entry '100018' for key 'PRIMARY'

SHOW ddl result\G; * 1. row * JOB_ID: 1359992239576580096 SCHEMA_NAME: d1 OBJECT_NAME: t1 DDL_TYPE: ALTER_TABLE RESULT_TYPE: ERROR RESULT_CONTENT: Failed to execute the DDL task. Caused by: ERR-CODE: [TDDL-5321][ERR_GLOBAL_SECONDARY_INDEX_BACKFILL_DUPLICATE_ENTRY] Duplicated entry '100018' for key 'PRIMARY'

3. **Resume or cancel the DDL task**

   In most cases, DDL tasks can be automatically resumed or canceled if DDL exceptions occur. In special cases, you must manually handle DDL exceptions. Fault tolerance policies vary based on DDL statements. For example, the following fault tolerance policy takes effect on the `CREATE TABLE` statement: After a failure occurs, the CREATE TABLE task is automatically resumed. If multiple failures occur, the CREATE TABLE task is canceled. 
   You can manually resume or cancel the DDL task.
   * To manually resume the DDL task, execute the `CONTINUE DDL` statement. 

   * To manually cancel the DDL task, execute the `CANCEL DDL` statement. Specific DDL tasks cannot be canceled. You can execute the `SHOW DDL` statement to check whether your DDL task can be canceled by viewing the value of the CANCELABLE field. 




   ```plaintext
   -- After the DDL exception is handled, you can execute the CONTINUE DDL statement to resume the DDL task.
    CONTINUE ddl 1359992239576580096;
   Query OK, 1 row affected (1.63 sec)

   -- If you do not want to add the unique global secondary index, you can execute the CANCEL DDL statement to cancel the DDL task.
    CANCEL ddl 1359992239576580096;
   Query OK, 1 row affected (0.03 sec)

1. Check whether data in the table is consistent and whether the table scheme is valid After the DDL statement is executed as expected, we recommend that you execute the following statements to check whether data in the table is consistent and whether the table scheme is valid:

   • The CHECK TABLE statement checks table data consistency. If OK is returned, table data consistency is achieved. In this case, your table is available for use. If an error message is returned and it indicates that table data consistency is not achieved, contact Alibaba Cloud technical support.

   • The SHOW CHECK TABLE statement checks whether the table scheme is valid.

   CHECK TABLE t1;
   +-------+-------+----------+----------+
   | TABLE | OP    | MSG_TYPE | MSG_TEXT |
   +-------+-------+----------+----------+
   | d1.t1 | check | status   | OK       |
   +-------+-------+----------+----------+
   1 row in set (0.05 sec)

   SHOW CHECK TABLE t1\G;
   *************************** 1. row ***************************
          Table: t1
   Create Table: CREATE TABLE `t1` (
       `c1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
       `c2` bigint(20) DEFAULT NULL,
       `c3` bigint(20) DEFAULT NULL,
       PRIMARY KEY USING BTREE (`c1`),
       UNIQUE GLOBAL KEY `idx_c2` (`c2`) COVERING (`c1`) DBPARTITION BY HASH(`c2`) TBPARTITION BY HASH(`c2`) TBPARTITIONS 3
   ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`c1`) tbpartition by hash(`c1`) tbpartitions 3
   1 row in set (0.02 sec)