Best practices - PolarDB - Alibaba Cloud Documentation Center

This topic describes the best practices for processing a job in the PENDING state.

Background

In this scenario, an engine is started for a new DDL job. If the DDL job fails or is interrupted due to exceptions, the job enters the PENDING state. In this case, you must take measures to process and resume the job. Otherwise, subsequent DDL statements in this job cannot be executed and an error is returned.

How it works

You can execute the SHOW [FULL] DDL statement to query DDL job details and identify the failure cause. You can view an error message in the REMARK field.
The following list describes the common methods for processing a job in the PENDING state. You can select a method based on your business requirements.
- Troubleshoot and resolve an issue. For example, check whether the issue is caused by errors in the data. If the issue is caused by duplicates, remove duplicates. If the issue persists, check whether the issue is caused by limits. If the issue is caused by limits, check whether you can lift the limits. After you resolve the issue, execute the RECOVER DDL statement to resume the job in the PENDING state.
- If you cannot resolve the issue that causes a job failure, the DDL statements in the job cannot be executed. In this case, you can execute the REMOVE DDL statement to delete the job. Before you execute the REMOVE DDL statement, ensure that the DDL statements in the job are not executed. Otherwise, the table status that is returned by the SHOW statement may be inconsistent with the actual table status. After you delete the DDL job, the table can be queried again.
- If you want to delete a table that a DDL job fails to query, you can execute the REMOVE DDL statement to delete the job. Then, you can execute the DROP TABLE IF EXISTS statement to delete the table. Before you delete a table, make sure that the table is empty or the data on the table is no longer needed. You must use the IF EXISTS keyword in the DROP TABLE statement. This ensures that the table can be forcibly deleted.

Examples

The following examples show how to process a DDL job in the PENDING state.

Create a table without specifying a primary key. Then, insert duplicate rows into the table. The result indicates that the value 1 for the id column duplicates.

mysql> create table test_pending (id int not null, age int) dbpartition by hash(id);
Query OK, 0 rows affected (0.33 sec)
mysql> insert into test_pending values(1,10),(1,20),(2,20),(3,30);
Query OK, 4 rows affected (0.10 sec)
mysql> select * from test_pending order by id;
+------+------+
| id   | age  |
+------+------+
|    1 |   10 |
|    1 |   20 |
|    2 |   20 |
|    3 |   30 |
+------+------+
4 rows in set (0.10 sec)

Configure a primary key for the table. The result indicates that the id column is incorrectly configured. In this case, the DDL statement fails because the values for the id column are not unique.

