Job management statements are extended Structured Query Language (SQL) statements dedicated to PolarDB-X 1.0. They can be used to query the details of data definition language (DDL) jobs and resume or roll back failed DDL jobs. This topic describes the syntax and usage of job management statements.

Query a job

You can query the details of a DDL job in the DDL queue where the DDL job may be either in a non-PENDING state as it is being executed or in the PENDING state due to failures.
Note Jobs that have been executed are in the COMPLETED state and are automatically cleared. You cannot query these jobs by executing the SHOW DDL statement.
  • Syntax 
    SHOW [FULL] DDL
    Parameter Description
    FULL Queries all information of a DDL job. If you do not specify this parameter, only the following common information is displayed.
    • JOB_ID
    • OBJECT_SCHEMA
    • OBJECT_NAME
    • JOB_TYPE
    • PHASE
    • STATE
    • PROGRESS
    • START_TIME
    • END_TIME
    • ELAPSED_TIME
    • REMARK
    • PHY_PROCESS
    • BACKFILL_PROGRESS
  • Description of the fields in the result set
    Field Description
    JOB_ID The unique ID of the DDL job. It is a long 64-bit signed integer.
    PARENT_JOB_ID The unique ID of the DDL parent job. It is a long 64-bit signed integer.
    Note If no parent job exists, this field is set to 0.
    SERVER The information of the DRDS server node that executes the DDL job.
    OBJECT_SCHEMA The schema name of the object corresponding to the DDL job. For example, this field can be the name of the current database.
    OBJECT_NAME The name of the object corresponding to the DDL job. For example, this field can be the name of the table where the current DDL statement is executed.
    NEW_OBJECT_NAME The new name of the object corresponding to the DDL job.
    Note This field is valid only when you execute RENAME TABLE. It indicates the target table name.
    JOB_TYPE The type of the DDL job.
    PHASE The phase where the DDL job is located.
    STATE The status of the DDL job.
    PROGRESS The progress of the DDL job.
    START_TIME The time when the execution of the DDL job started.
    END_TIME The time when the execution of the DDL job ended.
    ELAPSED_TIME The time elapsed after the execution of the DDL job ended. Unit: milliseconds.
    DDL_STMT The original DDL statement.
    REMARK The remarks of the DDL job.
    Note This field displays the failure cause of the DDL job when the DDL job is in the PENDING state.
  • Example

    Create a logical table that is partitioned into table shards in a database shard. Query the details of the job when the job is being executed.

    1. Execute the CREATE TABLE DDL statement on a connection. 
      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 64;
    2. Query the details of the DDL job on another connection. 
      mysql> show full ddl\G
*************************** 1. row ***************************
    JOB_ID: 1103792075578957824
PARENT_JOB_ID: 0
    SERVER: 1:102:10.81.69.55
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_mdb_mtb
NEW_OBJECT_NAME:
  JOB_TYPE: CREATE_TABLE
     PHASE: EXECUTE
     STATE: RUNNING
  PROGRESS: 90%
START_TIME: 2019-08-29 14:29:58.787
  END_TIME: 2019-08-29 14:30:07.177
ELAPSED_TIME(MS): 8416
  DDL_STMT: 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 64
    REMARK:

Resume a job

You can resume a pending DDL job that is suspended due to failures.
Note Before you resume the job, execute the SHOW DDL statement to check the causes for the interruption or failure. Resume the job only after the failure causes are eliminated. Otherwise, the same problem persists when you attempt to resume the job.
  • Syntax 
    RECOVER DDL { ALL | <job_id> [ , <job_id> ] ... }
    Parameter Description
    ALL Resumes all DDL jobs that are in the PENDING state. Note that this parameter causes the pending DDL jobs to be executed serially. Use it with caution.
    job_id The ID of the pending DDL job. This ID is displayed in the execution result of the SHOW DDL statement.
  • Example

    Create a logical table that is partitioned into table shards in a database shard and interrupt the job during execution. Execute the SHOW DDL statement to query the status and job_id of the job. Then, execute the RECOVER DDL statement to resume the job until the table is created.

    1. Interrupt the CREATE TABLE DDL job during execution. 
      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 64;
^C^C -- query aborted
    2. Query the information about the DDL job. The interrupted DDL job is in the PENDING state. 
      mysql> show ddl\G
*************************** 1. row ***************************
     JOB_ID: 1103796219480006656
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_mdb_mtb
  JOB_TYPE: CREATE_TABLE
      PHASE: EXECUTE
      STATE: PENDING
  PROGRESS: 33%
START_TIME: 2019-08-29 14:46:26.769
  END_TIME: 2019-08-29 14:46:29.691
