ANALYZE/OPTIMIZE/REPAIR on temp table that was never binlogged are written to the binlog

Root cause — replicating admin statements is deliberate for base tables (hence NO_WRITE_TO_BINLOG). The defect is that the guard has no temporary-table condition at all, sql_admin.cc:1526:

Root cause — replicating admin statements is deliberate for base tables (hence NO_WRITE_TO_BINLOG). The defect is that the guard has no temporary-table condition at all, sql_admin.cc:1526:

if (is_table_modified && is_cmd_replicated && !thd->lex->no_write_to_binlog)

  write_bin_log(thd, true, thd->query(), thd->query_length());

else

  thd->tmp_table_binlog_handled= 1;   /* <- MDEV-36099 made only THIS branch temp-aware */

Nothing checks tmp_table or using_binlog(). MDEV-36099 did make the else branch temp-table aware (to avoid spurious demotion), so temporary tables were considered in this function — but only in the not-logged direction.

is_cmd_replicated is a per-command constant, which predicts exactly which statements leak. Measured, with CHECK as the negative control:

┌───────────┬───────────────────┬────────┬──────────────┐

│ statement │ is_cmd_replicated │ engine │  binlogged?  │

├───────────┼───────────────────┼────────┼──────────────┤

│ ANALYZE   │ true (:1673)      │ InnoDB │ yes          │

├───────────┼───────────────────┼────────┼──────────────┤

│ OPTIMIZE  │ true (:1732)      │ InnoDB │ yes          │

├───────────┼───────────────────┼────────┼──────────────┤

│ REPAIR    │ true (:1760)      │ Aria   │ yes          │

├───────────┼───────────────────┼────────┼──────────────┤

│ REPAIR    │ true              │ InnoDB │ yes          │

├───────────┼───────────────────┼────────┼──────────────┤

│ CHECK     │ false (:1704)     │ Aria   │ no — correct │

└───────────┴───────────────────┴────────┴──────────────┘

No engine escapes it: InnoDB doesn't really implement REPAIR but returns repair status OK rather than refusing, so it reaches the commit that sets is_table_modified and is logged too.

--source include/have_innodb.inc

--source include/have_log_bin.inc

--source include/master-slave.inc

--connection master

--echo # the shipping default configuration: MIXED + create_tmp_table_binlog_formats=STATEMENT

SET SESSION binlog_format= MIXED;

SELECT @@session.binlog_format, @@session.create_tmp_table_binlog_formats;

--echo # an ordinary replicated BASE table named tt

CREATE TABLE tt (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;

INSERT INTO tt VALUES (1,10),(2,20),(3,30);

ANALYZE TABLE tt;

--sync_slave_with_master

--connection slave

--echo # slave's persistent-statistics timestamp for the BASE table, before

--let $s_before= `SELECT COUNT(*) FROM mysql.innodb_table_stats WHERE database_name='test' AND table_name='tt'`

--echo # rows in mysql.innodb_table_stats for test.tt = $s_before

--connection master

--echo #

--echo # a TEMPORARY table of the same name shadows it -- and is NOT binlogged here

--echo #

--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)

CREATE TEMPORARY TABLE tt (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;

INSERT INTO tt VALUES (7,70);

--echo # master sees the temporary table

SELECT * FROM tt;

--echo #

--echo # the admin statements, run by the master against its TEMPORARY table

--echo #

ANALYZE TABLE tt;

OPTIMIZE TABLE tt;

--echo #

--echo # what reached the binlog -- the CREATE/INSERT are correctly absent, so any

--echo # ANALYZE/OPTIMIZE here is being shipped for a table the slave does not have

--echo #

--source include/show_binlog_events.inc

--sync_slave_with_master

--source include/check_slave_no_error.inc

--connection slave

--echo # the slave has no temporary table, so it resolved the statements to the

--echo # BASE table -- which still holds the master's original three rows

--sorted_result

SELECT * FROM tt;

--let $errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1)

--echo # Last_SQL_Errno = $errno

--connection master

--echo # master's BASE table is untouched by all this -- it was shadowed throughout

DROP TEMPORARY TABLE tt;

--sorted_result

SELECT * FROM tt;

DROP TABLE tt;

--sync_slave_with_master

--source include/rpl_end.inc

--source include/have_innodb.inc

--source include/have_log_bin.inc

--source include/master-slave.inc

--connection master

--echo # the shipping default configuration: MIXED + create_tmp_table_binlog_formats=STATEMENT

SET SESSION binlog_format= MIXED;

