There are few issues in DROP and also CREATE temporary table's binary logging.
1. Query log event of DROP temporary table from triggers and stored functions
like in the following
create temporary table tmp_0 (a int) engine=innodb; |
set @@binlog_format=ROW; |
BEGIN;
|
INSERT INTO tt_error_1() VALUES (0), (1), (f_cd_tmp(-2,0)); |
gets cached to split two consecutive Rows_log_event (so slave will stop
- see below) when the binlog format is ROW in the following manner:
| master-bin.000001 | 530 | Annotate_rows | 1 | 609 | INSERT INTO tt_error_1() VALUES (0),(1),(f_cd_tmp(-2,0)) |
|
| master-bin.000001 | 609 | Table_map | 1 | 662 | table_id: 24 (test.tt_error_1) |
|
| master-bin.000001 | 662 | Write_rows_v1 | 1 | 705 | table_id: 24 |
|
| master-bin.000001 | 705 | Query | 1 | 843 | DROP TEMPORARY TABLE IF EXISTS `test`.`tmp_0` /* generated by server */ |
|
| master-bin.000001 | 843 | Annotate_rows | 1 | 922 | INSERT INTO tt_error_1() VALUES (0),(1),(f_cd_tmp(-2,0))
|
|
The function is defined with
delimiter |
|
create function f_cd_tmp(a int, b int) returns int |
begin
|
drop TEMPORARY table tmp_0;
|
return abs(a); |
end|
|
|
Slave reasonably fails to receive such malformed group of events:
2021-10-26 17:15:47 15 [ERROR] Slave IO thread did not receive an expected Rows-log end-of-statement for event starting at log 'master-bin.000001' position 790 whose last block was seen at log 'master-bin.000001' position 790. The end-of-statement should have been delivered before the current one at log 'master-bin.000001' position 833
|
2. Query log event of DROP temporary table gets cleared at statement rollback if
the statement ends up with an error, like below
create temporary table tmp_0 (a int) engine=innodb; |
set @@binlog_format=ROW; |
BEGIN;
|
# --error ER_DUP_KEY
|
INSERT INTO tt_error_1() VALUES (0), (1), (f_cd_tmp(-1,0)); |
COMMIT;
|
SHOW BINLOG EVENTS displays no DROP.
3. The fact of logged CREATE temporary table with a stored function or trigger gets forgotten at binlogging of the statement (in STATEMENT format) so when the transaction eventually ROLLBACKs the statement's Query log event that implicitly created the table also gets removed while it's replication unsafe.