Bug #40013 mixed replication: row based format could lead to stale tmp tables on the slave
Submitted: 14 Oct 2008 10:18 Modified: 18 Mar 2009 15:21
Reporter: Alexander Y. Fomichev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.26+, 5.1, 6.0 bzr OS:Any
Assigned to: li zhou CPU Architecture:Any
Tags: Contribution, temporary tables mixed row cleanup

[14 Oct 2008 10:18] Alexander Y. Fomichev
Description:
IIUC:
In mixed mode master could create temporary table being in STATEMENT binlog-format and switching to ROW just after. If so binlog will stay in row mod till the end of session (including TNX::cleanup at the and).
as far as temporary tables just ignored in row based format, temporary tables created in statement mode will not been droped at cleanup time, beacaus close_temporary_tables checks only current_stmt_binlog_row_based.

void close_temporary_tables(THD *thd)
....
if (!mysql_bin_log.is_open() || thd->current_stmt_binlog_row_based)

How to repeat:
on the master:
bowb ~ # mysql -e 'DROP TABLE IF EXISTS test; CREATE TABLE test (c CHAR(48))' test;
bowb ~ # for i in `seq 5`;do mysql -e 'CREATE TEMPORARY TABLE test_tmp (c CHAR(48));INSERT INTO test  SELECT UUID()' test;done
bowb ~ # for i in `seq 5`;do mysql -e 'CREATE TEMPORARY TABLE test_tmp (c CHAR(48));INSERT INTO test  SELECT UUID()' test;done

on the slave:
mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G
*************************** 1. row ***************************
Variable_name: Slave_open_temp_tables
        Value: 0
1 row in set (0.01 sec)

mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G
*************************** 1. row ***************************
Variable_name: Slave_open_temp_tables
        Value: 5
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE "Slave_open_temp_tables"\G
*************************** 1. row ***************************
Variable_name: Slave_open_temp_tables
        Value: 10
1 row in set (0.00 sec)

Suggested fix:
oops, i couldn't find something better then just clear_current_stmt_binlog_row_based() juts before close_temporary_tables (if initial log format was mixed) and hopes that *IF EXISTS* save me [as for temporary tables which created under 'row' binlog format]

diff -urNp mysql-5.1.28-a/sql/sql_class.cc mysql-5.1.28-b/sql/sql_class.cc
--- mysql-5.1.28-a/sql/sql_class.cc     2008-08-28 19:40:58.000000000 +0400
+++ mysql-5.1.28-b/sql/sql_class.cc     2008-10-14 11:18:48.256196550 +0400
@@ -844,6 +844,8 @@ void THD::cleanup(void)
   mysql_ha_cleanup(this);
   delete_dynamic(&user_var_events);
   hash_free(&user_vars);
+  if (variables.binlog_format == BINLOG_FORMAT_MIXED)
+       clear_current_stmt_binlog_row_based();
   close_temporary_tables(this);
   my_free((char*) variables.time_format, MYF(MY_ALLOW_ZERO_PTR));
   my_free((char*) variables.date_format, MYF(MY_ALLOW_ZERO_PTR));
[14 Oct 2008 14:12] Alexander Y. Fomichev
> as far as temporary tables just ignored in row based format, 
> temporary tables created in statement mode will not been droped 
> at cleanup time, beacaus  close_temporary_tables checks only 
> current_stmt_binlog_row_based.

Ugh, i was clean... for me only, i'm afraid, sorry :(
temporary tables created in statement mode will not been droped *on the slave* 
because close_temporary_tables checks only current_stmt_binlog_row_based and so skip insertion of DROP TABLE instructions into binlog.
[14 Oct 2008 20:28] Sveta Smirnova
Thank you for the report.

Verified as described.
[12 Jan 2009 10:33] Susanne Ebrecht
Bug #41969 was set as duplicate of this bug here.
[16 Feb 2009 9:49] Bugs System
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/66444

2793 Leonard Zhou	2009-02-16
      Bug#40013 Mixed replication can work for the operations of row based temp table.
      
      After we do 'insert into t1 select UUID()', the current format is changed to 'row' but the default format is still 'MIXED'.
      So we have problems when we do some operations after that clause. eg: can't create tmp table in slave...
      
      
      Our solution is to allow create/drop table if the default/current format is mixed/row and clear row based format flag.
[18 Feb 2009 8:55] Bugs System
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/66732

2793 Leonard Zhou	2009-02-18
      Bug#40013 mixed replication: row based format could lead to stale tmp tables on the slave
      
      In mixed mode, if we create a temporary table and do some update which switch to ROW format, 
      the format will keep in ROW format. 
      So when the session is end, temp table can't be dropped because at cleanup time we
      checks only current binlog format and so skip insertion of DROP TABLE instructions into binlog.
      
      Our solution is that when closing temp tables at cleanup time 
      we write DROP TABLE into binlog if current binlog format is ROW but in MIX mode.
[23 Feb 2009 3:34] Bugs System
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/67130

2793 Leonard Zhou	2009-02-23
      Bug#40013 mixed replication: row based format could lead to stale tmp tables on the
      slave.
      
      In mixed mode, if we create a temporary table and do some update which switch to ROW format,
      the format will keep in ROW format until the session ends or the table is dropped explicitly. 
      When the session ends, the temp table is dropped automaticly at cleanup time.
      but it checks only current binlog format and so skip insertion of DROP TABLE instructions into binlog.
      So the temp table can't be dropped correctly at slave.
      
      Our solution is that when closing temp tables at cleanup time we check both binlog format and binlog mode,
      and we could write DROP TABLE instructions into binlog if current binlog format is ROW but in MIX mode.
[13 Mar 2009 19:03] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090224070618-mr7stu6rfcvoj18g) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 10:52] Jon Stephens
Documented in the 5.1.33 changelog as follows:

        When using MIXED replication format and temporary tables were
        created in statement-based mode, but a later operation in the same
        session caused a switch to row-based mode, the temporary tables were 
        not dropped on the slave at the end of the session. 

        This bug was introduced by the fix for Bug #20499. 
        See also Bug #43046.

Set bug status to NDI pending merge of fix to the 6.0 tree.
[18 Mar 2009 13:20] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090224072212-51w0xg6doju2drup) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 15:21] Jon Stephens
Fix also documented in the 6.0.11 changelog; closed.
[9 May 2009 16:40] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:38] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:35] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)