"Turn" study of MySQL temporary table related problems

This article reprinted from: The introduction of http://itindex.net/detail/10901-mysql-temporary-table problem

Let's look at a few very simple MySQL statements first:

Mysql> Create temporary table tmp (ID int, data char (20)); Query OK, 0 rows Affected (0.00 sec) mysql> CREATE TABLE tmp (ID int, data char (20)); Query OK, 0 rows Affected (0.00 sec) mysql> drop table tmp; Query OK, 0 rows Affected (0.00 sec) mysql> drop table tmp; Query OK, 0 rows Affected (0.00 sec) mysql> drop table tmp; ERROR 1051 (42S02): Unknown table ' tmp '

This is the guiding question raised by Dinkey, and several statements seem simple, but the next series of questions we propose
The research of the line can be all around them!

Seeing the above statement, you can easily produce a question similar to the following:

1. The above statement has created two table named ' TMP ' in a session, but one is temporary
Table, one is the normal table. Here's the question: why temporary table can be associated with the normal table with the same name
Coexistence?

2. The above statement successfully executes two drop TABLE statements, so which table is the object that each statement operates on?
What is the priority relationship between the temporary table with the same name and the normal table?

Very good, with the problem will know the direction of the way! Then we start with these two questions
Our Journey of Discovery!

Research on the problem of same name and priority in stand-alone mode

Let's start with the phenomenon by verifying the outcome of the second question, which table has a higher priority?
For this we design the following statement:

Mysql> Create temporary table tmp (ID1 int, data1 char (20)); Query OK, 0 rows Affected (0.00 sec) mysql> describe tmp;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID1 | Int (11) |     YES | |       NULL | || Data1 | char (20) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> INSERT INTO TMP values (1, "Some"); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from tmp;+------+-------+| ID1 |    Data1 |+------+-------+| 1 | Some |+------+-------+1 row in Set (0.00 sec) mysql> CREATE TABLE tmp (ID2 int, data2 char (20)); Query OK, 0 rows Affected (0.00 sec) mysql> describe tmp;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID1 | Int (11) |     YES | |       NULL | || Data1 | char (20) | YES  | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> INSERT INTO TMP values (2, "Some"); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from tmp;+------+-------+| ID1 |    Data1 |+------+-------+| 1 |    Some | | 2 | Some |+------+-------+2 rows in Set (0.00 sec)

The above statement is simple: first create a temporary table named ' tmp ' and insert a value;
After that, a normal table named ' TMP ' is created, and a value is also insert. Final Select when found, twice
The insert operation is used for temporary table.

At this point we can get the initial impression that temporary table with the same name coexists with normal table.
temporary table higher priority. But don't forget that there is another situation: the table created first has a more
High priority. This conjecture is easy to verify that it's right and wrong, we just need to set the order of the created table
You can change it for a second. The code is not repeated here, giving the result directly: even if temporary table is in the normal table
After creation, operations such as Select,insert,update still take precedence over temporary table.
So we can further guess the drop table, the first drop is also temporary table.
Check it out right away:

/* Immediately before the Code */mysql> drop table tmp; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM TMP; Empty Set (0.01 sec) mysql> describe tmp;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| Id2 | Int (11) |     YES | |       NULL | || data2 | char (20) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2 rows in Set (0.00 sec) mysql> drop table tmp; Query OK, 0 rows Affected (0.00 sec) mysql> Show tables; Empty Set (0.00 sec) mysql> describe TMP; ERROR 1146 (42S02): Table ' test.tmp ' doesn ' t exist 

No problem! Here we have come to the preliminary conclusion from the phenomenon: in the same session under the same name
When temporary table is co-existing with normal table, temporary table is always first manipulated. So
We can go a step further and ask: Why does the temporary table have a higher priority than normal table?
And don't forget, at the beginning of this paragraph, we also raised a question: why in the same session under the same name
Temporary table and normaltable can coexist? It is well known that two temporary table with the same name or
Normal table is not allowed. We can first make a guess: temporary table and Normal table
is stored in a different location. Is this conjecture right? To answer these questions, we have to go to MySQL's source code
To find out what the answer is!

