MySQL · 捉蟲動態 · 臨時表操作導致主備不一致 · 數據庫內核月報

## bug描述在binlog_format=row模式下，事務中create或drop臨時表后，后面再執行DML（影響多行的DML)如果失敗，那么失敗的DML會回滾，但DML仍然記錄了binlog。這個 binlog 應用到備庫后會導致主備不一致。此bug已提給官方[bug#76940](http://bugs.mysql.com/bug.php?id=76940)。以下是重現的測例：主庫執行 ~~~ create table t1(c1 int primary key) engine=innodb; insert into t1 values(1),(2),(3),(4),(5); create table t2 (c1 int, c2 int, foreign key(c2) references t1(c1)) engine=innodb; insert into t2 values(1,1),(2,2),(5,5); create temporary table tmp as select * from t1; begin; drop temporary table if exists tmp; delete from t1 where c1 > 2; --delete 失敗： ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`zy`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`)) commit; mysql> select * from t1; +----+ | c1 | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ ~~~ 備庫結果 ~~~ mysql> select * from t1; +----+ | c1 | +----+ | 1 | | 2 | | 5 | +----+ ~~~ 查看主庫生成的binlog，`delete from t1 where c1 > 2`?失敗了也記入了binlog。 ~~~ BEGIN /*!*/; # at 1226 #150515 17:27:07 server id 1979399682 end_log_pos 1349 Query thread_id=6263054 exec_time=0 error_code=0 SET TIMESTAMP=1431682027/*!*/; SET @@session.pseudo_thread_id=6263054/*!*/; DROP TEMPORARY TABLE IF EXISTS `tmp` /* generated by server */ /*!*/; # at 1349 # at 1388 #150515 17:27:07 server id 1979399682 end_log_pos 1388 Table_map: `zy`.`t1` mapped to number 42174 #150515 17:27:07 server id 1979399682 end_log_pos 1427 Delete_rows: table id 42174 flags: STMT_END_F BINLOG ' 67tVVRMCPvt1JwAAAGwFAAAAAL6kAAAAAAEAAnp5AAJ0MQABAwAA 67tVVRkCPvt1JwAAAJMFAAAAAL6kAAAAAAEAAf/+AwAAAP4EAAAA '/*!*/; ### DELETE FROM zy.t1 ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### DELETE FROM zy.t1 ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ # at 1427 #150515 17:27:09 server id 1979399682 end_log_pos 1494 Query thread_id=6263054 exec_time=0 error_code=0 SET TIMESTAMP=1431682029/*!*/; COMMIT ~~~ ## bug分析 binlog有兩個cache用來緩存事務的binlog。 ~~~ binlog_cache_data stmt_cache; //存放非事務表和臨時表binlog binlog_cache_data trx_cache; //存放事務表binlog ~~~ 事務和語句回滾時應清理相應的cache, 事務提交時cache會刷入binlog文件中。臨時表在 drop 或 create 時不管成功還是失敗都會記binlog。當 drop 或 create 臨時表操作和其他DML在一個事務中時，drop 或 create 臨時表不管成功還是失敗都會記binlog。查看源碼中邏輯是只要事務中出現過 drop 或 create 臨時表操作，那么事務后來的語句不管成功還是失敗binlog cache都不會清理(參考函數`binlog_rollback`和`binlog_truncate_trx_cache`)。對于前面的例子，當事務執行到以下語句時，由于違反引用約束失敗語句回滾時trx_cache應該清理。 delete from t1 where c1 > 2; 因此 delete 3,4 兩條記錄的binlog是應該不記入binlog的。 ## bug修復方法當 drop 或 create 臨時表操作和其他DML在一個事務中時，如果當前執行的語句不是 drop 或 create 臨時表并且失敗，則 binlog cache 應該清理。如果當前執行的語句是drop或create臨時表，不管成功還是失敗，cache都不用清理，都應記入binlog。