mysql temporary table

TEMPORARY TABLE

本文带着如下问题（进行调研）：

为什么可以同名？

优先处理哪个表？

主从备份里怎么处理两个不同session，同名的temporary table，在从机上的更新？。

使用myISAM引擎，版本5.1.48。

 

代码分析：

lex->create_info.options & HA_LEX_CREATE_TMP_TABLE 临时表与正常表的判断：等于1为临时表，0为正常表。

 

./sql/sql_parse.cc/mysql_execute_command()

Case SQLCOM_CREATE_TABLE：

                   |!(lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)   //实体表

                            |end_active_trans(thd)   //do implicit commit

                   |create_table_precheck()

|find_table_in_list(TABLE_LIST *) //检查表是否已经存在，如果是temp table这里不检查

                   |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))  //实体表

                            |lock_table_name_if_not_cached()

                            |if(!name_lock)write_create_table_bin_log() //存在table cache但还没被写到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));  //创建temp table的名称：/tmp/#sql+current_pid（十六进制）+ thd->thread_id（服务器指定的一个id，而不是指服务器运行时的实质thread id值） +　thd->tmp_table + reg_ext（后缀如.frm）。也就是说thread_id是当服务器接收到一个客户端连接时从0开始增加

                                               |create_info->table_options|=HA_CREATE_DELAY_KEY_WRITE;

                            |else   //实体表

                                     |build_table_filename()

①     |if(temp && find_temporary_table())  //检查temp table是否已经存在,它是通过db+table length与所有的temp table list进行比较，然后比较一个key值，这个值是由server_id与variables.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)

                   |在语句中判断是否有明确写出temporarytable

         |mysql_rm_table()

                   |mysql_rm_table_part2()

                            |for(table->next_local)

                                     |drop_temporary_table()   //从thr->temporarylist中查找并删除

                                     |对实体表的删除

 

综上我们可以知道temporary table name是可以与一般的table同名的，因为它们保存在不同的位置，temp table保存在thr->table_list（这也是为什么temp table是相对于session的，每个session结束后temp table会被清空，因为当处理完一个客户端请求后thread也被回收了）；一般的table保存在全局的table list。

         对于drop可以发现在drop的时候是先droptemp table，也就是在thr->table_list中遍历。所以temp table相对于一般的table有优先权。

 

Mysql 主从备份原理：

         Slave有两个线程I/O线程及sql线程。通过I/O线程与master连接，读取master的binlog文件，然后将接收的日志内容依次添加到slave的relay-log文件末。并将读取到的master的binlog文件名和位置记录到master-info文件中。

         Slave的sql线程检测到relay-log更新了，会马上解析relay-log的内容，在slave端执行与master相同的操作。

 

代码入口：

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_thread_id= thread_id++;   //可见slave_sql_thread的thread_id被手动修改为一个全局变量

                   |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;  //将这些表的拥有者，重新指定为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_thread_id=thread_id;// for temp tables,此时的thread_id就是从relaylog文件中获得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()   //启动的时候删除所有的temp table

 

以下分析temp table在主从上的处理机制：

         首先我们总结一下temptable在master机上的处理机制：temp table是session有效的，在session结束时，temp table所占有的资源会被del。这个资源包括内存资源及在/tmp目录下的相应表文件及数据文件（该文件的named为：#sql_pid_threadid_tableid，对于master thread_id就是对应的sessionthread，而slave则更像是sql_slave_thread），所以在一个服务里的不同session可以定义同样的表，因为它们的thread_id不同，同一个session也可以建立多个不同的表，因为tableid不同。另外temp table是保存在thr->tablelist，而常规table是保存在global table list，所以temp table 可以与常规table同名，而且在open table的时候总是先去遍历thr->tablelist，即当存在temp table时，总是优先操作temp table。

         以上这些机制在slave上也是一样的，唯一不同的是在slave上只有一个sql_slave_thread，而不是像master上有多个client thread处理不同的sessions。也就是在slave上没有不同的thread_id，那么在/tmp目录下创建文件（master上不同的client同名的temp table）时就会产生冲突？