ELAPSED_TIME(MS): 2922
  DDL_STMT: 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 64
     REMARK: The job has been interrupted unexpectedly
    3. Execute the RECOVER DDL statement to resume the job. 
      mysql> recover ddl 1103796219480006656;
Query OK, 0 rows affected (7.28 sec)
    4. Execute CHECK TABLE to check the consistency of the table. 
      mysql> check table test_mdb_mtb;
+----------------------------------------+-------+----------+----------+
| TABLE                                  | OP    | MSG_TYPE | MSG_TEXT |
+----------------------------------------+-------+----------+----------+
| ddltest_1562056402230oymk.test_mdb_mtb | check | status   | OK       |
+----------------------------------------+-------+----------+----------+
1 row in set (2.24 sec)

Roll back a job

You can roll back a pending DDL job that is suspended due to failures.
Note You can only roll back CREATE TABLE and RENAME TABLE DDL jobs. For other DDL jobs that cannot be rolled back, we recommend that you resume the pending DDL jobs before you perform other DDL operations.
  • Syntax 
    ROLLBACK DDL <job_id> [ , <job_id> ] ...
    Parameter Description
    job_id The ID of the pending DDL job. This ID is displayed in the execution result of the SHOW DDL statement.
  • Example

    Create a logical table that is partitioned into table shards in a database shard and interrupt the job during execution. Execute the SHOW DDL statement to query the status and job_id of the job. Then, execute the ROLLBACK DDL statement to roll back the job.

    1. Interrupt the CREATE TABLE DDL job during execution. 
      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 64;
^C^C -- query aborted
    2. Query the information about the DDL job. The interrupted DDL job is in the PENDING state. 
      mysql> show ddl\G
*************************** 1. row ***************************
     JOB_ID: 1103797850607083520
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_mdb_mtb
  JOB_TYPE: CREATE_TABLE
      PHASE: EXECUTE
      STATE: PENDING
  PROGRESS: 40%
START_TIME: 2019-08-29 14:52:55.660
  END_TIME: 2019-08-29 14:52:58.885
ELAPSED_TIME(MS): 3225
  DDL_STMT: 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 64
     REMARK: The job has been interrupted unexpectedly
    3. Execute the ROLLBACK DDL statement to roll back the job. 
      mysql> rollback ddl 1103797850607083520;
Query OK, 0 rows affected (6.42 sec)
    4. Rollback is successful. The table does not exist. 
      mysql> show tables like 'test_mdb_mtb';
Empty set (0.00 sec)

Cancel a job

You can cancel a running DDL job that is not in the PENDING state.

  • Syntax 
    CANCEL DDL <job_id> [ , <job_id> ] ...
    Parameter Description
    job_id The ID of the DDL job that is not in the PENDING state. This ID is displayed in the execution result of the SHOW DDL statement.
  • Example

    Create a logical table that is partitioned into table shards in a database shard. Execute the CANCEL DDL statement to cancel the job. Execute the SHOW DDL statement to query the status and job_id of the job. Later, you can resume or roll back the job.

    1. Execute the CREATE TABLE DDL statement on a connection. 
      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 64;
    2. Query the information of the running DDL job by executing the SHOW DDL statement on another connection. 
      mysql> show ddl\G
*************************** 1. row ***************************
   JOB_ID: 1103798959568478208
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_mdb_mtb
 JOB_TYPE: CREATE_TABLE
     PHASE: EXECUTE
     STATE: RUNNING
 PROGRESS: 26%
START_TIME: 2019-08-29 14:57:20.058
 END_TIME: 2019-08-29 14:57:22.284
ELAPSED_TIME(MS): 2243
 DDL_STMT: 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 64
   REMARK:
    3. Execute the CANCEL DDL statement to cancel the execution of the DDL job. 
      mysql> cancel ddl 1103798959568478208;
Query OK, 2 rows affected (0.03 sec)
    4. Execute the SHOW DDL statement to query the status of the DDL job. The DDL job has been canceled and is in the PENDING state. 
      mysql> show ddl\G
*************************** 1. row ***************************
   JOB_ID: 1103798959568478208
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_mdb_mtb
 JOB_TYPE: CREATE_TABLE
     PHASE: EXECUTE
     STATE: PENDING
 PROGRESS: 87%
START_TIME: 2019-08-29 14:57:20.058
 END_TIME: 2019-08-29 14:57:28.899
ELAPSED_TIME(MS): 8841
 DDL_STMT: 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 64
   REMARK: ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] The job '1103798959568478208' has been cancelled.

Delete a job

