MySQL Bugs: #43046: mixed mode switch to row format with temp table lead to wrong result

Bug #43046	mixed mode switch to row format with temp table lead to wrong result
Submitted:	20 Feb 2009 8:40	Modified:	5 Aug 2009 11:21
Reporter:	li zhou	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:	Luis Soares	CPU Architecture:	Any
Tags:	temporary tables mixed row

Description:
In mixed mode, if we create a temporary table and do some updates that
switches the format to ROW format, then the format will remain ROW
format and prevent any CREATE/DROP/ALTER statements on temporary tables
been binlogged until the temporary table is dropped.

So we have the following problems:

1: Disconnect master server, drop temp table command doesn't write into binlog. So slave have wrong status with temp tables.

2: Create/Drop temp table doesn't write into binlog. So the table can't be created in slave.

3: All the other operations will based on 'ROW' format after the switch.

How to repeat:
We can use the following test case to reproduct this bug:

+source include/master-slave.inc;
+source include/have_binlog_format_mixed.inc;
+
+--echo ==== The First Test Initialize ====
+
+--echo [on master]
+--connection master
+
+CREATE TABLE t1 (a CHAR(48));
+CREATE TEMPORARY TABLE t1_tmp1(a INT);
+INSERT INTO t1 VALUES (UUID());
+
+--echo [on slave]
+sync_slave_with_master;
+
+--echo ==== Verify results on slave ====
+SHOW STATUS LIKE "Slave_open_temp_tables";
+
+--echo [on master]
+--connection master
+
+disconnect master;
+--connection master1
+
+--echo [on slave]
+sync_slave_with_master;
+
+--echo ==== Verify results on slave ====
+SHOW STATUS LIKE "Slave_open_temp_tables";
+
+--echo ==== Clean up ====
+
+--echo [on master]
+--connection master1
+DROP TABLE t1;
+
+--echo [on slave]
+sync_slave_with_master;
+
+#
+# The second test for create and drop temp table in row based format
+#
+
+--echo ==== The Second Test Initialize ====
+
+--echo [on master]
+--connection master1
+
+CREATE TABLE t1 (a CHAR(48));
+CREATE TEMPORARY TABLE t1_tmp1(a INT);
+INSERT INTO t1 VALUES (UUID());
+CREATE TEMPORARY TABLE t1_tmp2(a INT);
+CREATE TEMPORARY TABLE t1_tmp3(a INT);
+DROP TEMPORARY TABLE t1_tmp1;
+
+--echo [on slave]
+sync_slave_with_master;
+
+--echo ==== Verify results on slave ====
+SHOW STATUS LIKE "Slave_open_temp_tables";
+
+--echo [on master]
+
+--connection master1
+
+CREATE TEMPORARY TABLE t1_tmp4(a INT);
+INSERT INTO t1 VALUES (UUID());
+
+--echo [on slave]
+sync_slave_with_master;
+
+--echo ==== Verify results on slave ====
+SHOW STATUS LIKE "Slave_open_temp_tables";
+
+--echo ==== Clean up ====
+
+--echo [on master]
+--connection master1
+DROP TABLE t1;
+
+--echo [on slave]
+sync_slave_with_master;

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

3357 Luis Soares	2009-07-02
      BUG#43046: mixed mode switch to row format with temp table lead
                 to wrong result
      
      When using MIXED mode and issuing 'CREATE TEMPORARY TABLE t_tmp',
      the statement is logged if the current binlogging mode is
      STATEMENT. This causes the slave to replay the instruction and
      create the temporary table as well. If there is no switch to ROW
      mode, and later on a 'DROP TEMPORARY TABLE t_tmp' is issued, then
      this statement will also be logged and the slave will
      remove/close the temporary table.
      
      However, if there is a switch to ROW mode between the CREATE and
      DROP TEMPORARY table, the DROP statement will not be logged,
      leaving the slave with a dangling temporary table.
      
      This patch addresses this, by always logging a DROP TEMPORARY
      TABLE IF EXISTS when in mixed mode and a drop statement is issued
      for temporary table(s).
     @ mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
        Added test case.
     @ sql/sql_table.cc
        When dropping table(s) in mixed mode and current statement 
        logging is ROW, builds an extra DROP TEMPORARY TABLE IF 
        EXISTS for temporary tables that are being dropped. Later, 
        it logs the extra drop statement.

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

3357 Luis Soares	2009-07-08
      BUG#43046: mixed mode switch to row format with temp table lead
                 to wrong result
      
      When using MIXED mode and issuing 'CREATE TEMPORARY TABLE t_tmp',
      the statement is logged if the current binlogging mode is
      STATEMENT. This causes the slave to replay the instruction and
      create the temporary table as well. If there is no switch to ROW
      mode, and later on a 'DROP TEMPORARY TABLE t_tmp' is issued, then
      this statement will also be logged and the slave will
      remove/close the temporary table.
      
      However, if there is a switch to ROW mode between the CREATE and
      DROP TEMPORARY table, the DROP statement will not be logged,
      leaving the slave with a dangling temporary table.
      
      This patch addresses this, by always logging a DROP TEMPORARY
      TABLE IF EXISTS when in mixed mode and a drop statement is issued
      for temporary table(s).
     @ mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
        Added test case.
     @ sql/sql_table.cc
        When dropping table(s) in mixed mode and current statement 
        logging is ROW, builds an extra DROP TEMPORARY TABLE IF 
        EXISTS for temporary tables that are being dropped. Later, 
        it logs the extra drop statement.

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

3043 Luis Soares	2009-07-26
      BUG#43046: mixed mode switch to row format with temp table lead
                 to wrong result
            
      When using MIXED mode and issuing 'CREATE TEMPORARY TABLE t_tmp',
      the statement is logged if the current binlogging mode is
      STATEMENT. This causes the slave to replay the instruction and
      create the temporary table as well. If there is no switch to ROW
      mode, and later on a 'DROP TEMPORARY TABLE t_tmp' is issued, then
      this statement will also be logged and the slave will
      remove/close the temporary table.
            
      However, if there is a switch to ROW mode between the CREATE and
      DROP TEMPORARY table, the DROP statement will not be logged,
      leaving the slave with a dangling temporary table.
            
      This patch addresses this, by always logging a DROP TEMPORARY
      TABLE IF EXISTS when in mixed mode and a drop statement is issued
      for temporary table(s).
     @ mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
        Updated result file.
     @ mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
        Added test case.
     @ sql/sql_table.cc
        When dropping table(s) in mixed mode and current statement 
        logging is ROW, builds an extra DROP TEMPORARY TABLE IF 
        EXISTS for temporary tables that are being dropped. Later, 
        it logs the extra drop statement.

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

3486 Luis Soares	2009-07-26 [merge]
      BUG#43046: mixed mode switch to row format with temp table lead
                 to wrong result
      
      Automerge: mysql-5.1-bugteam --> mysql-pe

Pushed to mysql-5.1-bugteam and mysql-pe.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:satya.bn@sun.com-20090727062228-uf93hg5izc1lpe3z) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Documented bugfix in the 5.1.38 and 5.4.4 changelogs, as follows:

        When using the MIXED logging format, after creating a temporary
        table and performing an update that switched the logging format
        to ROW, the format switch persisted following the update,
        preventing any subsequent DDL statements on temporary tables
        from being written to the binary log until the temporary table
        was dropped.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed into 5.4.2.