## Module 1: Perform an Online Schema Change while the Application is Running

### Step 1. Start the data loading workload against the testing table

- Click [Terminal 1](ssh://monitor1-ssh#1) to log into the lab machine.

- Execute the following commands to run the data loading workload against the testing table named `demo_405a`. Keep in running, and proceed to the next step.

  ```bash
  source .venv/bin/activate
  source .env
  mysql \
   --comments -u "${TIDB_USER}" -p"${TIDB_PASSWORD}" \
   -h ${TIDB_HOST} -P ${TIDB_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt <<EOF
  DROP DATABASE IF EXISTS ${TIDB_DB_NAME};
  CREATE DATABASE ${TIDB_DB_NAME};
  SET GLOBAL tidb_enable_metadata_lock = OFF;
  USE ${TIDB_DB_NAME};
  DROP TABLE IF EXISTS demo_405a;
  CREATE TABLE demo_405a (
      id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      k INT NOT NULL,
      c CHAR(60) NOT NULL,
      INDEX k_idx (k)
  );
  EOF
  python load.py --insert
  ```

### Step 2. Apply the schema change while the workload is running

- Click [Terminal 2](ssh://monitor1-ssh#2) to log into the lab machine in another session.

- Connect to the demo database.

  ```bash
  source .env
  mysql \
   --comments -u "${TIDB_USER}" -p"${TIDB_PASSWORD}" \
   -h ${TIDB_HOST} -P ${TIDB_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${TIDB_DB_NAME}
  ```

- Review the table structure, there are 3 columns.

  ```sql
  DESC demo_405a;
  ```

  ```output
  +-------+----------+------+------+---------+----------------+
  | Field | Type     | Null | Key  | Default | Extra          |
  +-------+----------+------+------+---------+----------------+
  | id    | int(11)  | NO   | PRI  | NULL    | auto_increment |
  | k     | int(11)  | NO   | MUL  | NULL    |                |
  | c     | char(60) | NO   |      | NULL    |                |
  +-------+----------+------+------+---------+----------------+
  3 rows in set (0.00 sec)
  ```

- Verify that the workload is still running and that the row count continues to increase.

  ```sql
  SELECT COUNT(*) FROM demo_405a;
  SELECT SLEEP(2);
  SELECT COUNT(*) FROM demo_405a;
  ```

- Change the table schema by adding a new column called `attr1`. Observe that the transactions in Terminal 1 is NOT blocking the DDL.

  ```sql
  ALTER TABLE demo_405a ADD COLUMN (attr1 VARCHAR(10) DEFAULT 'N/A');
  ```

  ```output
  Query OK, 0 rows affected (0.44 sec)
  ```

- Confirm the schema change result. A new column was added.

  ```sql
  DESC demo_405a;
  ```

  ```output
  +-------+-------------+------+------+---------+----------------+
  | Field | Type        | Null | Key  | Default | Extra          |
  +-------+-------------+------+------+---------+----------------+
  | id    | int(11)     | NO   | PRI  | NULL    | auto_increment |
  | k     | int(11)     | NO   | MUL  | NULL    |                |
  | c     | char(60)    | NO   |      | NULL    |                |
  | attr1 | varchar(10) | YES  |      | N/A     |                |
  +-------+-------------+------+------+---------+----------------+
  4 rows in set (0.80 sec)
  ```

- Switch to [Terminal 1](ssh://monitor1-ssh#1) and confirm that your code has detected the schema change and adapted to it while the workload continues to run.

  ```output
  Inserting record 268
  Inserting record 269
  Inserting record 273
  Schema mutation encountered, retrying...
  Inserting record 274
  Inserting record 275
  Inserting record 276
  ```

### Step 3. Examine the workload code

- If you have time, review the exception handling section in `load.py` between lines 19 and 52.

- For a detailed best practice explanation of online schema change operations, please refer to Module 5 of the [Introduction to TiDB Cloud](/labs/demo_203).

### Step 4. Clean up

- In [Terminal 1](ssh://monitor1-ssh#1), press `CTRL-C`.

- Run the following commands to drop table practice table and restore the default settings.

  ```bash
  mysql \
   --comments -u "${TIDB_USER}" -p"${TIDB_PASSWORD}" \
   -h ${TIDB_HOST} -P ${TIDB_PORT} \
   --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   -D ${TIDB_DB_NAME} <<EOF
  SET GLOBAL tidb_enable_metadata_lock = ON;
  DROP TABLE IF EXISTS demo_405a;
  EOF
  ```