## Module 3: Automatically pause and resume DDL jobs In certain scenarios, such as database upgrades or shutdowns and startups, DDL jobs can be automatically paused and resumed. ### Step 1. Connect to the TiUP control machine with two terminals + Open two SSH connections to your lab virtual machine, by clicking: + [Terminal 1](ssh://monitor1-ssh#1) + [Terminal 2](ssh://monitor1-ssh#2) + You will now have two active terminal sessions. ### Step 2. Add index to table and modify column attributes > [!TIP] > Please note that the time interval between the commands in this module should not exceed 1 minute, as this may lead to unexpected results. + On [Terminal 1](ssh://monitor1-ssh#1), connect to the TiDB server instance. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + On [Terminal 1](ssh://monitor1-ssh#1), add an index to the table `test.ddl_test2` and modify the attributes of the `content` column. Please note: Do not wait for this command to finish. ```sql ALTER TABLE test.ddl_test2 DROP INDEX idx_content_updated_at; ``` ```output Query OK, 0 rows affected (0.16 sec) ``` ```sql ALTER TABLE test.ddl_test2 ADD INDEX idx_content_updated_at(content, updated_at);ALTER TABLE test.ddl_test2 MODIFY `content` VARCHAR(32) DEFAULT '0000000000' COMMENT 'content'; ``` + On [Terminal 2](ssh://monitor1-ssh#2), connect to the TiDB server instance. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Use the command `ADMIN SHOW DDL JOBS` to find the `JOB_ID` of the DDL job executed just now. In the example, the `JOB_ID` is `180` and `STATE` is `running`, indicating that the DDL job is currently in progress. Please note that your output may differ from the example provided. ```sql ADMIN SHOW DDL JOBS; EXIT; ``` ```output +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | NULL | running | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | | 170 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:47.830000 | synced | ingest, DXF | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 11 rows in set (0.01 sec) ``` ### Step 3. Shutdown and Startup the TiDB Database + On [Terminal 2](ssh://monitor1-ssh#2), shutdown the database. ```bash export PATH="$PATH:/home/ec2-user/.tiup/bin" tiup cluster stop tidb-demo ``` ```output Will stop the cluster tidb-demo with nodes: , roles: . Do you want to continue? [y/N]:(default=N) y + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa.pub + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.181 + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.46 ... Stop 10.90.2.169 success Stop 10.90.4.234 success Stopped cluster `tidb-demo` successfully ``` + On [Terminal 1](ssh://monitor1-ssh#1), we have noticed that the connection between the client and the database has been interrupted, but we are unable to determine whether the DDL task has completed. ```output Query OK, 0 rows affected (8.96 sec) ERROR 2013 (HY000): Lost connection to MySQL server during query ``` + On [Terminal 2](ssh://monitor1-ssh#2), startup the database. ```bash export PATH="$PATH:/home/ec2-user/.tiup/bin" tiup cluster start tidb-demo ``` ```output Starting cluster tidb-demo... + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-demo/ssh/id_rsa.pub + [Parallel] - UserSSH: user=ec2-user, host=10.90.1.181 + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.169 ... Start 10.90.2.119 success Start 10.90.4.234 success + [ Serial ] - UpdateTopology: cluster=tidb-demo Started cluster `tidb-demo` successfully ``` ### Step 4. Query the DDL jobs + On [Terminal 2](ssh://monitor1-ssh#2), connect to TiDB server instance. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Use the command `ADMIN SHOW DDL JOBS` to find the `JOB_ID` of the DDL job started to execute before the database restart. In the example, the `JOB_ID` is `180` and `STATE` is `running`, indicating that the DDL job is currently in progress. Please note that your output may differ from the example provided. ```sql ADMIN SHOW DDL JOBS; ``` ```output +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | write reorganization | 2 | 112 | 0 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | NULL | running | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | | 170 | test | ddl_test1 | add index | public | 2 | 110 | 4194304 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:37.981000 | 2025-07-27 05:49:47.830000 | synced | ingest, DXF | +--------+---------+------------+---------------+----------------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 11 rows in set (0.02 sec) ``` + Please repeatedly execute the command `ADMIN SHOW DDL JOBS` some times until the `STATE` of the DDL job with `JOB_ID` `180` changes to `synced`, indicating that the DDL task has completed. Please note that your output may differ from the example provided. ```sql ADMIN SHOW DDL JOBS; ``` ```output +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | JOB_ID | DB_NAME | TABLE_NAME | JOB_TYPE | SCHEMA_STATE | SCHEMA_ID | TABLE_ID | ROW_COUNT | CREATE_TIME | START_TIME | END_TIME | STATE | COMMENTS | +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ | 180 | test | ddl_test2 | modify column | public | 2 | 112 | 4194304 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:34:35.931000 | 2025-07-27 06:47:39.090000 | synced | | | 179 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 06:34:26.931000 | 2025-07-27 06:34:26.981000 | 2025-07-27 06:34:35.880000 | synced | ingest, DXF | | 178 | test | ddl_test2 | drop index | none | 2 | 112 | 0 | 2025-07-27 06:32:36.731000 | 2025-07-27 06:32:36.781000 | 2025-07-27 06:32:36.881000 | synced | | | 177 | test | ddl_test4 | modify column | public | 2 | 116 | 7250922 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:16:59.731000 | 2025-07-27 06:20:49.080000 | synced | | | 176 | test | ddl_test4 | add index | public | 2 | 116 | 4194304 | 2025-07-27 06:08:28.731000 | 2025-07-27 06:10:53.130000 | 2025-07-27 06:16:59.731000 | synced | ingest, DXF | | 175 | test | ddl_test3 | modify column | public | 2 | 114 | 4194304 | 2025-07-27 06:08:20.181000 | 2025-07-27 06:08:20.231000 | 2025-07-27 06:15:07.480000 | synced | | | 174 | test | ddl_test3 | add index | public | 2 | 114 | 4194304 | 2025-07-27 06:08:03.880000 | 2025-07-27 06:08:03.931000 | 2025-07-27 06:08:20.130000 | synced | ingest, DXF | | 173 | test | ddl_test2 | modify column | none | 2 | 112 | 0 | 2025-07-27 05:58:49.530000 | 2025-07-27 05:58:49.530000 | 2025-07-27 06:02:37.780000 | rollback done | | | 172 | test | ddl_test2 | add index | public | 2 | 112 | 4194304 | 2025-07-27 05:58:39.031000 | 2025-07-27 05:58:39.081000 | 2025-07-27 05:58:49.480000 | synced | ingest, DXF | | 171 | test | ddl_test1 | modify column | public | 2 | 110 | 4194304 | 2025-07-27 05:49:49.981000 | 2025-07-27 05:49:50.031000 | 2025-07-27 05:54:29.281000 | synced | | +--------+---------+------------+---------------+--------------+-----------+----------+-----------+----------------------------+----------------------------+----------------------------+---------------+-------------+ 10 rows in set (0.01 sec) ``` + On [Terminal 1](ssh://monitor1-ssh#1), upon checking the definition of `test.ddl_test2`, we find that both the adding index and the modification of the `content` column's attributes were successful. ```sql SHOW CREATE TABLE test.ddl_test2; ``` ```output *************************** 1. row *************************** Table: ddl_test2 Create Table: CREATE TABLE `ddl_test2` ( `id` bigint DEFAULT NULL, `content` varchar(32) DEFAULT '0000000000' COMMENT 'content', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `version` int DEFAULT NULL, KEY `idx_content_updated_at` (`content`,`updated_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.01 sec) ERROR: No query specified ``` + This is the end of Module 3.