You can delete a pending DDL job that is suspended due to failures, and clear the corresponding caches.
Warning Be cautious about executing REMOVE DDL to delete a DDL job. After you delete a pending job, the intermediate state during the DDL execution is exposed, causing disturbance to subsequent operations. Therefore, when you are not sure whether the pending job can be securely deleted, do not execute the REMOVE DDL statement to delete the job. You can preferably resume or roll back the job to make the job exit the PENDING state first.
  • Syntax 
    REMOVE DDL { ALL PENDING | <job_id> [ , <job_id> ] ... }
    Parameter Description
    ALL PENDING Deletes all jobs that are in the PENDING state and clears internal caches.
    job_id The ID of the pending DDL job. This ID is displayed in the execution result of the SHOW DDL statement.
  • Example

    Assume that two tables exist in the database and a referential integrity relationship is established between the two tables. When you attempt to delete the parent table, an error is reported because tables with the referential integrity constraint cannot be deleted. In this case, if you do not want another attempt to delete the table, you can delete the DDL job.

    1. In the database, create two parent-child tables with the referential integrity relationship. 
      mysql> show create table test_parent\G
*************************** 1. row ***************************
Table: test_parent
Create Table: CREATE TABLE `test_parent` (
`id` int(11) NOT NULL,
`pkey` int(11) NOT NULL,
`col` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`pkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`id`)
1 row in set (0.01 sec)
mysql> show create table test_child\G
*************************** 1. row ***************************
Table: test_child
Create Table: CREATE TABLE `test_child` (
`id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
KEY `parent_id` (`parent_id`),
CONSTRAINT `test_child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `test_parent` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`parent_id`)
1 row in set (0.02 sec)
    2. Attempt to delete the parent table. Due to the referential integrity constraint, an error is reported. 
      mysql> drop table test_parent;
ERROR 4636 (HY000): [f518265d0066000][10.81.69.55:3306][ddltest]ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] Not all physical operations have been done successfully: expected 9,
but done 0. Caused by: 1217:DDLTEST_1562056402230OYMK_7WW7_0007:Cannot delete or update a parent row: a foreign key constraint fails on `test_parent`;1217:DDLTEST_15620564022
30OYMK_7WW7_0000:Cannot delete or update a parent row: a foreign key constraint fails on `test_parent`;1217:DDLTEST_1562056402230OYMK_7WW7_0002:Cannot delete or update a pare
nt row: a
    3. Query the DDL job. 
      mysql> show ddl\G
*************************** 1. row ***************************
     JOB_ID: 1103806757547171840
OBJECT_SCHEMA: ddltest
OBJECT_NAME: test_parent
  JOB_TYPE: DROP_TABLE
      PHASE: EXECUTE
      STATE: PENDING
  PROGRESS: 0%
START_TIME: 2019-08-29 15:28:19.240
  END_TIME: 2019-08-29 15:28:19.456
ELAPSED_TIME(MS): 216
  DDL_STMT: drop table test_parent
     REMARK: ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] Not all physical operations have been done successfully: expected 9, but done 0. Caused by: 1217:DDLTEST_1562056402
230OYMK_7WW7_0007:Cannot delete or update a parent row: a foreign key constraint fails on `test_pare ...
    4. The DDL job violates the referential integrity constraint when you attempt to delete the table. As a result, the delete operation fails. At this time, if you execute CHECK TABLE, you can see that the table is still consistent. 
      mysql> check table test_parent;
+---------------------------------------+-------+----------+----------+
| TABLE                                 | OP    | MSG_TYPE | MSG_TEXT |
+---------------------------------------+-------+----------+----------+
| ddltest_1562056402230oymk.test_parent | check | status   | OK       |
+---------------------------------------+-------+----------+----------+
1 row in set (0.05 sec)
    5. However, the table is inaccessible because a pending job exists for the table. 
      mysql> show tables like 'test_parent';
Empty set (0.00 sec)
mysql> show create table test_parent;
ERROR 4642 (HY000): [f5185a78b066000][10.81.69.55:3306][ddltest]ERR-CODE: [TDDL-4642][ERR_UNKNOWN_TABLE] Unknown table 'ddltest.test_parent'
    6. In this case, the table deletion job is not executed and the table structure is still consistent. It seems that you can choose to roll back the failed DDL operation. However, the DROP TABLE statement does not allow rollback operations. Therefore, you must choose to delete the failed DDL job. 
      mysql> remove ddl 1103806757547171840;
Query OK, 1 row affected (0.02 sec)
    7. After the DDL job is deleted, the table recovers to be accessible. 
      mysql> show tables like 'test_parent';
+-------------------+
| TABLES_IN_DDLTEST |
+-------------------+
| test_parent       |
+-------------------+
1 row in set (0.01 sec)