(I inserted a few words: as a no-no MySQL rookie, just got the MySQL source, I like to get the heavenly book,
There is no knowing where to start except worship. After a period of time, I was glad to find that I was finally
It is a good way to get started by looking at the clues and feeling "task-driven +gdb". MySQL full source can be from
Download the following address: http://dev.mysql.com/downloads/)

We can start with the process of creating a table to explore the process (the following code, if not specifically noted,
The comments are the original code comments. )

For statements

Create temporary table tmp (ID int, data char); CREATE TABLE tmp (ID int, data char (20));

Navigate to the Mysql_execute_command () function in./sql/sql_parse.cc.

  Case sqlcom_create_table:  {    ...    if (res= create_table_precheck (THD, Select_tables, create_table))      goto end_with_restore_list;      ...      /* Regular Create *      /if (Create_info.options & ha_lex_create_table_like)        res= mysql_create_like_table (THD , Create_table, Select_tables,                                     &create_info);      else      {        res= mysql_create_table (THD, create_table->db,                                create_table->table_name, &create_ info,                                &alter_info, 0, 0);      }      ...  }

First we look at the implementation of the Create_table_precheck () function in the same file:

...      /* For temporary tables We don't have a to        check if the created table exists      *      /if (! Lex->create_info.options & ha_lex_create_tmp_table) &&          find_table_in_global_list (tables, create _table->db,                                    create_table->table_name))      {error= FALSE;        goto err;      } ...

The Find_table_in_global_list () function essentially calls the./sql/sql_base.cc file
The Find_table_in_list () function. The function is to go through the in-Memory global table list,
Verify that the normal table with the same name already exists. Note that for temporary table, so far it is not done
Duplicate names are checked.

Continue tracing to the mysql_create_table () function in./sql/sql_talbe.cc.
The comments at the beginning are very clear:

  /* Database and name-locking aware wrapper for Mysql_create_table_no_lock (), */

This function is actually an encapsulation of mysql_create_table_no_lock () and handles some lock-up
Mechanism. We continue to trace the Mysql_create_table_no_lock () function to the same file.

...      /* Check if Table exists *  /if (Create_info->options & ha_lex_create_tmp_table)  {    path_length= Build _tmptable_filename (THD, path, sizeof (path));    create_info->table_options|=ha_create_delay_key_write;  }  else  {    path_length= build_table_filename (path, sizeof)-1, DB, alias, Reg_ext,                                      internal_tmp_table? fn_is_tmp:0);  } ...

Here we see a key function Build_tmptable_filename (), which is located in the./sql/sql_table.cc file
, this function is named for temporary table. Inside the function we see the following key code:

...  My_snprintf (P, Bufflen-(P-buff), "/%s%lx_%lx_%x%s",              Tmp_file_prefix, Current_pid,              thd->thread_id, thd->tmp_table++, Reg_ext);

With the above code, the naming convention for temporary table is very clear, where Current_pid is 16 binary
form, thd->thread_id is the client's line program number, thd->tmp_table is the temporary table ordinal, and Reg_ext
is a suffix like *.frm.

Now we go back to function Mysql_create_table_no_lock (), followed by the code just now:

  /* Check If Table already exists */  if ((Create_info->options & ha_lex_create_tmp_table) &&      find_ Temporary_table (THD, DB, table_name))  {    //If a table with the same name is found, execute the error handling code here (not the original comment)  } ...

In the above code, we see a key function find_temporary_table (), which is a large internal function.
An article, it goes through the tmp_table list and checks to see if the temporary table already exists. If everything
No problem, then proceed down:

...  if (rea_create_table (THD, PATH, DB, table_name,                       Create_info, Alter_info->create_list,                       Key_count, Key_info _buffer, file) ...

Here we can see that the function of the rea_create_table () function is to create the actual data file of the normal table.

...  if (Create_info->options & ha_lex_create_tmp_table)  {/    * Open table and put in temporary table list */
   if (! ( Open_temporary_table (THD, PATH, DB, TABLE_NAME, 1)))    {      (void) rm_temporary_table (Create_info->db_type, path);      Goto unlock_and_end;    }    Thd->thread_specific_used= TRUE;  } ...

The above code is for temporary table operations, where the open_temporary_table () function opens a
Temporary table and joins it to the thd->temporary_table queue. Continue down, at the end of the function see a
Sentence code:

  Error= Write_create_table_bin_log (THD, Create_info, internal_tmp_table);

Enter the Write_create_table_bin_log () function, which is a very clear note:

  /*    Don ' t write statement if:    -it is a internal temporary table,    -row-based logging is used and it we CR Eating a temporary table, or    -the binary log is not open.    Otherwise, the statement shall be binlogged.   */

It has been made clear that if the temporary table or row-based Binlog mode is created internally
Create temporary table or Binlog function is not turned on, then do not write binlog, in other cases will be written.

At this point, MySQL is running out of a typical process of creating a table. Summing up the above code, we can answer the first question,
This is the same name as normal table and temporary table coexistence problem. Now we know that normal table and
Temporary table is saved in a different location, temporary table is saved in the thd->temporary_table queue
, and the normal table is stored in the global queue, so that the normal table with the same name as the temporary table
Can coexist. Also, the temporary table is relative to the session, because the corresponding thread at the end of the session
Is recycled, the temporary table corresponding to that thread is freed. Further, from temporary
Table naming rules we can see that each temporary table corresponds to a unique client thread ID, so
It is clear that the temporary table with the same name between the clients is allowed to coexist. And the normal table is obviously in any situation
Conditions are not allowed.

To answer the second question, the priority issue, we just need to look at the process of a drop table, and other operations
The principle is similar. Here we are no longer as detailed as the previous step by step analysis of the source code, directly give the key codes
(In function Mysql_rm_table_part2 (), the function is located in./sql/sql_table.cc)

... error= drop_temporary_table (THD, table); Delete temporary table (non-original comment)      here ... Error= ha_delete_table (THD, Table_type, PATH, DB, Table->table_name,                             !dont_log_query);//delete table contents and index (non-original comment) here ... /* Delete The table definition file */strmov (end,reg_ext);        The following deletes the table definition file (not the original comment) if (! ( New_error=my_delete (PATH,MYF (MY_WME)))        {  some_tables_deleted=1;          new_error= table_triggers_list::d rop_all_triggers (THD, DB,                                                            table->table_name);        } ...

From the above code, it is not difficult to see that the process of the drop table always go first temporary table, then walk Normal table.
This also explains why temporary table has a higher priority than normal table.

Well, so far we have essentially answered two questions raised at the beginning of the article, so that the problem seems to have
The solution was quite satisfactory. But wait a while, all the research we've done is based on the same server, if it's distributed
Type of system, that is, the master-slave mode, what kind of situation will appear? In the following section we continue to explore.

Research on temporary table mechanism under master-slave mode

The first thing we want to illustrate is the implementation mechanism of MySQL master-slave backup. We know that there is one of the many log types in mysql for the
Binlog log type, and all operations involving modifying the database are logged to the Binlog log. Binlog log This
body is divided into two ways: statement-based mode, row-based mode (mixed method can be regarded as a mixture of the two
methods). In master-slave mode, there are two types of servers in a particular distributed server farm: Master (Master service
) and slave (slave server). Master party records its own traces of the data in a way that is recorded in the native Binlog text
, and when slave is connected to master, master initiates a binlog dump to send the local binlog content to the slave party. At this point Slave will start two threads: Slave I/O thread and Slave SQL thread. The Slave I/O
thread reads Binlog content sent from the master's Binlog dump Cheng and writes it to the native relay log
. The Slave SQL thread reads from the local relay log and executes the event that needs to be updated. More specific implementation and matching
details can refer to the Official document: Http://dev.mysql.com/doc/refman/5.1/en/replication.html

Note that there is only one thread for the slave party execution event, and that is the slave SQL thread. Think about what we're doing right now.
Solution, what kind of problem will arise? Recall just the MySQL temporary table naming convention, where one of the items is a thread
Id. Recall just now we said that because temporary table is relative to the session, so the different client can
To create a temporary table with the same name. Here's the problem: Move the scene to master-slave mode, and the master side
After two client, each client creates a temporary table named a. We assume that at this point
If Master's Binlog mode is set to statement-based, both of these table events will be written to Binlog.
Now that the Slave I/O thread detects and reads both events, the Slave SQL thread executes both events. According to
Our idea that at this time slave is unable to differentiate between these two temporary table because the thread ID is the same!

But the actual verification, MySQL can deal with this problem, and not as we expected to error. So MySQL
How is the interior handled? Let's take a closer look at the check in the Build table function Mysql_create_table_no_lock ()
Check the implementation code of the function find_temporary_table () that temporary table name conflict.

...  Key_length= Create_table_def_key (THD, Key, table_list, 1);

Obviously the Create_table_def_key () function is the key to distinguishing each temporary table, and we continue to look at this function
Internal details:

...    Int4store (key + Key_length + 4, thd->variables.pseudo_thread_id);

Here we see a key message: thd->variables.pseudo_thread_id. If you use GDB debugging, we send
Now the value of thd->variables.pseudo_thread_id in the find_temporary_table () function equals Relay-log
The thread ID of the master, which is the value of the thread ID of the client that is recorded in Binlog. Note however that the slave SQL line Cheng
The Init_slave_thread () function called in the initialization function handle_slave_sql () has such a code:

...  Thd->thread_id= thd->variables.pseudo_thread_id= thread_id++;

Here, thd->variable.pseudo_thread_id is initialized to the current thread ID of slave. So when did it get
What about the changes? Continue to see the code:

...  while (!sql_slave_killed (Thd,rli))  {    ...    if (Exec_relay_log_event (Thd,rli))    {      ...    }  } ...

The above code enters the loop that executes relay log. A function was called in exec_relay_log_event ()
Apply_event_and_update_pos (), and this function calls the Ev->apply_event (), which finally calls the
Query_log_event::d o_apply_event (). In this function we see:

...    Thd->variables.pseudo_thread_id= thread_id;  For temp tables ...

It is here that the thd->variables.pseudo_thread_id has been set to the value we want to see. It's amazing!

Temporary table in master-slave mode may cause the problem of different steps

Now let's consider another problem, that is, the temporary table may be caused by the master/slave mode
Problem.

Recall that MySQL created the temporary table procedure. This procedure, in addition to adding temporary table information to the current thread
In addition to the temporary table queue, a temporary data file is created in the/tmp directory.
Such as:

#sql64d6_18_0. frm  #sql64d6_18_0. IBD (InnoDB)

Consider the following scenario: a temporary table is created on the master machine, and the Binlog mode at this time is
Statement-based. So slave read the event, and on the slave also synchronized the operation, that is, the same
Set up a temporary table. At this point, slave suddenly restarts unexpectedly for some reason. We know the server
Reboot will cause all data files in the/tmp folder to be emptied, then on slave, the original temporary table
No longer exists. But the original temporary table on Master is still good! So, if we are in
Any modification to the temporary table on master will cause an error on the slave side, resulting in a message similar to the following:

Error ' Table ' test.tmp ' doesn ' t exist ' on query. Default database: ' Test '. Query: ' INSERT into TMP values (somevalue) '

We know that the data files are present in the/tmp directory until the slave server shuts down until it restarts. The nature of the problem
Is that the temporary table linked list in memory is recycled after Slave server shuts down, resulting in a data file in/tmp
There is no corresponding data structure, then we do not know which of the corresponding client to create the table.

The basic idea to solve this problem is to restore the relevant information in memory in some way when the slave is restarted. One of
The idea is that when slave creates temporary table, we write an extra file to record and maintain the data file with the customer
The corresponding relationship between the thread ID, table name, and database name. Another way of thinking is that when you create a temporary table in slave,
We record the corresponding binlog, and then redo the records at startup. The specific implementation here is no longer detailed
Expand

Have fun!

"Turn" study of MySQL temporary table related problems