# Global Temporary Table ### Overview + In this exercise, you gain some experience on the global temporary table in TiDB. Along the steps, you will turn off the auto commit feature during this exercise. ### 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. ### 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. ### Duration + This exercise should take you approximately 15 minutes to complete. ### Tasks 1. Connect to the TiDB server. 2. Check and understand the demo script. 3. Use the global temporary table. ### Solution 1. Connect to the TiDB server. + (a) For TiDB Playground cluster users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` + (b) For TiDB Cloud Serverless Tier users: ``` $ cd tidb-course-201-lab/scripts $ ./connect-cloud.sh ... tidb:4000> ``` 2. Check and understand the demo script `07-demo-global-temp-table.sql`: Review the demo script content. Pay attention to the `manual commit` step, this step actually clears the rows in `test.temp2`. And, we will make a total of three attempts to create a global temporary table. Attempt 1 will fail, attempt 2 will succeed, and attempt 3 will fail, try to explain why by going through the comments: ``` tidb:4000> SYSTEM cat 07-demo-global-temp-table.sql /* source 07-demo-global-temp-table.sql */ /* Setup: Create a global temporary table */ DROP TABLE IF EXISTS test.temp2, test.perm3; /* Create global temporary table attempt 1, fail with error code 1064 */ CREATE GLOBAL TEMPORARY TABLE test.temp2 (id BIGINT); /* Create global temporary table attempt 2 */ CREATE GLOBAL TEMPORARY TABLE test.temp2 (id BIGINT) ON COMMIT DELETE ROWS; /* Turn off autocommit for current session */ SET @@autocommit = 'OFF'; /* Insert data into the global temporary table */ INSERT INTO test.temp2 VALUES (10); /* Query 1: Check the data in the global temporary table, expecting 10 */ SELECT * FROM test.temp2; /* Manual commit, the data is cleared */ COMMIT; /* Query 2: Check the data in the global temporary table again */ SELECT * FROM test.temp2; /* Create a permanent table */ CREATE TABLE test.perm3 (id BIGINT); /* Create global temporary table attempt 3, error code 1050 is expected */ CREATE GLOBAL TEMPORARY TABLE test.perm3 (id BIGINT) ON COMMIT DELETE ROWS; ``` 3. Run the demo script `07-demo-global-temp-table.sql` and observe the results. (use the TiDB Playground cluster connection as an example). ```sql SOURCE 07-demo-global-temp-table.sql ``` ``` tidb:4000> SOURCE 07-demo-global-temp-table.sql -------------- DROP TABLE IF EXISTS test.temp2, test.perm3 -------------- Query OK, 0 rows affected, 2 warnings (0.01 sec) -------------- CREATE GLOBAL TEMPORARY TABLE test.temp2 (id BIGINT) -------------- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 52 near ""GLOBAL TEMPORARY and ON COMMIT DELETE ROWS must appear together -------------- CREATE GLOBAL TEMPORARY TABLE test.temp2 (id BIGINT) ON COMMIT DELETE ROWS -------------- Query OK, 0 rows affected (0.75 sec) -------------- SET @@autocommit = 'OFF' -------------- Query OK, 0 rows affected (0.00 sec) -------------- INSERT INTO test.temp2 VALUES (10) -------------- Query OK, 1 row affected (0.00 sec) -------------- SELECT * FROM test.temp2 -------------- +------+ | id | +------+ | 10 | +------+ 1 row in set (0.00 sec) -------------- COMMIT -------------- Query OK, 0 rows affected (0.01 sec) -------------- SELECT * FROM test.temp2 -------------- Empty set (0.00 sec) -------------- CREATE TABLE test.perm3 (id BIGINT) -------------- Query OK, 0 rows affected (0.54 sec) -------------- CREATE GLOBAL TEMPORARY TABLE test.perm3 (id BIGINT) ON COMMIT DELETE ROWS -------------- ERROR 1050 (42S01): Table 'test.perm3' already exists ``` 4. Insert 1 row into `test.temp2`, keep the transaction active, and check the table content. ```sql SET @@AUTOCOMMIT=OFF; INSERT INTO test.temp2 VALUES (1); SELECT * FROM test.temp2; ``` ``` tidb:4000> SET @@AUTOCOMMIT=OFF; -------------- SET @@AUTOCOMMIT=OFF -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> INSERT INTO test.temp2 VALUES (1); -------------- INSERT INTO test.temp2 VALUES (1) -------------- Query OK, 1 row affected (0.00 sec) tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) ``` 5. Open a new terminal window and log in as user `root`. ``` $ ./connect-4000.sh ``` 6. Query global temporary table `test.temp2`. The table exists, and it should be empty. ```sql SELECT * FROM test.temp2; ``` ``` tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- Empty set (0.00 sec) ``` 7. Insert 1 row into `test.temp2`, keep the transaction active, and check the table content. ```sql SET @@AUTOCOMMIT=OFF; INSERT INTO test.temp2 VALUES (2); SELECT * FROM test.temp2; ``` ``` tidb:4000> SET @@AUTOCOMMIT=OFF; -------------- SET @@AUTOCOMMIT=OFF -------------- Query OK, 0 rows affected (0.01 sec) tidb:4000> INSERT INTO test.temp2 VALUES (2); -------------- INSERT INTO test.temp2 VALUES (2) -------------- Query OK, 1 row affected (0.00 sec) tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec) ``` 8. Commit the transaction and check the table content, it should be empty. ```sql COMMIT; SELECT * FROM test.temp2; ``` ``` tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- Empty set (0.00 sec) ``` 9. Go back to the first terminal, issue a query, and then commit and query again. Observe the result. ```sql SELECT * FROM test.temp2; COMMIT; SELECT * FROM test.temp2; ``` ``` tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) tidb:4000> COMMIT; -------------- COMMIT -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> SELECT * FROM test.temp2; -------------- SELECT * FROM test.temp2 -------------- Empty set (0.00 sec) ``` ## Conclusion Unlike the local temporary table, the table definition of a global temporary table is in the same namespace as the permanent table and visible to all sessions, while the data of a global temporary table is visible only in the current transaction. This means when the transaction ends, the data is automatically cleared. ## End of Exercises