Bug #72151 treat statements using temporary tables as unsafe
Submitted: 28 Mar 2014 12:38
Reporter: Peter Burns Email Updates:
Status: Open Impact on me:
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.1.66 OS:Linux
Assigned to: CPU Architecture:Any

[28 Mar 2014 12:38] Peter Burns
We run several servers in a replication loop. When we try to switch to MIXED mode, we find slaves switching unexpectedly to row-based logging, and this causes us problems (see my note on http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html).

How to repeat:
- master server replicating to slave, which also creates a binary log. Both in MIXED mode.
- a session on the master creates a temporary table (logged by statement)
- another session on the master logs by row (e.g. because it's unsafe)
- other sessions on the master log by statement

Consequence is that when these statement-logged events replicate to the slave, they now get logged by row (until the temporary table is dropped).

Suggested fix:
An option to treat all statements using temporary tables as unsafe (and hence log them row-based in MIXED mode). As in ROW mode, this option would prevent temporary tables from replicating at all.