The new data definition language (DDL) execution engine of Distributed Relational Database Service (DRDS) introduces the job management feature. As a result, external behavior is different from that in earlier versions. This topic describes the typical scenarios.
If a DDL statement is executed, you do not need to know the status of the DDL statement. The DDL statement that has been executed is automatically deleted.
After a DDL statement is executed, we recommend that you execute
CHECK TABLEimmediately to check the consistency of the logical table.
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 failure cause of the pending DDL job. The failure cause is indicated by the REMARK field. You must eliminate the factors that cause the DDL execution failure and then execute a DDL job management statement to resume, roll back, or delete the DDL job. Otherwise, the failure may persist.
If a DDL statement fails to be executed and the corresponding DDL job is in the Pending state, the target table become inaccessible for protection purposes. For example, no responses are displayed after SHOW TABLES is executed, and errors such as the table is unknown or the table does not exist occur after operations such as data manipulation language (DML) statements are executed. The target table recovers only after the pending DDL job is resumed or rolled back to make the target table enters the Consistent state.
When you specify IF NOT EXISTS for CREATE TABLE or specify IF EXISTS for DROP TABLE, certain errors that occur during execution will not cause the DDL statement to fail to be executed. However, the errors will be recorded in warnings. Check the number of returned warnings after the DDL statement is executed. In addition, execute the SHOW WARNINGS statement to check whether any warnings exist. In this way, you can avoid missing important information.
When you need to execute a DDL statement on a client such as Data Management (DMS), if the execution time required for the DDL statement cannot be evaluated and timeout-based interruption for the connection between the client and the DRDS instance is enabled on the client, you can enable the asynchronous mode by setting
PURE_ASYNC_DDL_MODEto TRUE to prevent the DDL statement from being interrupted due to timeout. For how to use the asynchronous mode, see the topic about the PURE_ASYNC_DDL_MODE parameter. In this way, the client returns immediately after the DDL statement is executed. You can view the status of the DDL job by executing the SHOW DDL statement.
After you resume, roll back, or delete the pending DDL job by executing a DDL job management statement, we recommend that you execute
CHECK TABLEto check the consistency of the logical table.
Limits on DDL job management:
Only the CREATE TABLE and RENAME TABLE DDL operations can be rolled back by executing the ROLLBACK DDL statement.
The RECOVER DDL and ROLLBACK DDL statements cannot be combined or repeated for a pending DDL job. For example, you cannot execute RECOVER DDL to roll back a failed job and then execute RECOVER DDL to recover the job after rollback fails. Such combined operations may cause inconsistency in the logical table. If you encounter similar complex scenarios, contact DRDS Customer Services.
Use REMOVE DDL with caution and only when security is ensured. If you cannot ensure the security, we recommend that you do not execute REMOVE DDL. Misuse of REMOVE DDL may expose the intermediate state of DDL jobs and lead to inconsistency in the logical table. If problems occur or the table is not secure due to misuse of REMOVE DDL, contact DRDS Customer Services.
By default, a maximum of 128 table shards can be created for a single physical database. You can adjust the upper limit by using certain parameters. The following sample code shows these parameters.
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][18.104.22.168: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 jobs can be accumulated in the job queue of the DDL execution engine. After this upper limit is reached, you cannot execute any DDL statement. You can execute REMOVE DDL with caution to clear unwanted pending jobs. This limit cannot be adjusted by any parameter.