MySQL temporary table

Temporary table

This article carries the following questions (Investigation ):

Why can the same name be used?

Which table is processed first?

In master-slave backup, how does one deal with two different sessions, the temporary table with the same name, and the update on the slave machine ?.

Use the MyISAM engine version 5.1.48.

 

Code Analysis:

Lex-> create_info.options & ha_lex_create_tmp_table temporary table and normal table judgment: equal to 1 is temporary table, 0 is normal table.

 

./SQL/SQL _parse.cc/mysql_execute_command ()

Case sqlcom_create_table:

|! (Lex-> create_info.options & ha_lex_create_tmp_table) // entity table

| End_active_trans (THD) // do implicit commit

| Create_table_precheck ()

| Find_table_in_list (table_list *) // check whether the table already exists. If it is Temp table, do not check it here.

| If (select_lex-> item_list.elements)

| Newselect_create ()

| Else

| Mysql_create_table ()

 

./SQL/SQL _table.cc/mysql_create_table ()

| If (! (Create_info-> options & ha_lex_create_tmp_table) // entity table

| Lock_table_name_if_not_cached ()

| If (! Name_lock) write_create_table_bin_log () // table cache exists but it has not been written to BINLOG

| Mysql_create_table_no_lock ()

| Mysql_prepare_create_table ()

| If (create_info-> options & ha_lex_create_tmp_table) // temp table

| Build_tmptable_filename (THD, path, sizeof (PATH); // create the temp table name:/tmp/# SQL + current_pid (hexadecimal) + thd-> thread_id (an ID specified by the server, instead of the actual thread id value during server running) + thd-> tmp_table + reg_ext (suffix such. FRM ). That is to say, thread_id increases from 0 when the server receives a client connection.

| Create_info-> table_options | = ha_create_delay_key_write;

| Else // entity table

| Build_table_filename ()

①| If (temp & find_temporary_table () // check whether the temp table exists. It is compared with all temp table lists through dB + table length, then compare a key value, which consists of server_id and variables. determined by pseudo _ thread_id.

| Write_create_table_bin_log ()

| If (temp table)

| Open_temporary_table (1) // Open table and put in temporary table list (THD-> temporary_tables)

| Write_create_table_bin_log ()

 

Case sqlcom_drop_table:

| If (! Lex-> drop_temporary)

| Determine whether temporarytable is explicitly written in the statement

| Mysql_rm_table ()

| Mysql_rm_table_part2 ()

| For (Table-> next_local)

| Drop_temporary_table () // find and delete from Thr-> temporarylist

| Delete an object table

 

In summary, we can know that the temporary table name can have the same name as the common table because they are stored in different locations, temp table is stored in thr-> table_list (this is why temp table is relative to session. After each session ends, temp table is cleared, because after processing a client request, the thread is also recycled.) General tables are stored in the global table list.

For drop, we can find that droptemp table is used to traverse the table in thr-> table_list. Therefore, temp table has priority over general tables.

 

MySQL master-slave backup principle:

Slave has two threads: I/O and SQL. Connect to the master through the I/O thread, read the BINLOG file of the master, and then add the received log content to the end of the relay-log file of the slave in sequence. And record the BINLOG file name and location of the read master to the master-info file.

When the slave SQL thread detects that relay-log is updated, it will immediately parse the relay-log Content and perform the same operations as the master on the slave side.

 

Code entry:

SQL _parse.cc: mysql_execute_command ()

Case sqlcom_slave_start:

{

Pthread_mutex_lock (& lock_active_mi );

Start_slave (THD, active_mi, 1/* netreport */);

| Start_slave_threads ()

| Start_slave_thread (handle_slave_io ,...)

| Start_slave_thread (handle_slave_ SQL ,...)

Pthread_mutex_unlock (& lock_active_mi );

Break;

}

 

Slave. CC: handle_slave_io ()

| Safe_connect () // connect master host

| Register_slave_on_master ()

| Read_event () // read Master event

| Queue_event () // queueing master event to the relay log

| Flush_master_info () // update master.info

 

Slave. CC: handle_slave_ SQL ()

| Init_slave_thread (THD)

②| Thd-> thread_id = thd-> variables. pseudo do_thread_id = thread_id ++; // The thread_id of slave_ SQL _thread is manually changed to a global variable.

| Thd-> temporary_tables = RLi-> save_temporary_tables; // restore temp tables

| Set_thd_in_use_temporary_tables (RLI); // (re) set SQL _thd in use forsaved temp tables

| Table-> in_use = RLi-> SQL _thd; // rename the owner of these tables as SQL _thread

| Init_relay_log_pos () // open the relay log

| Check_temp_dir ()

| While (! SQL _slave_killed ())

| Exec_relay_log_event () // read andexecutes relay events

| Apply_event_and_update_pos ()

| Ev-> apply_event (RLI)

| Log_event.cc: query_log_event: do_apply_event ()

| Thd-> variables. pseudo do_thread_id = thread_id; // For temp tables, thread_id is obtained from the relaylog file thread_id

| Mysql_parse ()

| Mysql_execute_command (THD)

| Ev-> update_pos (RLI)

| RLi-> save_temporary_tables = thd-> temporary_tables; // SQL _thread killed

 

SQL _parse.cc: mysql_execute_command ()

Case sqlcom_slave_stop:

| Stop_slave (THD, active_mi, 1/* NET Report */);

| Terminate_slave_threads ()

| Terminate_slave_thread (io_thd );

| Terminate_slave_thread (SQL _thd );

 

SQL/mysqld. CC: Main ()

| Mysql_rm_tmp_tables () // delete all temp tables at startup

 

The following analyzes the processing mechanism of temp table on the master and slave nodes:

First, let's summarize the processing mechanism of temptable on the master machine: temp table is effective for sessions. At the end of the session, all resources occupied by temp table will be Del. This resource includes memory resources and corresponding table files and data files under the/tmp directory (the named of this file is: # SQL _pid_threadid_tableid, which is the corresponding sessionthread for Master thread_id, slave is more like SQL _slave_thread), so different sessions in a service can define the same table. Because of their different thread_id, the same session can also create multiple different tables, because tableid is different. In addition, temp
Table is saved in thr-> tablelist, while regular table is saved in global table list, so temp table can have the same name as regular table, in addition, when you open the table, you always traverse Thr-> tablelist first, that is, when there is a temp table, the temp table is always prioritized.

The above mechanisms are the same on slave. The only difference is that there is only one SQL _slave_thread on slave, instead of having multiple client threads on the master to process different sessions. That is, if there is no different thread_id on the slave, a conflict occurs when a file is created under the/tmp directory (the temp table with the same name of different clients on the master?

MySQL solves this problem by: when creating temptable, it determines whether there is an existing same name (same database, same table, same thread_id, same server_id, and above ).①Process ). When the name is named, only tableid is auto-incrementing, and thread_id is unchanged. It is determined during slave SQL initialization, as shown in the preceding figure.②As shown in, it is added only when the slave is restarted. However, they are stored in thd-> variables. pseudo do_thread_id to determine whether they belong to the same session. If they are different, tableid ++ is essentially distinguished by saving the thread_id of the master.

 

When stop slave and start slave are used, what is the processing of temporary tables?

When you stop slave, slave_ SQL _thread will be stopped. At this time, thd-> temptablelist will be saved to the relayinfo structure (in memory ), therefore, all subsequent operations on the temp table can be executed normally. In this case, the temp file will not be deleted, and the temp file will not be deleted when you start slave.

When slave server end (Termination )?

When the entire slaveserver end (Termination), because all the memory resources are cleared, the original temp table list in slave is also lost, in addition, the system will delete all temp files during restart. So if the master re-operates the temp table, slave will report an error. For example:

[Error] slave SQL: Error 'table' test. t_355 'doesn' t exist 'on query. default Database: 'test '. query: 'insertinto t_355 values (4) ', error_code: 1146

[Warning] slave: Table 'test. t_355 'doesn' texist error_code: 1146

[Error] Error running query, slave sqlthread aborted. fix the problem, and restart the slave SQL thread with "Slave start". We stopped at log 'mysql-bin.000020 'position 1094.

 

The essence of this problem is that the temp table list of the memory is recycled, that is, the temp. frm does not have the corresponding memory structure. And server_id and thread_id are lost.

Possible solutions:

1. Write the memory temptable structure to the disk.

After creating the temp table, write the structure to a separate file on the disk (each temp table has a corresponding file, because when a considerable temp table is deleted, files can also be deleted ).

When the slave server is started, the temp table data file (. frm,. myi,. MYD) under the tmpdir directory is not deleted)

Then, when slave_ SQL _thread is started, load the table structure file previously written to the disk to thd-> temp table.

Advantage: it is easy to implement and does not need to modify the file name because the table structure contains this information.

Disadvantage: the written file may be large, and the table structure contains a large number of pointers, which should be read to the content pointed to. The temp table name is inconsistent with the original one, although this may not affect the operation.

2. Record the BINLOG event of create temptable and redo these events at startup.

This record is written to a new file when you execute the create temp table event, because this event contains all information about create temp, DB, table, server_id, thread_id, only the table data information is needed.

Then execute these events when starting slave_ SQL _thread, and some new data files will be generated. frm.

Finally, replace the original. frm data with the new file created by the event (delete the new file first, and then change the original file name to the new file name ).

Advantage: all processes can be implemented by the original system module, so the implementation may be relatively simple.

Disadvantage: del immediately after the event creates a file, which is a waste. In addition, it must be determined that the event type is relative to slave and is the create temp event (this does not have clear information in the event and may need to be parsed) for saving. The modification may have other effects.

Details (improvements ):

1. Before the slave executes the event cyclically (while (! SQL _slave_killed () creates a logfile, which records the temping between tempfile name and {relay_binlog_file, POS}. This file corresponds to a map in the memory, after each slave executes an event, it determines whether the event is "create temporary tabe". Here, we directly compare the statement in the event body to a string (or directly compare the statement under "temp dir. FRM file: Find the recently created file name in the map. If not, this temp
Table is created by the current event, so this relationship is recorded in the map and also written to the file). If so, call build_tmptable_filename (THD, path, sizeof (PATH )) (In this case, the PID is the PID of the server, and thread_id is also available, table_id must be-1) Get the temptable file name, put this relationship {Temp File Name and {relay_binlog_file, pos} records our logfile. (If the intermediate temp table is deleted, this file is not modified, because we will
Table name to find value {relay_binlog_file, POS })

2. When the slave server is started (before mysql_rm_tmp_tables), move the temp file of the file to another directory.

3. Read the logfile created when reading 1 to map, and then get the name of the moved file to search in map to obtain its corresponding value. (This process is a loop)

4. Construct io_cache using the obtained value, and then read_event_log to obtain the event of the previous create temp table.

5. Finally, run the event: ev-> apply_event (RLI) -- à query _ log_event: do_apply_event ()

During master dump, all the temp table files of the master must be deleted. In this case, the temp table files of the slave should also be deleted, so this information should be captured, and perform the same operations as the master. In this case, slave should delete the temp table file.

Note: I would like to express my sincere gratitude to you for your guidance and help. Haha!