Handle DDL exceptions

Description

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 table data consistency and correctness. In special cases, you must manually handle DDL exceptions.

Procedure

The following sections provide an example on how to handle DDL exceptions. A statement that fails to create a unique global secondary index is used in this example. 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.

   mysql> 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 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 primary key column is not unique.

   mysql> 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'
   
   
   mysql> 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 resolved, you can execute the CONTINUE DDL statement to resume the DDL task.
   mysql> 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.
   mysql> cancel ddl 1359992239576580096;
   Query OK, 1 row affected (0.03 sec)

4. Check table data consistency and correctness
   After the DDL statement for creating a unique global secondary index is executed as expected, we recommend that you execute the following statements to check table data consistency and the correctness of your table schema:

   • 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 the error message indicates that table data consistency is not achieved, contact Alibaba Cloud technical support.
   • The SHOW CREATE TABLE statement: checks the correctness of your table schema.

   mysql> check table t1;
   +-------+-------+----------+----------+
   | TABLE | OP    | MSG_TYPE | MSG_TEXT |
   +-------+-------+----------+----------+
   | d1.t1 | check | status   | OK       |
   +-------+-------+----------+----------+
   1 row in set (0.05 sec)
   
   mysql> show create 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)