Bug #20499 mixed mode with temporary table breaks binlog

Description:
If in mixed mode on master. If a query stores in a temporary table a function which triggers row-based (e.g. UUID()), then the temp table's data will not be stored in the binlog. At the end of the creation we switch back to statement-based. But if an INSERT SELECTs from the temp table into a permanent table, then it will be binlogged statement-based (no UUID() involved), but slave does not have the data of the temporary table, so will not replicate the INSERT SELECT well.

How to repeat:
patch rpl_switch_stm_row_mixed.test:
===== t/rpl_switch_stm_row_mixed.test 1.4 vs edited =====
*** /home/mysql_src/mysql-5.1-new-WL3146-nopatch/BitKeeper/tmp/bk_rpl_switch_stm_row_mixed.test-1.4_wqSZGn      2006-03-13 15:34:15 +01:00
--- edited/t/rpl_switch_stm_row_mixed.test      2006-06-16 14:05:18 +02:00
***************
*** 196,208 ****
    select count(*) from t9;
  }

- --replace_column 2 # 5 #
- --replace_regex /table_id: [0-9]+/table_id: #/
- show binlog events from 102;
  sync_slave_with_master;
  # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
  --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
  --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql

  connection master;
  drop database mysqltest1;
--- 196,224 ----
    select count(*) from t9;
  }

  sync_slave_with_master;
  # as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID
  --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql
  --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql
+
+
+
+ connection master;
+ use mysqltest1;
+ drop table t1,t2;
+ CREATE TEMPORARY TABLE t1 SELECT UUID();
+ create table t2 like t1;
+ INSERT INTO t2 SELECT * FROM t1;
+ insert into t2 values("first");
+ drop table t1;
+ insert into t2 values("second");
+ select count(*) from t2;
+ --replace_column 2 # 5 #
+ --replace_regex /table_id: [0-9]+/table_id: #/
+ show binlog events from 102;
+
+ sync_slave_with_master;
+ select count(*) from mysqltest1.t2;

  connection master;
  drop database mysqltest1;

Suggested fix:
I will commit a patch today, which does not reset back to statement-based if there are open temporary tables

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7760

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7766

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8752

Pushed in replication team tree 5.1, will be in 5.1.12.
Bug happened in such cases, in mixed binlogging mode:
CREATE TEMPORARY TABLE t1 SELECT UUID(); # UUID => binlogged row-based
# automatically switches back to statement-based
create table t2 like t1; # a normal table
INSERT INTO t2 SELECT * FROM t1; # statement-based
The last statement depends on t1. But t1's creation was binlogged row-based,
and for temp tables, row-based binlogging writes nothing to binlog (as expected).
So the last statement didn't insert any data on slave.
Fix is that we don't automatically switch back to statement-based if the
session has temp tables.
ChangeSet
  guilhem@gbichot3.local|ChangeSet|20060709150047|19215    2006/07/09 17:00:47+02:00 guilhem@gbichot3.local +15 -0
  * Mixed replication mode * :
...
 3) Fix for BUG#20499 "mixed mode with temporary table breaks binlog":
  a temporary table containing e.g. UUID has its changes not binlogged,
  so any query updating a permanent table with data from the temporary table
  will run wrongly on slave. Solution: in mixed mode we don't switch back
  from row-based to statement-based when there exists temporary tables.

Noted in 5.1.12 changelog.

In mixed binary logging mode, storing into a temporary table a 
row that uses a function that causes a switch to row-based
binary logging, the temporary table's data was not logged.
Consequently, selecting from the table into a non-temporary
table would would work on the master but not the slave.