## Module 5: Use Online DDL To Perform Online Schema Changes

+ This module illustrates how to perform **online schema changes (DDL operations)** in **TiDB** while ensuring that an ongoing **INSERT job** continues running without interruption. A key challenge in this process is handling **error code 8028**, which occurs when schema mutations temporarily block transactions. The demo shows a robust approach to managing this scenario, allowing the **insert job** to recover and continue despite the schema change.

+ This module consists of two main operations:

1. **Insert Job (`--insert`)**
   + Continuously inserts new records into a specified table.
   + Handles potential schema mutation exception (error code **8028**) that may occur due to concurrent DDL operations.
   + Retries the insert operation in case of failure to maintain service availability.

2. **Schema Modification (`--alter`)**
   + Executes an `ALTER TABLE` command to modify the table structure while inserts are in progress.
   + Demonstrates that TiDB allows **online schema changes**, but transactions may momentarily fail due to schema mutations.
   + Ensures that the new schema is successfully applied while ongoing transactions adapt accordingly.

> [!TIP]
> **Key Implementation Details:**
>
> + **Handling TiDB Metadata Lock**: The script **disables metadata locking** (`SET GLOBAL tidb_enable_metadata_lock = OFF`) to allow DDL changes without blocking queries.
> + **Automatic Error Handling**: The insert job **detects error code 8028** and **retries failed transactions** to prevent crashes.
> + **Concurrent Execution**: The script enables running the **insert job** and **schema modification** in separate terminals, simulating a real-world scenario where database updates occur alongside ongoing transactions.

+ Run the insert job first.

  ```bash
  rm -f DemoJdbcOnlineDDL.class
  javac -cp mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL.java  
  sleep 1   
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL --insert
  ```

  ```output
  ...
  Inserting record 0
  Inserting record 1
  Inserting record 2
  Inserting record 3
  Inserting record 4
  Inserting record 5
  ...
  ```

+ Create a new terminal by clicking the `+` button in the VS Code.

  <![90%](https://lab-static.pingcap.com/images/2025/7/7/797b653f8fc9a08a362cf618d2fe7fb9ef960fad.jpeg)>

+ Run the schema modification in the new terminal.

  ```bash
  source env.sh
  java -cp :mysql-connector-j-9.1.0.jar DemoJdbcOnlineDDL --alter
  ```

  ```output
  Executing ALTER TABLE on online_ddl_test
  Connected to TiDB: xxxxxx.xxxxxx@gateway01.us-west-2.prod.aws.tidbcloud.com:4000 using database test
  Table online_ddl_test exists, proceeding with ALTER TABLE
  Executing: ALTER TABLE online_ddl_test ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A')
  ALTER TABLE completed successfully in 0.39 seconds

  Updated table structure:
    id: int(11)
    k: int(11)
    c: char(120)
    pad: char(60)
    ed: varchar(10)
  ```

+ On VS Code, switch back to the first terminal, we can see that the insert job continues with a retry log.

  ```output
  ...
  Inserting record 105
  Inserting record 106
  Schema mutation encountered, retrying...
  Inserting record 107
  Inserting record 108
  ...
  ```

+ Neither the insert DML nor the DDL operation are blocked.

+ You can now kill the running insertion program using `CTRL + c` in the terminal.