The new DDL execution engine introduces the task management feature. As a result, statement behavior is different from that in earlier versions. This topic describes the considerations and limits of the task management feature.

Considerations

  • If the DDL statement of a DDL task is executed, you can ignore the status of the DDL task. The DDL tasks that are executed are automatically deleted.
  • After the DDL statement of a DDL task is executed, we recommend that you immediately execute the CHECK TABLE statement to check the consistency of the logical tables that correspond to the DDL task.
  • After you execute a DDL task management statement to resume, roll back, or delete a DDL task, we recommend that you execute the CHECK TABLE statement to check the consistency of the logical tables that correspond to the DDL task.
  • If a DDL statement fails to be executed, an error code and an error message are returned. You can also execute the SHOW DDL statement to view the cause of the failure in the pending DDL task. The cause of the failure is indicated by the REMARK field.
    Notice We recommend that you identify the cause of the failure in a DDL task and how to handle the failure before you execute a DDL task management statement to resume, roll back, or delete the DDL task. Otherwise, the DDL task management statement may fail to be executed.
  • If a DDL statement fails to be executed and the corresponding DDL task is in the pending state, the status of the table that you want to use becomes inaccessible for security considerations. Then, no responses are displayed after a statement such as SHOW TABLES is executed. An error may occur after you execute a DML statement or perform another operation. The error message may indicate that the table is unknown or does not exist. The table that you want to use can be accessed only after the pending DDL task is resumed or rolled back to make this table enter the consistent state.
  • If you use the IF NOT EXISTS clause in the CREATE TABLE statement or use the IF EXISTS clause in the DROP TABLE statement, some errors that occur during execution do not cause the execution of the DDL statement to fail. However, the errors are recorded in warnings. Check whether a message that indicates the number of warnings is returned after you execute the DDL statement. For example, the following message may be returned: 1 warning. Execute the SHOW WARNINGS statement to check the warnings. This way, you can avoid missing important information.
  • On a client such as the Data Management (DMS) client,you can configure PURE_ASYNC_DDL_MODE to execute DDL statements in asynchronous mode. This prevents the DDL statement execution from being interrupted due to timeout. This configuration is suitable for scenarios in which the time required to execute a DDL statement cannot be estimated and the timeout-based interruption for the connection between the client and the DRDS instance is enabled on the client. After the DDL statement is executed, you can execute the SHOW DDL statement on the client to view the status of the corresponding DDL task.

Limits

  • Only the CREATE TABLE and RENAME TABLE operations can be rolled back.
  • The RECOVER DDL and ROLLBACK DDL statements cannot be combined or repeated for a pending DDL task. For example, you cannot execute ROLLBACK DDL to roll back a failed task and then execute RECOVER DDL to recover the task after the rollback fails. Such combinations of operations may cause inconsistency in the logical tables. If operation combinations are required to meet your business needs, .
  • Execute the REMOVE DDL statement only when the database security is ensured. If you execute the REMOVE DDL statement when the database security is not ensured, the intermediate states of DDL tasks may be revealed and the logical tables may be inconsistent. If problems occur or the security of the table data is compromised due to the misuse of REMOVE DDL, .
  • By default, a maximum of 128 table shards can be created for a single physical database. You can change the limit by using the parameters shown in the following sample code.
    mysql> create table test_mdb_mtb (c1 int not null auto_increment primary key, c2 varchar(10), c3 date) dbpartition by hash(c1) tbpartition by hash(c1) tbpartitions 129;
    ERROR 4647 (HY000): [f5bd90594800000][30.25.86.55:8527][JICHEN_LOCAL_APP]ERR-CODE: [TDDL-4647][ERR_TABLE_PARTITIONS_EXCEED_LIMIT] The number of table partitions '129' exceeds the upper limit '128'. Please specify less table partitions or adjust the value of the parameter MAX_TABLE_PARTITIONS_PER_DB.
    mysql> /*+TDDL:cmd_extra(MAX_TABLE_PARTITIONS_PER_DB=400)*/create table test_mdb_mtb (c1 int not null auto_increment primary key, c2 varchar(10), c3 date) dbpartition by hash(c1) tbpartition by hash(c1) tbpartitions 129;
    Query OK, 0 rows affected (2.64 sec)
  • A maximum of 65,535 pending DDL tasks can be accumulated in the task queue of the DDL execution engine. If the number of pending DDL tasks exceeds this limit, you cannot execute DDL statements. In this case, you must execute REMOVE DDL to remove unwanted pending tasks. This limit cannot be changed by modifying parameters.