CREATE TEMPORARY TABLEとレプリケーション

マスターでこんな処理をしている時の話。

mysql> CREATE TEMPORARY TABLE this_is_tmp_table (..);
mysql> INSERT INTO this_is_tmp_table SELECT .. FROM this_is_origin_table WHERE ..;
mysql> UPDATE this_is_tmp_table SET ..;
mysql> INSERT INTO this_is_summary_table SELECT .. FROM this_is_tmp_table;
mysql> DROP TEMPORARY TABLE this_is_tmp_table;

テンポラリーテーブルを使ってごにょごにょした後に、
テンポラリーじゃないテーブルにデータを書き出すような処理。

テンポラリーテーブルは
・datadirではなくtmpdirにデータ実体が吐かれる(ibdata1は別として)
・CREATE TEMPORARY TABLEしたスレッド以外からは見えない
・CREATE TEMPORARY TABLEしたスレッドが接続を閉じれば消される
ような特性を持っている関係上、↑のような処理をレプリケーションしていると、
CREATE TEMPORARY TABLEからDROP TEMPORARY TABLEの間で
SQL_Threadが終了(STOP SLAVEやmysqldの再起動を含めて)してしまうと、
ステートメントベースのレプリケーションではそのままレプリケーションが再開できなくなる。

たとえば、↑の3行目の処理中にSTOP SLAVEしたりすると、
次にSTART SLAVEした時に

2013-06-18 21:44:30 8865 [ERROR] Slave SQL: Error 'Table 'd1.this_is_tmp_table' doesn't exist' on query. Default database: 'd1'. Query: 'INSERT INTO this_is_summary_table SELECT * FROM this_is_tmp_table', Error_code: 1146

となる。
この動作はbinlog_format = STATEMENTの時に起こり得る。

binlog_format = MIXEDだと、

$ mysqlbinlog -vv relay.000018
..
### INSERT INTO `d1`.`this_is_tmp_table`
### SET
###   @1=999990 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=NULL /* LONGINT meta=96 nullable=1 is_null=1 */
..

のように、テンポラリーテーブルを使ってる周りはレプリケーションアンセーフだと判定して、
ROWモードでの書き出しになる。
なので、この場合は途中で止めてもなんとかなる。

5.1で推しだったbinlog_format = MIXEDは、5.5からは暗黙のデフォルトでは`ない'ので注意。
(binlog_formatの暗黙のデフォルトはSTATEMENTに戻った。5.1の中盤～後半だけ暗黙のデフォルトがMIXED)