# Exercise 3: Exception Handling 
## Prerequisites
+ You had completed exercise [Creating a TiDB Playground Cluster](https://eng.edu.pingcap.com/unit/view/id:3899)
+ Your TiDB service listens at port 4000.
+ The Java SDK environment is already configured on the operating system.
+ [git](https://git-scm.com/) is already installed on the operating system.


## Section 1: Preparation
1. Clone example scripts repository to the practice node (e.g: Your local macOS, Linux or VM in the cloud).
```
$ git clone https://github.com/pingcap/tidb-course-201-lab
```

2. Go to working directory: `tidb-course-201-lab/scripts`.
```
$ cd tidb-course-201-lab/scripts
```

3. If you have TiDB Cloud Developer Tier and you prefer to do the exercise on a Cloud node like Amazon EC2, please jump to *Section 3: Try it out on TiDB Cloud*


## Section 2: Exception handling
1. Go through the sample code and note line `if (e.getErrorCode() == 8028)`- [DemoJdbcPreparedStatement8028.java](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoJdbcPreparedStatement8028.java), so that you can understand how to handle exceptions to error code 8028 in this exercise.

2. Check the value of the system variable `tidb_enable_metadata_lock`. If the value is `ON`, update the value to `OFF`.
   ```sql
   SHOW VARIABLES LIKE "tidb_enable_metadata_lock";
   SET GLOBAL tidb_enable_metadata_lock = OFF;
   ```
   ```
   tidb:4000> SHOW VARIABLES LIKE "tidb_enable_metadata_lock";
   --------------
   SHOW VARIABLES LIKE "tidb_enable_metadata_lock"
   --------------

   +---------------------------+-------+
   | Variable_name | Value |
   +---------------------------+-------+
   | tidb_enable_metadata_lock | ON |
   +---------------------------+-------+
   1 row in set (0.00 sec)

   tidb:4000> SET GLOBAL tidb_enable_metadata_lock = OFF;
   --------------
   SET GLOBAL tidb_enable_metadata_lock = OFF
   --------------

   Query OK, 0 rows affected (0.01 sec)
   ```

3. Run demo script and found that 192,000 rows are successfully inserted and `NAME1` is all `BEFORE-DDL`.
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local
```

4. Run demo script again. After `./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` starts running, open a new terminal to run the `./07-demo-online-ddl-add-column-02-workload-after.sh`. Then, check the results in the terminal 1.  You can find `ERROR 8028` appears in the output of the terminal 1, and execution is interrupted.
+ terminal 1:
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local
```
+ terminal 2:
```
$ ./07-demo-online-ddl-add-column-02-workload-after.sh
```

5. Run demo script again. Pass the `8028` as a parameter to `./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh`. After `./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh` starts running, open a new terminal to run the `./07-demo-online-ddl-add-column-02-workload-after.sh`. Then, check the results in the terminal 1.  You can find `ERROR 8028` appears in the output of the terminal 1, but execution is not interrupted. Also, after `ERROR 8028` appears, `AFTER-DDL` displays in the field `name1`. The `BEFORE-DDL` line inserted at the end of the output is still 192,000.
+ terminal 1:
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local 8028
```
+ terminal 2:
```
$ ./07-demo-online-ddl-add-column-02-workload-after.sh
```

6. Compare the differences in the `ERROR 8028` content in the output of the two runs of Step 3 and Step 4. The difference in the number of rows they insert `BEFORE-DDL` is because the [DemoJdbcPreparedStatement8028.java](https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/DemoJdbcPreparedStatement8028.java) run in step 4 contains an exception handling method, so that the successful insertion can be guaranteed when `ERROR 8028` occurs.

## Section 3: Try it out on TiDB Cloud
1. Until now, our exercises are targeting local TiDB Playground cluster. If you completed the [TiDB Architecture Basics Exercise 1a](https://eng.edu.pingcap.com/unit/view/id:2466), you can change the target database to TiDB Cloud Developer Tier. 
	+ Note: Each subsequent step requires you to do it on a practice node in the same Cloud Provider Region (e.g: Amazon EC2 instance) as your TiDB Cloud Developer Tier. Otherwise, the response latency may suffer. 

2. Pass the `cloud` as a parameter to run the demo against TiDB Cloud. Before running the script, make sure the TiDB Cloud cluster has been started and then set environment variables for TiDB Cloud:
```
$ export TIDB_CLOUD_HOST=<hostname> 
$ export TIDB_CLOUD_USERNAME=<username> 
$ export TIDB_CLOUD_PASSWORD=<password> 
$ export TIDB_CLOUD_PORT=<port> 
```
And then, run the script.
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh cloud
```
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh cloud 8028
```

## Exception handling sample output
```
$ ./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
Connection established.
preparing
preparing
preparing
preparing
preparing
populating
populating
populating
populating
populating
populating

/* Executing query: select count(*) as "|320|" from test.seed; */
	Row#, |320|
	1) 320

/* Executing query: DESC test.target_table; */
	Row#, Field, Type
	1) id, bigint(20)
	2) name1, char(20)
