INSERT/REPLACE ... SELECT into temporary table may cause silent master/slave divergence

# Mechanism: REPLACE ... SELECT is unconditionally flagged unsafe

# (sql_parse.cc:4617 BINLOG_STMT_UNSAFE_REPLACE_SELECT), so under MIXED it is

# escalated to ROW logging; a temporary table has no ROW logging, so nothing is

# written. Rule 2 requires the table to be demoted to "not up to date" at that

# point -- it is not (sql_insert.cc:4676 takes binary_logged from binlog_query()'s

# return code rather than from whether an event was written). The next statement

# using the temporary table is therefore statement-logged verbatim, and the slave

# recomputes it from its own stale copy.

#

# INSERT IGNORE ... SELECT and INSERT ... SELECT ... ON DUPLICATE KEY UPDATE

# diverge identically; 

--source include/have_innodb.inc

--source include/have_log_bin.inc

--source include/master-slave.inc

--connection master

SET SESSION binlog_format= MIXED;

SET SESSION create_tmp_table_binlog_formats= 'MIXED,STATEMENT';

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

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

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

INSERT INTO src VALUES (1,101),(2,202),(3,303);

--sync_slave_with_master

--connection master

--echo # an ordinary logged temporary table: the CREATE and the INSERT both reach

--echo # the slave, so its copy starts out correct

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

INSERT INTO t VALUES (1,10),(2,20);

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

--echo # the statement under test -- unsafe under MIXED, so not logged at all

REPLACE INTO t SELECT id, v + 1 FROM src;

--echo # and now t is propagated into the permanent base table

INSERT INTO base SELECT id, v FROM t;

--echo # binlog: the REPLACE is absent, the INSERT ... SELECT is a plain Query

--source include/show_binlog_events.inc

--echo # master's view

--sorted_result

SELECT * FROM t;

--sorted_result

SELECT * FROM base;

--sync_slave_with_master

--source include/check_slave_no_error.inc

--echo # slave's view of the SAME permanent table

--connection slave

--sorted_result

SELECT * FROM base;

--connection master

--let $m_sig= `SELECT CONCAT(COUNT(*),':',COALESCE(SUM(v),0)) FROM base`

--connection slave

--let $s_sig= `SELECT CONCAT(COUNT(*),':',COALESCE(SUM(v),0)) FROM base`

--let $diverged= `SELECT '$m_sig' <> '$s_sig'`

--echo # base rows:sum(v) -- master $m_sig / slave $s_sig  ==>  DIVERGED = $diverged

--echo # applier state below must show a perfectly healthy slave

--connection slave

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

--let $running= query_get_value(SHOW SLAVE STATUS, Slave_SQL_Running, 1)

--echo # Slave_SQL_Running = $running / Last_SQL_Errno = $errno

--connection master

DROP TEMPORARY TABLE t;

DROP TABLE base, src;

--sync_slave_with_master

--source include/rpl_end.inc

commit d15c4aaf868789dc06bafc77e04fb95390f4d7d6 (HEAD -> 11.8-enterprise-TODO-6197, origin/11.8-enterprise-TODO-6197)

Author: Brandon Nesterenko <brandon.nesterenko@mariadb.com>

Date:   Thu Jul 30 09:42:57 2026 -0600

    TODO-6197: Custom Build to verify MENT-2792 - Backport MDEV-36099

    Update VERSION