## Module 4: Online Schema Change Online schema changes are particularly critical in SaaS environments, where uninterrupted uptime and continuous availability are vital for ensuring a seamless user experience. In this module, you will execute a DDL operation to add a new column to an existing table to support a business change while simultaneous DML insertions are occurring on the same table. You'll observe that the DDL operation is not blocked by the ongoing DML, and the Java application running the DML is designed to be aware of TiDB's Online DDL capabilities. It has been programmed to expect schema changes and handle them seamlessly. ### Step 1. Review the schema created by sysbench + On [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB server instance. ```bash mysql --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql [(none)] > ``` + Delete all rows and check the definition of table `sbtest1000.sbtest9`. There are four columns defined in the table, `id` is the primary key. ```sql DELETE FROM sbtest1000.sbtest9; DESC sbtest1000.sbtest9; ``` ```output +-------+-----------+------+------+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+------+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | k | int | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | | +-------+-----------+------+------+---------+----------------+ 4 rows in set (0.00 sec) ``` ### Step 2. Prepare for online schema change + Turn off the [metadata lock](https://docs.pingcap.com/tidb/stable/system-variable-reference/#tidb_enable_metadata_lock) so that the DDL statements will not be blocked by on-going DML transactions on the same tables. ```sql SET GLOBAL tidb_enable_metadata_lock = OFF; SHOW VARIABLES LIKE "tidb_enable_metadata_lock"; EXIT ``` + Review the provided mini Java program, it performs the simple insertion operations endlessly. And most importantly, it expects the error `8028` and is able to retry the transaction, in short, it is compatible with TiDB's online schema change behavior. Error code `8028`, with the message Information schema is changed, occurs when the program attempts to commit the transaction but detects a schema definition different from the one at the beginning of the transaction. ```bash cat InsertJob.java | egrep -A1 -B1 8028 ``` ```output } catch (SQLException e) { if (e.getErrorCode() == 8028) { System.out.println("Schema mutation encountered, retry ..."); ``` ### Step 3. Start the insertion workload + On [Terminal 1](ssh://monitor1-ssh#1), run following commands to start the insertion workload, it won't stop. Keep it running. Proceed to the next step. ```bash rm -f InsertJob.class javac -cp .:mysql-connector-java-5.1.36-bin.jar InsertJob.java sleep 1 java -cp .:mysql-connector-java-5.1.36-bin.jar InsertJob ${HOST_DB1_PRIVATE_IP} 4000 t1000 ``` ```output Inserting 0 Inserting 1 Inserting 2 Inserting 3 Inserting 4 Inserting 5 Inserting 6 Inserting 7 ... ``` ### Step 4. Perform the online schema change + Based on certain business requirements, you decide to add a new column to table `sbtest1000.sbtest9`, which is `ed VARCHAR(10) DEFAULT 'N/A'`. + Switch to [Terminal 2](ssh://monitor1-ssh#2), connect to TiDB server instance. ```bash mysql --prompt="mysql [\d] > " -h ${HOST_DB1_PRIVATE_IP} -P 4000 -u root ``` ```output mysql [(none)] > ``` + Check the table twice, confirm that the insertion workload is working and the row count is increasing. ```sql SELECT COUNT(1) FROM sbtest1000.sbtest9; SELECT SLEEP(2); SELECT COUNT(1) FROM sbtest1000.sbtest9; ``` ```output +----------+ | COUNT(1) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec) +----------+ | SLEEP(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec) +----------+ | COUNT(1) | +----------+ | 18 | +----------+ 1 row in set (0.00 sec) ``` + Perform the DDL to add a new column to `sbtest1000.sbtest9`. ```sql ALTER TABLE sbtest1000.sbtest9 ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A'); ``` ```output Query OK, 0 rows affected (0.34 sec) ``` + Switch back to [Terminal 1](ssh://monitor1-ssh#1), observe the output, we are expecting an output with `Schema mutation encountered, retry ...`, and the workload is not interrupted. ```output ... Inserting 50 Inserting 51 Schema mutation encountered, retry ... Inserting 52 Inserting 53 Inserting 54 Inserting 55 Inserting 56 ... ``` + Go back to [Terminal 2](ssh://monitor1-ssh#2), confirm that the row count is still increasing. ```sql SELECT COUNT(1) FROM sbtest1000.sbtest9; SELECT SLEEP(2); SELECT COUNT(1) FROM sbtest1000.sbtest9; ``` ```output +----------+ | COUNT(1) | +----------+ | 71 | +----------+ 1 row in set (0.01 sec) +----------+ | SLEEP(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec) +----------+ | COUNT(1) | +----------+ | 74 | +----------+ 1 row in set (0.00 sec) ``` + Verify that the table definition was changed. Note that the column `ed varchar(10)` is the new column. ```sql DESC sbtest1000.sbtest9; ``` ```output +-------+-------------+------+------+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+------+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | k | int | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | | | ed | varchar(10) | YES | | N/A | | +-------+-------------+------+------+---------+----------------+ 5 rows in set (0.00 sec) ``` + At this point, you've witnessed TiDB's Online DDL capability, which allows schema changes without the need to halt any operations. Go to [Terminal 1](ssh://monitor1-ssh#1), and press CTRL-C. Then, exit both terminals.