Mysql解决这个问题的方法是：在创建temptable时判断是否已经有存在的同名（同db,同table，同thread_id，同server_id，如上在面的①过程）。而取名的时候只有tableid在自增，thread_id是不变的，在slave sql 初始化的时候确定，如上面的②所示，它的增加只在slave被重启的时候。但是它们通过保存在thd->variables.pseudo_thread_id判断是否是属于同一个session，如果是不同的话那么tableid++，其本质还是通过保存了master的thread_id来区分。

 

当stop slave以及start slave，临时表的处理？

         当stop slave的时候会使得slave_sql_thread被stop，此时会把thd->temptablelist保存到relayinfo结构中（内存中），所以后面的对temp table的操作都可以正常执行。此时temp file不会被删除，并且当start slave的时候也不删除temp file。

当slave server end（termination）?

         当整个slaveserver end（termination）的时候，由于所有的内存资源被清除，所以slave中原来的temp table list也丢失了，而且系统在重新启动的时候会删除所有的temp file。所以此时如果master重新操作temp table的话，slave就会报错。如：

[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.

 

该问题的本质是：内存的temp table list被回收了，也就是导致temp.frm没有对应的内存结构。以及server_id，thread_id的丢失。

解决的可能方案：

1、  把内存的temptable结构写到磁盘中

在创建temp table后，把该结构写到磁盘的单独文件（每个temp table一个相应的文件，因为这样当相当的temp table被删除的时候，文件也可以被删除）。

在slave server启动的时候不删除tmpdir目录下的temp table数据文件（.frm,.myi,.myd）

然后在slave_sql_thread启动的时候，加载之前写到磁盘的table结构文件到thd->temp table里。

优点：实现简单，也不需修改文件名，因为table结构里有这个信息了。

缺点：写的文件可能较大，而且table结构里有大量的指针，要读取所指向的内容；temp table name与原始的不一致，虽然这可能不影响运行。

2、  把create temptable的binlog event记录下来，在启动的时候重做这些事件。

在执行create temp table event的时候把该记录，写到一个新的文件中，因为这个event包含了create temp的所有信息，db,table,server_id,thread_id，只差表数据信息。

然后在启动slave_sql_thread的时候先执行这些event，此时会得到一些新的数据文件.frm。

最后把原来的.frm数据替换为新的event刚创建的文件（先删除新的文件，然后修改原来的文件名为新的文件名）。

优点：所有的过程都可以由原来系统的模块来实现，所以实现可能也较简单。

缺点：在event创建了文件之后马上又del，感觉有点浪费。而且必须判断event类型是相对于slave且是create temp event（这个在event中没有明确的信息，可能需要解析）进行保存。修改后可能会有其它影响。

详细（改进）：

1.      在slave进行循环执行event之前（while(!sql_slave_killed())）创建一个logfile，这个文件记录tempfile name与{relay_binlog_file，pos }的对应关系，这个文件对应于内存的一个map，在每次slave执行完一个event之后判断该event是不是create temporary tabe：这里是直接把event body里面的statement语句拿来做一个字符串比较（或者直接把temp dir下的.frm文件，把最近创建的文件名，然后到map中查找，如果没找到，说明此temp table就是当前event创建的，那么把这个关系记录到map中，同时也写到文件中），如果是的话就调用build_tmptable_filename(thd, path, sizeof(path))（此时的pid就是server的pid，thread_id也有，table_id必须-1）获得此temptable的文件名，把这个关系{ temp file name与{relay_binlog_file，pos }}记录到我们的logfile。（如果中间temp table被删除的话不修改该文件，因为后面我们根据已经存在的temp table name来查找value{ relay_binlog_file，pos }）

2.      在slave server启动的时候（mysql_rm_tmp_tables之前）把文件的temp file移到另外一个目录。

3.      读1时所创建的logfile到map中，然后通过得到移动的文件的name，去map中查找，获得它对应的value。（此过程是一个循环）

4.      通过得到的value去构造IO_CACHE，然后read_event_log，获得之前create temp table的event。

5.      最后执行该event：ev->apply_event(rli) --àQuery_log_event::do_apply_event()

在master dump的是时候，master的所有的temp table 文件都要删除，这种情况下slave的temp table文件也应该被删除，所以应该捕捉这个信息，并且进行与master同样的操作，也就是这种情况下，slave就应该删除temp table 文件。

注：以上内容有很多受到海洋同学的指导及帮助，在这里表示真诚的谢意。哈哈！