## 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 demonstrates 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
  poetry run python mysql_connector_python_demo/online_ddl.py --insert
  ```

  ```output
  Starting insert job on table online_ddl_test
  You can now run ALTER TABLE in another terminal with:
  python online_ddl.py --alter online_ddl_test
  Connected to TiDB: 2nR9s2Y6oh4rkp7.6z3VQEZ4@gateway01.us-west-2.prod.aws.tidbcloud.com:4000 using database test
  Disabled TiDB metadata lock for online DDL operations
  Current setting: tidb_enable_metadata_lock = OFF
  Created table online_ddl_test
  Inserting record 0
  Inserting record 1
  Inserting record 2
  Inserting record 3
  Inserting record 4
  Inserting record 5
  ...
  ```

  ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-online-ddl-0319.jpg)

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

  ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-add-terminal-0319.jpg)

+ Run the schema modification in the new terminal.

  ```bash
  poetry run python mysql_connector_python_demo/online_ddl.py --alter
  ```

  ```output
  Executing ALTER TABLE on online_ddl_test
  Connected to TiDB: 2nR9s2Y6oh4rkp7.6z3VQEZ4@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)
  ```

  ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-alter-table-0319.jpg)

+ On VS Code, switch back to the first terminal, observe the insert job will continue to run with a retry log.

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

  ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-error-catch.jpg)

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