PolarDB:Job management statements

Query a job

SHOW DDL returns all DDL jobs currently in the DDL queue — both actively running jobs and jobs stuck in PENDING state due to failures. Jobs that complete successfully move to COMPLETED state and are automatically cleared; they do not appear in the output.

Syntax

SHOW [FULL] DDL

Parameters

Output fields

Example

This example creates a sharded table and queries the job while it is running. Run the CREATE TABLE statement on one connection, then run SHOW FULL DDL on a second connection.

1. On connection 1, run:

   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. On connection 2, query the job:

   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:

   The STATE is RUNNING and PROGRESS is at 90%, meaning the job is still executing. The empty REMARK field confirms no errors have occurred. When the job completes, it moves to COMPLETED state and is automatically removed from the queue.

Resume a job

RECOVER DDL resumes one or more DDL jobs in PENDING state.

Before resuming, run SHOW DDL and check the REMARK field to identify why the job failed. Fix the underlying issue first — resuming without resolving the cause results in the job failing again immediately.

Syntax

RECOVER DDL { ALL | <job_id> [, <job_id>] ... }

Parameters

Example

This example interrupts a CREATE TABLE job mid-execution and then resumes it.

1. On connection 1, start creating a sharded table, then interrupt it:

   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 job to confirm it is in PENDING state and get the JOB_ID:

   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

   The STATE is PENDING and REMARK shows the job was interrupted unexpectedly — not a data integrity issue, so it is safe to resume.

3. Resume the job:

   mysql> recover ddl 1103796219480006656;
   Query OK, 0 rows affected (7.28 sec)

4. Run CHECK TABLE to verify the table was created without inconsistencies:

   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)

   A status: OK result confirms the table is consistent.

Roll back a job

ROLLBACK DDL rolls back a PENDING DDL job, undoing any partial changes made before the failure.

Syntax

ROLLBACK DDL <job_id> [, <job_id>] ...

Parameters

Example

This example interrupts a CREATE TABLE job and rolls it back.

1. On connection 1, start creating a sharded table, then interrupt it:

   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 job to get the JOB_ID:

   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. Roll back the job:

   mysql> rollback ddl 1103797850607083520;
   Query OK, 0 rows affected (6.42 sec)

4. Verify the table no longer exists:

   mysql> show tables like 'test_mdb_mtb';
   Empty set (0.00 sec)

   The empty result set confirms the rollback succeeded and the table was removed.

Cancel a job

CANCEL DDL cancels a DDL job that is actively running (not in PENDING state). After cancellation, the job transitions to PENDING state with an error in the REMARK field. From PENDING, you can resume the job with RECOVER DDL or roll it back with ROLLBACK DDL.

Syntax

CANCEL DDL <job_id> [, <job_id>] ...

Parameters

Example

This example cancels a running CREATE TABLE job.

1. On connection 1, start creating a sharded table:

   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. On connection 2, query the running job to get its JOB_ID:

   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. Cancel the job:

   mysql> cancel ddl 1103798959568478208;
   Query OK, 2 rows affected (0.03 sec)

4. Query the job again to confirm its 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.

   The job is now in PENDING state. The REMARK field confirms it was cancelled (ERR_DDL_JOB_ERROR). Use RECOVER DDL or ROLLBACK DDL to resolve the pending job.

Delete a job

REMOVE DDL deletes a PENDING DDL job and clears the associated internal caches.

Syntax

REMOVE DDL { ALL PENDING | <job_id> [, <job_id>] ... }

Parameters

Example

This example shows a scenario where a DROP TABLE job fails due to a referential integrity constraint, leaving the table inaccessible. Because DROP TABLE does not support rollback, the only option is to delete the pending job.

1. Inspect the existing parent-child tables with their referential integrity constraint:

   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 drop the parent table. The operation fails because test_child holds a foreign key referencing test_parent:

   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`;...

3. Query the DDL job to confirm it is in PENDING state:

   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_1562056402230OYMK_7WW7_0007:Cannot delete or update a parent row: a foreign key constraint fails on `test_pare ...

   The REMARK confirms the foreign key constraint blocked the drop. At 0% progress, no physical deletion occurred — the table structure is still intact.

4. Verify the table data is 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 pending job blocks access to 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'

   The table is consistent but unreachable. Because DROP TABLE does not support rollback, delete the failed job to restore access.

6. Delete the pending job:

   mysql> remove ddl 1103806757547171840;
   Query OK, 1 row affected (0.02 sec)

7. Confirm the table is accessible again:

   mysql> show tables like 'test_parent';
   +-------------------+
   | TABLES_IN_DDLTEST |
   +-------------------+
   | test_parent       |
   +-------------------+
   1 row in set (0.01 sec)

   The table is visible and accessible. Address the referential integrity constraint before retrying the DROP TABLE operation.