MySQL Bugs: #54842: DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW

Bug #54842	DROP TEMPORARY TABLE not binlogged after manual switching binlog format to ROW
Submitted:	27 Jun 2010 22:00	Modified:	3 Sep 2010 14:22
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.5-m3	OS:	Any
Assigned to:	Luis Soares	CPU Architecture:	Any
Tags:	regression

Description:
If binlog_format is explicitly switched from STATEMENT to ROW after a temporary table was created, upon disconnect master does not write DROP TEMPORARY TABLE into binary log. Consequently, slave does not drop the table, and temporary tables (and corresponding files) can pile up if the scenario is repeating.

This is a regression from 5.5.4-m3 where DROP statements are written into binary log automatically.

It might be related to bug#52616 fixed in 5.5.5-m3.

For the scenario in 'How to repeat' section, second SHOW STATUS and SHOW BINLOG EVENTS produce the following:

SHOW STATUS LIKE 'Slave_open_temp_tables';
Variable_name   Value
Slave_open_temp_tables  2
SHOW BINLOG EVENTS;
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.5-m3-log, Binlog ver: 4
master-bin.000001       107     Query   1       196     use `test`; DROP TABLE IF EXISTS tperm
master-bin.000001       196     Query   1       287     use `test`; CREATE TABLE tperm ( i INT )
master-bin.000001       287     Query   1       388     use `test`; CREATE TEMPORARY TABLE ttmp1 ( i INT )
master-bin.000001       388     Query   1       489     use `test`; CREATE TEMPORARY TABLE ttmp2 ( i INT )

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_mixed_or_statement.inc

--disable_warnings
DROP TABLE IF EXISTS tperm;
--enable_warnings

CREATE TABLE tperm ( i INT );
--sync_slave_with_master
SHOW STATUS LIKE 'Slave_open_temp_tables';

--connect(con1,localhost,root,,)
CREATE TEMPORARY TABLE ttmp1 ( i INT );
SET SESSION binlog_format=ROW;
--disconnect con1

--connect(con2,localhost,root,,)
CREATE TEMPORARY TABLE ttmp2 ( i INT );
SET SESSION binlog_format=ROW;
--disconnect con2

--connection master
--sync_slave_with_master
SHOW STATUS LIKE 'Slave_open_temp_tables';

--connection master
SHOW BINLOG EVENTS;
DROP TABLE tperm;

--exit

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/112442

3079 Luis Soares	2010-06-29
      BUG#54842: DROP TEMPORARY TABLE not binlogged after manual
      switching binlog format to ROW
      
      BUG 52616 fixed the case which the user would switch from STMT to
      ROW binlog format, but the server would silently ignore it. After
      that fix thd->is_current_stmt_binlog_format_row() reports correct
      value at logging time and events are logged in ROW (as expected)
      instead of STMT as they were previously and wrongly logged.
      
      However, the fix was only partially complete, because on
      disconnect, at THD cleanup, the implicit logging of temporary
      tables is conditionally performed. If the binlog_format==ROW and
      thd->is_current_stmt_binlog_format_row() is true then DROPs are
      not logged. Given that the user can switch from STMT to ROW, this
      is wrong because the server cannot tell, just by relying on the
      ROW binlog format, that the tables have been dropped before. This
      is effectively similar to the MIXED scenario when a switch from
      STMT to ROW is triggered.
      
      We fix this by removing this condition from
      close_temporary_tables.
     @ mysql-test/extra/binlog_tests/drop_temp_table.test
        Added binlog test case.
     @ mysql-test/suite/binlog/r/binlog_row_drop_tmp_tbl.result
        Result changes because:
        - there is a missing drop on three temporary tables
        - it now contains results for the test added
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Result now contains the implicit drop for the temporary table.
     @ mysql-test/suite/binlog/r/binlog_stm_drop_tmp_tbl.result
        Result file changed because it now contains results for added
        test case.
     @ mysql-test/suite/rpl/r/rpl_drop_temp.result
        Result file changed because it now contains results for added
        test case.
     @ mysql-test/suite/rpl/t/rpl_drop_temp.test
        Added replication test case.
     @ sql/sql_base.cc
        Removed the condition that would make the server to skip
        logging implicit drops when ROW binary log format mode was 
        in use.
        Additionally, deployed DBUG_ENTER/RETURN macros.

Queued in mysql-trunk-bugfixing and merged to mysql-next-mr-bugfixing:
- http://pb2.norway.sun.com/web.py?template=push_details&push=1401752
- http://pb2.norway.sun.com/web.py?template=push_details&push=1401744

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Documented bugfix in the 5.5.6 changelog as follows:

        If binlog_format was explicitly switched from STATEMENT to ROW
        following the creation of a temporary table, then on disconnect
        the master failed to write the expected DROP TEMPORARY TABLE
        statement into the binary log. As a consequence, temporary
        tables (and their corresponding files) accumulated as this
        scenario was repeated.
      
Set NM status - waiting for merge to next-mr tree.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html