mysql> alter table test_pending add primary key (id);
ERROR 4636 (HY000): [f5be83373466000][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 8. Caused by: 1062:DDLTEST_1562056402230OYMK_7WW7_0001:Duplicate entry '1' for key 'PRIMARY' on `test_pending`;.

Execute the SHOW FULL DDL statement to query the job status and the failure cause. The result indicates that the physical DDL statement fails because a physical table contains duplicate column values.
```
mysql> show full ddl\G
*************************** 1. row ***************************
          JOB_ID: 1106733441212637184
   PARENT_JOB_ID: 0
          SERVER: 1:102:10.81.69.55
   OBJECT_SCHEMA: ddltest
     OBJECT_NAME: test_pending
 NEW_OBJECT_NAME:
        JOB_TYPE: ALTER_TABLE
           PHASE: EXECUTE
           STATE: PENDING
        PROGRESS: 77%
      START_TIME: 2019-09-06 17:17:55.002
        END_TIME: 2019-09-06 17:17:55.273
ELAPSED_TIME(MS): 271
        DDL_STMT: alter table test_pending add primary key (id)
          REMARK: ERR-CODE: [TDDL-4636][ERR_DDL_JOB_ERROR] Not all physical operations have been done successfully: expected 9, but done 8. Caused by: 1062:DDLTEST_1562056402
230OYMK_7WW7_0001:Duplicate entry '1' for key 'PRIMARY' on `test_pending`;.
            
```
Content in the REMARK field:
- Not all physical operations have been done successfully: expected 9, but done 8.: You attempt to execute nine physical DDL statements on the logical table. Eight statements are executed, but one statement fails. This causes a failure in the DDL job. The DDL job enters the PENDING state.
- Caused by: 1062:DDLTEST_1562056402 230OYMK_7WW7_0001:Duplicate entry '1' for key 'PRIMARY' on 'test_pending';: This indicates the root cause of the failure. The value 1 for the id column duplicates in the physical table test_pending. The physical table is stored in the physical database DDLTEST_1562056402 230OYMK_7WW7_0001. Therefore, the id column cannot be used as the primary key.

Check the table for errors. The result indicates that the logical table status returned is inconsistent with the actual logical table status.

mysql> check table test_pending;
+----------------------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------+
| TABLE                                  | OP    | MSG_TYPE | MSG_TEXT                                                                                                    |
+----------------------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------+
| ddltest_1562056402230oymk.test_pending | check | Error    | Table 'DDLTEST_1562056402230OYMK_7WW7_0001.test_pending' find incorrect columns 'id', please recreate table |
+----------------------------------------+-------+----------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

Check whether subsequent statements in the job can be executed. The result indicates that an error is returned when you execute the DROP TABLE statement.

mysql> drop table test_pending;
ERROR 4644 (HY000): [f5beae39d466000][10.81.69.55:3306][ddltest]ERR-CODE: [TDDL-4644][ERR_PENDING_DDL_JOB_EXISTS] Another DDL job '1106733441212637184' with operation 'ALTER_
TABLE' is pending on ddltest.test_pending in ddltest. Please use SHOW DDL to check it, and then recover or rollback it using RECOVER DDL or ROLLBACK DDL, or just remove it us
ing REMOVE DDL if you confirm that the pending job can be discarded.

Use one of the following methods to process the job. For more information about the common methods, see the "How it works" section. The following code shows the effect of each method.

Remove duplicates from the table. Then, resume the DDL job to configure a primary key for the table.

Remove duplicates from the table. Before you perform this operation, make sure that you need only one copy of the data. If you want to execute the DELETE statement to remove duplicates, log on to your PolarDB-X 1.0 instance.If an error message is returned when you log on to the PolarDB-X 1.0 instance, you can connect to a backend ApsaraDB RDS for MySQL database based on the error message.
```
mysql> delete from test_pending where id=1 and age=20;
Query OK, 1 row affected (0.07 sec)
mysql> select * from test_pending order by id;
+------+------+
| id   | age  |
+------+------+
|    1 |   10 |
|    2 |   20 |
|    3 |   30 |
+------+------+
3 rows in set (0.02 sec)
                            
```

After you remove duplicates, resume the DDL job in the PENDING state. The result indicates that the DDL job resumes, the record of the job failure is cleared, and the primary key is configured for the table.

mysql> recover ddl 1106733441212637184;
Query OK, 0 rows affected (1.28 sec)
mysql> show full ddl\G
Empty set (0.00 sec)
mysql> show create table test_pending\G
*************************** 1. row ***************************
       Table: test_pending
Create Table: CREATE TABLE `test_pending` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_id` (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`id`)
1 row in set (0.02 sec)
mysql> check table test_pending;
+----------------------------------------+-------+----------+----------+
| TABLE                                  | OP    | MSG_TYPE | MSG_TEXT |
+----------------------------------------+-------+----------+----------+
| ddltest_1562056402230oymk.test_pending | check | status   | OK       |
+----------------------------------------+-------+----------+----------+
1 row in set (0.10 sec)

Delete the failed DDL job, delete the table, and then create the table again. Before you delete the table, make sure that the data on the table is no longer needed.

mysql> remove ddl 1106733441212637184;
Query OK, 1 row affected (0.02 sec)
mysql> drop table if exists test_pending;
Query OK, 0 rows affected (0.44 sec)
mysql> show tables like 'test_pending';
Empty set (0.01 sec)