# Online DDL ## Overview + In these exercises, you perform Online DDL in TiDB. You learn that Online DDL is a non-blocking asynchronous operation. + In Exercise 1, you become familiar with the DML workload used in these exercises. + In Exercise 2, you run a DML workload and a schema-changing DDL on the same table, resulting in the DML workload being interrupted due to its inability to handle the schema mutation caused by the Online DDL. + In Exercise 3, you run both a DML workload and a schema-changing DDL on the same table, and the DML workload is able to handle the schema mutation caused by Online DDL, thus avoiding business interruptions. ### Notes + Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`. + The "`$`" in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + These exercises can be run on a single machine, such as your laptop. + All scripts and codes in these exercises provide no production warranty, use them at your own risk. ### Assumptions + You are using macOS or Linux. + You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git). + You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes. + You have JDK 8 or later installed. ## Exercise 1: Run Testing DML Workload by Using a Java Program ### Overview + In this exercise, you insert 192,000 rows into a table named `test.target_table` by using 200 transactions. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks 1. Check the Java source code of the demo program. 2. Insert 192,000 rows into TiDB. ### Solution 1. Under `tidb-course-201-lab/scripts` folder, in Java source code `DemoJdbcPreparedStatement8028.java`, you should pay attention to following lines (there is no need to go through all the source code). `sqlCreateTable` defines the schema DDL of `test.target_table`. It has two columns, which are `id` and `name1`. ``` String sqlCreateTable = "CREATE TABLE test.target_table(id BIGINT PRIMARY KEY AUTO_RANDOM, name1 CHAR(20))"; ``` Table `test.seed` is used to provide seed rows for `test.target_table`, and the value for `name1` is always `BEFORE-DDL`. ``` static String MAIN_TASK = "INSERT INTO test.target_table (id, name1) SELECT NULL, name FROM test.seed"; ... String sqlInsertIntoSeed = "INSERT INTO test.seed (name) VALUES ('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL'),('BEFORE-DDL')"; ``` The following block is the primary task: a loop for executing 200 insert transactions, followed by an immediate query to check the row count in `target_table` after each transaction: ``` PreparedStatement psMainInsert = connection.prepareStatement(MAIN_TASK); for (int i = 0; i < 200; i++) { I = i; System.out.println("Main task workload ..." + i); for (int k = 0; k < 3; k++) { psMainInsert.executeUpdate(); } connection.commit(); System.out.println("Main task workload commit ..."); printResultSetStringString( "SELECT name1 AS \"|NAME1|\", COUNT(*) AS \"|BEFORE-DDL-GOAL: 192000|\" FROM test.target_table GROUP BY name1 ORDER BY 1", connection); } ``` 2. Run the demo script `11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` to execute the Java program you inspected in step 1, and observe the results. No errors are expected (use `cloud` option to execute against the TiDB Serverless Tier, use `local` for TiDB Playground Cluster): ``` $ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local TiDB endpoint: 127.0.0.1 TiDB username: root Default TiDB server port: 4000 Security options: Connection established. populating ... /* Executing query: SELECT name1 as "|NAME1|", count(*) as "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 192000 I: 199 Turn on autocommit. Connection closed. ``` ## Exercise 2: Run Testing DML Workload and Change the Target Table Schema at the Same Time ### Overview + In this exercise, you insert 192,000 rows into a table named `test.target_table` by using 200 transactions. At the same time, you change the schema of the `test.target_table` to add a new column. Schema change in TiDB is Online DDL, Online DDL and DML will not block each other. However, the workload of inserting might encounter an exception this time, because the `INSERT` DML encounters a mutated table during its SQL statement execution lifecycle. ### Duration + This exercise should take you approximately 30 minutes to complete. ### Tasks 1. On terminal 1, run the script `11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` to insert 192,000 rows into TiDB table `test.target_table`. 2. While the script `11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` is running, on terminal 2, start another terminal and change the schema of `test.target_table` at the same time. ### Solution 1. Open two terminals. On terminal 1, call the script to run the DML inserting workload; do NOT wait on this terminal for the workload completion, but switch to terminal 2 when the Java program has inserted several hundred rows, for example, when the output shows `Main task workload ...17`, which means the 17th transaction is being committed, this is the right time to switch into terminal 2. (when calling the script, use `cloud` option to execute against the TiDB Serverless Tier, use `local` for TiDB Playground Cluster): ``` $ cd tidb-course-201-lab/scripts $ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local TiDB endpoint: 127.0.0.1 TiDB username: root Default TiDB server port: 4000 Security options: Connection established. populating ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 16320 Main task workload ...17 Main task workload commit ... ... ``` 2. While terminal 1 is busy on inserting rows, in terminal 2, connect to TiDB with mysql-client. + a) For TiDB Cloud Serverless users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-cloud.sh ... tidb-cloud> ``` + b) For TiDB Playground Cluster users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 3. On terminal 2, call script `07-demo-online-ddl-add-column-02.sql` to execute a DDL on `target_table` to add a new column named `name2`. You find that the DDL operation will not be blocked by the DML workload in terminal 1: ``` tidb:4000> source 07-demo-online-ddl-add-column-02.sql Query OK, 0 rows affected (0.52 sec) +-------+------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | name1 | char(20) | YES | | NULL | | | name2 | char(40) | YES | | NULL | | +-------+------------+------+------+---------+-------+ 3 rows in set (0.00 sec) ``` 4. Go back to terminal 1, observe what happened. An error is expected. How many rows had been inserted? What is the error message? And why? Here is the example output, which shows that committing the 103rd transaction failed with error code 8028. Review the error message for more details. The explanation is that the information schema was changed during the execution of the statement (e.g., the table definition may have been updated by other DDLs running in parallel). ``` ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 98880 Main task workload ...103 Main task error. Error: java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] SQLState: HY000 ErrorCode: 8028 java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:806) at DemoJdbcPreparedStatement8028.main(DemoJdbcPreparedStatement8028.java:148) Turn on autocommit. Connection closed. ``` Switch to terminal 2, check the rows count in table `test.target_table`: ``` tidb:4000> SELECT COUNT(*) FROM test.target_table WHERE name1='BEFORE-DDL'; +----------+ | count(*) | +----------+ | 98880 | +----------+ 1 row in set (0.01 sec) ``` On terminal 2, check table schema: ``` tidb:4000> DESC test.target_table; +-------+------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | name1 | char(20) | YES | | NULL | | | name2 | char(40) | YES | | NULL | | +-------+------------+------+------+---------+-------+ 3 rows in set (0.01 sec) ``` Thus, the conclusion is that the Online DDL succeeded, however, the DML workload failed (note that our target was to insert 192,000 rows into `test.target_table`). We can fix that in next exercise. ## Exercise 3: Run Testing DML Workload with Exception Handling and Change the Target Table Schema at the Same Time ### Overview + In this exercise, you insert 192,000 rows into a table named `test.target_table` by using 200 transactions. At the same time, you change the schema of the `test.target_table` to add a new column. + Unlike previous exercise, the DML workload can handle the error code 8028 with the simplest solution, re-executing the failed transaction. ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks 1. Check the Java source code again of the demo program. 2. On terminal 1, run the script `11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` to insert 192,000 rows into TiDB table `test.target_table`. 3. While the script `11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` is running, on terminal 2, start another terminal and change the schema of `test.target_table` at the same time. ### Solution 1. Under `tidb-course-201-lab/scripts` folder, in Java source code `DemoJdbcPreparedStatement8028.java`, pay attention to following lines (there is no need to go through all the source code). `if e.getErrorCode() == 8028`, the program can do something to handle this exception; `for (int i = I; i < 200; i++)` indicates that the handling logic is quite straightforward, i.e. to continue executing the workload from where it left off until 200 transactions are completed. ``` ... if (e.getErrorCode() == errorCodeToHandleOnce) { if (e.getErrorCode() == 8028) { System.out.println("8028 (schema mutation) encountered, backoff..."); } System.out.println( "DO anything in reaction to error, in this example we continue our workload."); Thread.sleep(5000); connection.rollback(); PreparedStatement psMainInsert = connection.prepareStatement(MAIN_TASK); for (int i = I; i < 200; i++) { I = i; System.out.println("Main task workload ..." + i); for (int k = 0; k < 3; k++) { psMainInsert.executeUpdate(); } connection.commit(); System.out.println("Main task workload commit ..."); printResultSetStringString( "SELECT name1 AS \"|NAME1|\", COUNT(*) AS \"|BEFORE-DDL-GOAL: 192000|\" FROM test.target_table GROUP BY name1 ORDER BY 1", connection); } } ... ``` The second argument can tell the program to re-execute the failed transaction upon encountering a specific error code. ``` if (args != null && args.length > 1) { errorCodeToHandleOnce = Integer.parseInt(args[1]); } ``` 2. Open two terminals. On terminal 1, call the script to run the DML inserting workload with `8028` as the second parameter; do NOT wait on this terminal for the workload completion, but switch to terminal 2 when the Java program has inserted several hundred rows, for example, when the output shows `Main task workload ...17`, which means the 17th transaction is being committed, this is the right time to switch into terminal 2. (when calling the script, use `cloud` option to execute against the TiDB Serverless Tier, use `local` for TiDB Playground Cluster): ``` $ cd tidb-course-201-lab/scripts $ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local 8028 TiDB endpoint: 127.0.0.1 TiDB username: root Default TiDB server port: 4000 Security options: Connection established. populating ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 16320 Main task workload ...17 Main task workload commit ... ... ``` 3. While terminal 1 is busy on inserting rows, in terminal 2, connect to TiDB with mysql-client. a) For TiDB Cloud Serverless users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-cloud.sh ... tidb-cloud> ``` b) For local Playground users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 4. On terminal 2, call script `07-demo-online-ddl-add-column-02.sql` to execute a DDL on `target_table` to add a new column named `name2`. You find that the DDL operation will not be blocked by the DML workload in terminal 1: ``` tidb:4000> source 07-demo-online-ddl-add-column-02.sql Query OK, 0 rows affected (0.52 sec) +-------+------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | name1 | char(20) | YES | | NULL | | | name2 | char(40) | YES | | NULL | | +-------+------------+------+------+---------+-------+ 3 rows in set (0.00 sec) ``` 5. Go back to terminal 1, observe what happened. The 8028 error is raised and handled. Here is the example output, in the 46th loop the schema mutation error happened again, then the transaction is re-executed and the workload continued. ``` ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 44160 Main task workload ...46 Main task error. Error: java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] SQLState: HY000 ErrorCode: 8028 java.sql.SQLException: Information schema is changed during the execution of the statement(for example, table definition may be updated by other DDL ran in parallel). If you see this error often, try increasing `tidb_max_delta_schema_count`. [try again later] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:806) at DemoJdbcPreparedStatement8028.main(DemoJdbcPreparedStatement8028.java:148) 8028 (schema mutation) encountered, backoff... DO anything in reaction to error, in this example we continue our workload. Main task workload ...46 Main task workload commit ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 45120 Main task workload ...47 Main task workload commit ... ... ``` At the bottom of terminal 1, the result showed that 192,000 rows had been inserted. ``` ... /* Executing query: SELECT name1 AS "|NAME1|", COUNT(*) AS "|BEFORE-DDL-GOAL: 192000|" FROM test.target_table GROUP BY name1 ORDER BY 1; */ Row#, name1, |BEFORE-DDL-GOAL: 192000| 1) BEFORE-DDL, 192000 Turn on autocommit. Connection closed. ``` Switch to terminal 2, check the rows count in table `test.target_table`: ``` tidb:4000> SELECT COUNT(*) FROM test.target_table WHERE name1='BEFORE-DDL'; +----------+ | COUNT(*) | +----------+ | 192000 | +----------+ 1 row in set (0.12 sec) ``` On terminal 2, check table schema: ``` tidb:4000> DESC test.target_table; +-------+------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | name1 | char(20) | YES | | NULL | | | name2 | char(40) | YES | | NULL | | +-------+------------+------+------+---------+-------+ 3 rows in set (0.00 sec) ``` Thus, the conclusion is that the Online DDL succeeded, and the DML workload also succeeded, they were run in parallel. Last, here is a short [YouTube video introducing TiDB Online DDL](https://www.youtube.com/watch?v=d8jAtSzI6GY) for your reference. ## End of Exercises