MySQL Bugs: #20499: mixed mode with temporary table breaks binlog

Bug #20499	mixed mode with temporary table breaks binlog
Submitted:	16 Jun 2006 14:12	Modified:	24 Jul 2006 19:56
Reporter:	Guilhem Bichot
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Guilhem Bichot	Target Version:

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.