Main stream workload ...0
Main stream 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, 960
Main stream workload ...1
Main stream 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, 1920
Main stream workload ...2
Main stream workload commit ...

(Omit intermediate output)

/* 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, 191040
Main stream workload ...199
Main stream 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, 192000
I: 199
Turn on autocommit.
Connection closed.
```

+ terminal 1:
```
$ ./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
Connection established.
preparing
preparing
preparing
preparing
preparing
populating
populating
populating
populating
populating
populating

/* Executing query: select count(*) as "|320|" from test.seed; */
	Row#, |320|
	1) 320

/* Executing query: DESC test.target_table; */
	Row#, Field, Type
	1) id, bigint(20)
	2) name1, char(20)
Main stream workload ...0
Main stream 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, 960
Main stream workload ...1
Main stream 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, 1920
Main stream workload ...2
Main stream workload commit ...

(Omit intermediate output)

/* 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, 145920
Main stream workload ...152
Main stream 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.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
	at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1612)
	at DemoJdbcPreparedStatement8028.main(DemoJdbcPreparedStatement8028.java:144)
I: 152
Turn on autocommit.
Connection closed.
```
+ terminal 2:
```
$ ./07-demo-online-ddl-add-column-02-workload-after.sh
Field	Type	Null	Key	Default	Extra
id	bigint(20)	NO	PRI	NULL	
name1	char(20)	YES		NULL	
name2	char(40)	YES		NULL	
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
AFTER-DDL	AFTER-DDL	16
BEFORE-DDL	NULL	116160
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
BEFORE-DDL	NULL	117120
AFTER-DDL	AFTER-DDL	32

(Omit intermediate output)

|NAME1|	|NAME2|	|AFTER-DDL-GOAL: 1600|
BEFORE-DDL	NULL	145920
AFTER-DDL	AFTER-DDL	1584
|NAME1|	|NAME2|	|AFTER-DDL-GOAL: 1600|
AFTER-DDL	AFTER-DDL	1600
BEFORE-DDL	NULL	145920
```


+ terminal 1:
```
$ ./11-demo-jdbc-prepared-statement-online-ddl-01-show.sh local 8028
Connection established.
preparing
preparing
preparing
preparing
preparing
populating
populating
populating
populating
populating
populating

/* Executing query: select count(*) as "|320|" from test.seed; */
	Row#, |320|
	1) 320

/* Executing query: DESC test.target_table; */
	Row#, Field, Type
	1) id, bigint(20)
	2) name1, char(20)
Main stream workload ...0
Main stream workload commit ...

(Omit intermediate output)

Main stream 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.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
	at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1612)
	at DemoJdbcPreparedStatement8028.main(DemoJdbcPreparedStatement8028.java:144)
8028 (schema mutation) encountered, backoff...
DO anything in reaction to error, in this example we continue our workload.
Main stream workload ...160
Main stream 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) AFTER-DDL, 848
	2) BEFORE-DDL, 154560
Main stream workload ...161
Main stream workload commit ...


(Omit intermediate output)

/* 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) AFTER-DDL, 1520
	2) BEFORE-DDL, 192000
I: 199
Turn on autocommit.
Connection closed.
```

+ terminal 2:
```
% ./07-demo-online-ddl-add-column-02-workload-after.sh
Field	Type	Null	Key	Default	Extra
id	bigint(20)	NO	PRI	NULL	
name1	char(20)	YES		NULL	
name2	char(40)	YES		NULL	
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
BEFORE-DDL	NULL	111360
AFTER-DDL	AFTER-DDL	16
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
AFTER-DDL	AFTER-DDL	32
BEFORE-DDL	NULL	111360

(Omit intermediate output)

AFTER-DDL	AFTER-DDL	1568
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
AFTER-DDL	AFTER-DDL	1584
BEFORE-DDL	NULL	139200
|NAME1|	|NAME2|	|AFTER-DDL: 1600|
AFTER-DDL	AFTER-DDL	1600
BEFORE-DDL	NULL	140160
```