SELECT @@session.binlog_format, @@session.create_tmp_table_binlog_formats;

--echo #

--echo # ======== REPAIR on a never-logged TEMPORARY table (Aria: REPAIR is supported)

--echo #

--connection master

--echo # a replicated BASE table named rr, so the name exists on both sides

CREATE TABLE rr (id INT PRIMARY KEY, v INT) ENGINE=Aria;

INSERT INTO rr VALUES (1,10),(2,20),(3,30);

--sync_slave_with_master

--connection master

--echo # a TEMPORARY table of the same name shadows it. Under MIXED with the

--echo # shipping default this CREATE is NOT binlogged.

--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)

CREATE TEMPORARY TABLE rr (id INT PRIMARY KEY, v INT) ENGINE=Aria;

INSERT INTO rr VALUES (7,70);

--echo # master sees the temporary table

SELECT * FROM rr;

--let $pos_before= query_get_value(SHOW MASTER STATUS, Position, 1)

REPAIR TABLE rr;

--let $pos_after= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $repair_logged= `SELECT $pos_after > $pos_before`

--echo # REPAIR advanced the binlog position = $repair_logged   [prediction: 1, i.e. it leaks]

--echo #

--echo # ======== CHECK -- the negative control (is_cmd_replicated = false)

--echo #

--let $pos_before= query_get_value(SHOW MASTER STATUS, Position, 1)

CHECK TABLE rr;

--let $pos_after= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $check_logged= `SELECT $pos_after > $pos_before`

--echo # CHECK advanced the binlog position = $check_logged   [prediction: 0, it does not leak]

--echo #

--echo # ======== what actually reached the binlog

--echo #

--echo # The CREATE TEMPORARY and the INSERT must be absent -- that is what makes

--echo # any admin statement here a statement shipped for a table the slave has

--echo # never received.

--source include/show_binlog_events.inc

--sync_slave_with_master

--source include/check_slave_no_error.inc

--connection slave

--echo # the slave has no temporary table, so anything logged bound to its BASE

--echo # table -- which still holds the master's original three rows

--sorted_result

SELECT * FROM rr;

--let $errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1)

--echo # Last_SQL_Errno = $errno

--connection master

DROP TEMPORARY TABLE rr;

--echo # master's BASE table, untouched -- it was shadowed throughout

--sorted_result

SELECT * FROM rr;

DROP TABLE rr;

--sync_slave_with_master

--echo #

--echo # ======== does REPAIR on an INNODB temporary table leak too?

--echo #

--echo # InnoDB does not implement REPAIR. If the command fails before the commit

--echo # that sets is_table_modified, nothing is logged -- for a reason unrelated

--echo # to temporary tables. Measured rather than assumed.

--echo #

--connection master

CREATE TABLE ri (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;

INSERT INTO ri VALUES (1,10),(2,20),(3,30);

--sync_slave_with_master

--connection master

CREATE TEMPORARY TABLE ri (id INT PRIMARY KEY, v INT) ENGINE=InnoDB;

INSERT INTO ri VALUES (7,70);

--let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $pos_before= query_get_value(SHOW MASTER STATUS, Position, 1)

REPAIR TABLE ri;

--let $pos_after= query_get_value(SHOW MASTER STATUS, Position, 1)

--let $innodb_repair_logged= `SELECT $pos_after > $pos_before`

--echo # InnoDB REPAIR advanced the binlog position = $innodb_repair_logged

--source include/show_binlog_events.inc

--sync_slave_with_master

--source include/check_slave_no_error.inc

--echo #

--echo # ======== summary of what was measured, then the assertions

--echo #

--echo # Aria    REPAIR on never-logged temp table, binlogged = $repair_logged   [must be 0]

--echo # Aria    CHECK  on never-logged temp table, binlogged = $check_logged   [must be 0]

--echo # InnoDB  REPAIR on never-logged temp table, binlogged = $innodb_repair_logged   [must be 0]

--echo #

--connection master

--let $assert_text= CHECK must not be binlogged (control -- is_cmd_replicated=false)

--let $assert_cond= $check_logged = 0

--source include/assert.inc

--let $assert_text= REPAIR on a never-logged InnoDB temporary table must not be binlogged

--let $assert_cond= $innodb_repair_logged = 0

--source include/assert.inc

--let $assert_text= REPAIR on a never-logged Aria temporary table must NOT be binlogged

--let $assert_cond= $repair_logged = 0

--source include/assert.inc

DROP TEMPORARY TABLE ri;

DROP TABLE ri;

--sync_slave_with_master

--source include/rpl_end.inc