Handle DDL exceptions

Overview

As a distributed database, PolarDB-X uses a complex workflow to execute DDL statements. For example, when you create a sharded table, PolarDB-X creates multiple physical MySQL tables on several data nodes. The PolarDB-X DDL framework has built-in fault tolerance to ensure DDL operations are correct and consistent. However, in some situations, you may need to handle DDL exceptions manually.

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 DDL execution state

   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)

2. Identify the cause of the error

   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.

   ALTER TABLE t1 ADD unique global INDEX `idx_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.

   -- 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)

4. Check table consistency and table schema
   After the DDL statement successfully completes, run the following statements to verify table consistency and the table schema:

   • Use the CHECK TABLE statement to check table consistency. If the result is OK, the table is consistent and ready for use. If an error indicates the table is inconsistent, contact Alibaba Cloud technical support.
   • Use the SHOW CHECK TABLE statement to check the table schema.

   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)