This topic describes common methods that you can use to 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.
- 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)
- 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'
- 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 theSHOW 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)
- To manually resume the DDL task, execute the
- Check table data consistency and correctnessAfter 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)
- The