How does MySQL CREATE and delete temporary tables?

 

1. Introduction:

The MySQL temporary table belongs to the session level. When the session exits, the temporary table is deleted. Temporary tables can have the same name as other tables and are independently maintained in the thd struct. Therefore, different sessions can create temporary tables with the same name and only operate on temporary tables owned by themselves;

 

 

The syntax for creating a temporary table is simple:

 

Root @ test 03:26:44> show create table tmp1 \ G

* *************************** 1. row ***************************

Table: tmp1

Create Table: create temporary table 'tmp1 '(

'A' int (11) not null AUTO_INCREMENT,

'B' int (11) DEFAULT NULL,

'C' int (11) default null,

Primary key ('A ')

) ENGINE = InnoDB default charset = latin1

1 row in set (0.00 sec)

 

After a temporary table is created, two files are generated in the tmp Folder:

# Sql3e95_1a_0.frm

# Sql3e95_1a_0.ibd

 

How does MySQL CREATE and delete temporary tables?

 

2. Create

Execute SQL:

 

Create temporary table 'tmp1' ('A' int (11) not null AUTO_INCREMENT, 'B' int (11) DEFAULT NULL, 'C' int (11) DEFAULT NULL, primary key ('A '));

 

1) breakpoint: ysql_execute_command

 

Mysql_execute_command:

2205 switch (lex-> SQL _command ){

(Gdb)

2532 if (! (Lex-> create_info.options & HA_LEX_CREATE_TMP_TABLE ))

(Gdb) p lex-> create_info.options -------- false in the if statement

$2 = 1

Create_table_precheck ------ check whether you have the permission to create a table and whether the table name already exists on the global linked list (no need to check the temporary table)

Append_file_to_dir ------ Fix names if symlinked tables

 

If (select_lex-> item_list.elements) ------------------- when it is a statement such as create... select, select_lex-> item_list.elements is not 0. Here we only consider the simple case

If (result = new select_create ))

Res = handle_select (thd, lex, result, 0 );

Else

(1) mysql_create_like_table --------------- create table like... similar statements

(2) mysql_create_table --------------- mainly analyzes this function

 

2) breakpoint: mysql_create_table

 

Mysql_create_table

Mysql_create_table_no_lock

Check_engine

File = get_new_handler

3842 set_table_default_charset (thd, create_info, (char *) db );

3844 if (mysql_prepare_create_table (thd, create_info, alter_info,

3854 path_length = build_tmptable_filename (thd, path, sizeof (path); ----- create a temporary table File Name: # SQL {process id }_{ thread_id }_{ temporary table integer id of the current thread thd-> tmp_table}

3978 rea_create_table -------------------------------------------------------------------- create frm files and ibd files

3986 open_temporary_table ------------------------------------------------------------- open a temporary table

(1) construct the table and table_share struct

(2) Add the table struct to the thd-> temporary_tables linked list.

4009 error = write_create_table_bin_log ---------------------------------------------- write binlog

 

3. delete a temporary table

Manually execute drop table tmp1

 

Mysql_execute_command

Case SQLCOM_DROP_TABLE:

Mysql_rm_table

Mysql_rm_table_part2

For (table = tables; table = table-> next_local)

Drop_temporary_table --------------------------- query temporary tables from thd-> temporary_tables

Call close_temporary_table to close and delete temporary table files, and delete corresponding nodes from thd-> temporary_tables.

If (! Drop_temporary) ------------------------------- When deleting a non-temporary table, execute the following logic

----------------------------------------

4. When the session exits.

Look at the stack:

Breakpoint 16, rm_temporary_table (base = 0xc8c560, path = 0x1427c10 "/u01/ mysql-5148.stock/tmp/# sql3e95_1d_0") at SQL _base.cc: 5634

5634 bool rm_temporary_table (handlerton * base, char * path)

(Gdb)

5641 strmov (ext = strend (path), reg_ext );

(Gdb) bt

#0 rm_temporary_table (base = 0xc8c560, path = 0x1427c10 "/u01/mysql-5148.stock/tmp/# sql3e95_1d_0") at SQL _base.cc: 5641

#1 0x000000000000005f6eaa in close_temporary (table = 0x1427030, free_share = true, delete_table = true) at SQL _base.cc: 1928

#2 0x000000000000005f725f in close_temporary_tables (thd = 0x14065f0) at SQL _base.cc: 1549

#3 0x0000000000592d9b in THD: cleanup (this = 0x14065f0) at SQL _class.cc: 967

#4 0x000000000000005a3579 in unlink_thd (thd = 0xc8c560) at mysqld. cc: 1858

#5 0x000000000000005a35dc in one_thread_per_connection_end (thd = 0xc8c560, put_in_cache = 16) at mysqld. cc: 1945

#6 0x000000000000005ac208 in handle_one_connection (arg = 0x14065f0) at SQL _connect.cc: 1141

#7 0x0000003e638064a7 in start_thread () from/lib64/libpthread. so.0

#8 0x0000003e630d3c2d in clone () from/lib64/libc. so.6

#9 0x0000000000000000 in ?? ()

 

At the end of the session, THD: cleanup is called to clean up the temporary table.

 

From the path to record growth