## Module 5: Online Schema Change

TiDB can achieve Online Schema Change via Online DDL, which allows users to modify the table definition—such as adding or dropping columns, modifying column properties, adding or dropping indexes, and renaming tables—without stopping the application. These operations can be performed online with zero downtime. For more details on the principle, refer to: [Best Practices for DDL Execution in TiDB](https://docs.pingcap.com/tidb/stable/ddl-introduction).

In this module, you will add the `Discount` column to the `SHOP`.`TRANSACTIONS` table while the application is running.

You can also review the fundamentals of Online DDL in "Module 5: Evolving the Schema with Non-Blocking Online DDL" in [Introduction to TiDB Cloud Starter](https://labs.tidb.io/labs/demo_203) after completing this module.

### Step 1. Check the "Transactions List" page and table in database

+ Access the "Transactions list" page at [Transaction List](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}). We aim to add a `Discount` column for the table in this lab.

![204_5_1_1.png 85%](https://lab-static.pingcap.com/images/2026/1/22/9e0c98ea3fd1a12d505fd8f2c4aae194176e2136.png)

+ In VS Code, open a **NEW Terminal** (let's call it "VS Code Terminal 2").

![204_5_1_2.png 85%](https://lab-static.pingcap.com/images/2026/1/22/d3c60da79b95bd149d9bde24611fe3996e42d374.png)

+ In VS Code Terminal 2, execute the  `php artisan model:show Transaction` command to view the definition of the table `SHOP`.`TRANSACTIONS` corresponding to the Model `APP/Model/Transaction.php`.

  ```bash
  php artisan model:show Transaction
  ```

  ```output
  App\Models\Transaction .....................................................
  Database ............................................................. mysql
  Table ......................................................... transactions

  Attributes ..................................................... type / cast
  id unique, fillable .............................. bigint(20) unsigned / int
  user_id fillable ....................................... bigint(20) unsigned
  product_id fillable .................................... bigint(20) unsigned
  price fillable ............................................... decimal(10,2)
  buy_count fillable ................................................. int(11)
  total_price fillable ......................................... decimal(18,2)
  created_at nullable ................................... timestamp / datetime
  updated_at nullable ................................... timestamp / datetime
  pay_type fillable ............................................... bigint(20)

  Relations ..................................................................
  Product HasOne ...................................... App\Models\Product
  User HasOne ................................................ App\Models\User
  PayType HasOne .......................................... App\Models\PayType

  Events .....................................................................

  Observers ..................................................................
  ```

+ Alternatively, in VS Code Terminal 2, use the command `php artisan db:table transactions` to check the definition of the table `SHOP`.`TRANSACTIONS`and confirm that the `Discount` column is not present.

  ```bash
  php artisan db:table transactions
  ```

  ```output
  shop.transactions ..........................................................
  Columns .................................................................. 9
  Size ............................................................... 2.02 KB
  Engine .............................................................. InnoDB
  Collation ............................................... utf8mb4_unicode_ci

  Column ................................................................ Type
  id bigint .............................................. bigint(20) unsigned
  user_id bigint ......................................... bigint(20) unsigned
  product_id bigint ...................................... bigint(20) unsigned
  price decimal ................................................ decimal(10,2)
  buy_count int ...................................................... int(11)
  total_price decimal .......................................... decimal(18,2)
  created_at timestamp, nullable ................................... timestamp
  updated_at timestamp, nullable ................................... timestamp
  pay_type bigint ............................................... 1 bigint(20)

  Index ......................................................................
  primary id .................................................. btree, primary
  user_transaction_idx user_id ......................................... btree
  product_transaction_idx product_id ................................... btree
  ```

### Step 2. Prepare for online schema change

+ In VS Code Terminal 2, connect to TiDB Cloud by the MySQL client.

   ```bash
   mysql --comments -u ${LAB:SERVERLESS_CLUSTER_USERNAME} \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl \
   --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   --ssl-verify-server-cert \
   -p${LAB:SERVERLESS_CLUSTER_PASSWORD} \
   ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
   ```

   ```output
   Your MySQL connection id is 61317775
   Server version: 8.0.11-TiDB-v7.5.2-serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   MySQL [shop]>
   ```

+ In VS Code Terminal 2, run the command to check the value of the system variable `tidb_enable_metadata_lock`. Its default value is `ON`.

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  MySQL [shop]> SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | ON    |
  +---------------------------+-------+
  1 row in set (0.011 sec)
  ```

+ Change the value of the system variable `tidb_enable_metadata_lock` to `OFF`.  VS Code will pop up a window asking "Are you sure you want to paste 2 lines of text into the terminal?", please click "Paste".

   ![204_5_2_1.png 85%](https://lab-static.pingcap.com/images/2026/2/6/f59a9fb8fb4b13e9f37efd228ef37c04b1da3a1f.png)

  ```sql
  SET GLOBAL tidb_enable_metadata_lock = OFF;
  SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  MySQL [shop]> SET GLOBAL tidb_enable_metadata_lock = OFF;
  Query OK, 0 rows affected (0.016 sec)

  MySQL [shop]> SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | OFF   |
  +---------------------------+-------+
  1 row in set (0.011 sec)
  ```

+ Note: When the system variable `tidb_enable_metadata_lock`is `ON` (the default value), all DDL commands must wait for DML statements on the related table to complete. The DDL command will be blocked during this waiting period, which is consistent with the behavior of MySQL.
Only when the system variable `tidb_enable_metadata_lock` is `OFF` will the execution of DDL commands not be affected by any DML commands.
For details, refer to: "Module 5: Evolving the Schema with Non-Blocking Online DDL" in [Introduction to TiDB Cloud Starter](/labs/demo_203).

+ In the VS Code Terminal 2, exit the MySQL client.

   ```sql
   EXIT
   ```

   ```output
   MySQL [shop]> EXIT
   Bye
   root@0c2c53eace8f:~/Mshop#
   ```

+ Modify the function `public function buy(Request $request, Product $product)` in the Controller `app/Http/Controllers/ProductsController.php`. This function implements the purchase transaction functionality, which may be executed repeatedly during the schema change process. Change the initial value of the variable `$OnlineSchemaChange` from `false` to `true`and save the file. This takes effect immediately.

  ```php
  /**
  * Process the products purchase.
  */
  public function buy(Request $request, Product $product)
  {
    try {
        //Validate
        $validated = $request->validate(rules: [
            'buy_count' => 'required|integer|min:1',
            'pay_with' => 'required|integer|min:1',
        ]);
    } catch(ValidationException $e) {

        return redirect('/products/' . $product->id)
            ->withErrors($validated)
            ->withInput();
    }

    /**
    * If $OnlineSchemaChange is true, execute function $this->executeTransactionWithRetry(...) to prevent from the
    * error 'Error 8028: Information schema is changed during the execution of the statement' by retry for purchase transaction.
    * If $OnlineSchemaChange is true, exectue function $this->executeTransaction(...) for purchase transaction without retry.
    */
    $OnlineSchemaChange = true;

    if($OnlineSchemaChange) {

        //Execute the logic of purchase transaction.
        return $this->executeTransactionWithRetry($request, $product, $validated);
    } else {
        //Use recursive functions to handle retry logic of purchase transaction.
        return $this->executeTransaction($request, $product, $validated);

    }
  }
  ```

+ The reason for the above change is that when Online DDL starts executing on the`SHOP`.`TRANSACTIONS` table, transactions that began before the DDL command and are currently running on `SHOP`.`TRANSACTIONS` may fail due to the table structure change (ERROR 8028: Information schema is changed). Therefore, another function, `private function executeTransactionWithRetry($request, $product, $validated, $retryCount = 0)`, is introduced in the application to resolve this issue through retries, ensuring all transactions execute normally. For more details, refer to: "Module 5: Evolving the Schema with Non-Blocking Online DDL" in [Introduction to TiDB Cloud Starter](https://labs.tidb.io/labs/demo_203).

### Step 3. Preform continuous change from the application

- Click [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) to access the management page, then click the `Start auto change` button. From this point on, the page will automatically and randomly perform transactions and stock refills. For easier observation, only the book titled `Waterfalls` will be updated.

![start-auto-change.jpg 85%](https://lab-static.pingcap.com/images/2026/2/9/e3dd6c1002045ae88ebf7280a575de63a34aa830.jpg)

> [!TIP]
> If you already have the [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) page opened in your browser, please refresh the webpage before click the `Start auto change` button.

- Keep the page open and proceed to the next step.

### Step 4. Perform the online schema change

+ In VS Code Terminal 2, use the `php artisan make:migration…` command to generate the migration file for adding the `Discount` column to the `SHOP`.`TRANSACTIONS` table.

  ```bash
  php artisan make:migration add_discount_to_transactions_table --table=transactions
  ```

  ```output
  INFO  Migration [database/migrations/2026_01_22_171252_add_discount_to_transactions_table.php] created successfully.
  ```

+ In VSCode, locate the newly generated code file `2026_01_22_171252_add_discount_to_transactions_table.php` in `/database/migrations/`. Note that your file name may differ from the manual's example; the format is: `xxxx_xx_xx_xxxxxx_add_discount_to_transactions_table.php`.

![205_5_3_1.png 85%](https://lab-static.pingcap.com/images/2026/1/22/c95ec6163c9498ad30ac3f16926650d4130b641d.png)

+ Update the file `/database/migrations/2026_01_22_171252_add_discount_to_transactions_table.php`  to add the definition for the `Discount` column and the rollback operation, then save the file. Note that your file name may differ from the manual's example; the format is: `xxxx_xx_xx_xxxxxx_add_discount_to_transactions_table.php`.

  ```php
  <?php

  use Illuminate\Database\Migrations\Migration;
  use Illuminate\Database\Schema\Blueprint;
  use Illuminate\Support\Facades\Schema;

  return new class extends Migration
  {
    /**
      * Run the migrations.
      */
    public function up(): void
    {
        Schema::table('transactions', function (Blueprint $table) {
            //
            $table->decimal('discount', 5,2)->default(0.0);
        });
    }

    /**
      * Reverse the migrations.
      */
    public function down(): void
    {
        Schema::table('transactions', function (Blueprint $table) {
            //
            $table->dropColumn('discount');
        });
    }
  };
  ```

+ Return to the Terminal and use the `php artisan migration`command to perform the online schema change for the `SHOP`.`TRANSACTIONS` table. The running transactions will not block the schema change.

  ```bash
  php artisan migrate
  ```

  ```output
  INFO  Running migrations.

  2026_01_22_171252_add_discount_to_transactions_table ................................ 845.16ms DONE
  ```

+ Observe the [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) page; running transactions are not blocked by the schema change.

+ Continue to use the command `php artisan db:table transactions`in the Terminal to view the definition of the`SHOP`.`TRANSACTIONS` table. You will find that the `Discount`column has been added, and no transactions failed due to the table modification throughout the entire process.

  ```bash
  php artisan db:table transactions
  ```

  ```output
  shop.transactions ..........................................................
  Columns ................................................................. 10
  Size ............................................................... 2.09 KB
  Engine .............................................................. InnoDB
  Collation ............................................... utf8mb4_unicode_ci

  Column ................................................................ Type
  id bigint .............................................. bigint(20) unsigned
  user_id bigint ......................................... bigint(20) unsigned
  product_id bigint ...................................... bigint(20) unsigned
  price decimal ................................................ decimal(10,2)
  buy_count int ...................................................... int(11)
  total_price decimal .......................................... decimal(18,2)
  created_at timestamp, nullable ................................... timestamp
  updated_at timestamp, nullable ................................... timestamp
  pay_type bigint ............................................... 1 bigint(20)
  discount decimal ................................................ 0 decimal(5,2)

  Index ......................................................................
  primary id .................................................. btree, primary
  user_transaction_idx user_id ......................................... btree
  product_transaction_idx product_id ................................... btree
  ```

### Step 5. Modify the view and check the new transactions list page

+ In VSCode, modify the function `public function index()` in the Controller `APP/Http/Controllers/TransactionController.php`. This function is used to implement the "Transactions list" functionality. Change the initial value of the variable `$hasDiscount` from`false` to `true` and save the file.

  ```php
  /**
  * Display a listing of the resource.
  */
  public function index()
  {
  //
  // Rows per page.
  $row_per_page = 10;

  // Get the transactions of this page.
  $TransactionPaginate = Transaction::OrderBy('created_at', 'desc')
  ->OrderBy('user_id','desc')
  ->with('Product')
  ->with('User')
  ->with('PayType')
  ->paginate($row_per_page);

  // Set the title and data for view.
  $binding = [
      'title' => __('shop.transaction.Transactions-list'),
      'TransactionPaginate'=> $TransactionPaginate,
  ];

  /**
  * If $hasDiscount is true, it will use the view 'Transactions.listTransactionSave.blade.php' with 'Discount' column in the its table to list the transactions.
  * If $hasDiscount is false, it will use the view 'Transactions.listTransaction.blade.php' without 'Discount' column in the its table to list the transactions.
  */
  $hasDiscount = true;
  if($hasDiscount) {
      // Sent the Transactions data to view with the column 'Discount'.
      return view('transactions.listTransactionSave', $binding);
  }
  else {
      // Sent the Transactions data to view without the column 'Discount'.
      return view('transactions.listTransaction', $binding);
  }
  ```

+ After the modification, the transaction list is rendered by the View `resource/views/Transactions.listTransactionSave.blade.php`. Access the "Transactions list" page at [Transaction List](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}) again, and you will find that the `Discount` column is now present.

![204_5_4_1.png 85%](https://lab-static.pingcap.com/images/2026/1/22/c09c1264220e704401c4f1d4af194610c3b7a3d2.png)

+ You have now completed this module.