List:Commits« Previous MessageNext Message »
From:Luis Soares Date:July 2 2009 8:34pm
Subject:bzr commit into mysql-azalea branch (luis.soares:3357) Bug#43046
View as plain text  
#At file:///home/lsoares/Workspace/mysql-server/bugfix/b43046/mysql-azalea/ based on revid:zhenxing.he@stripped

 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.

    modified:
      mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result
      mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test
      sql/sql_table.cc
=== modified file 'mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result'
--- a/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result	2009-02-23 03:26:38 +0000
+++ b/mysql-test/suite/rpl/r/rpl_temp_table_mix_row.result	2009-07-02 20:34:32 +0000
@@ -24,3 +24,48 @@ Slave_open_temp_tables	0
 [on master]
 DROP TABLE t1;
 [on slave]
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
+CREATE TEMPORARY TABLE t1_tmp (i1 int);
+ALTER TABLE t1_tmp ADD COLUMN b INT;
+DELETE FROM t2;
+CREATE TEMPORARY TABLE t2_tmp (a int);
+ALTER TABLE t1_tmp ADD COLUMN c INT;
+### assertion: assert that there is one open temp table on slave
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+Variable_name	Value
+Slave_open_temp_tables	1
+DROP TABLE t1_tmp, t2;
+INSERT INTO t1 VALUES (1);
+DROP TEMPORARY TABLE t2_tmp;
+INSERT INTO t1 VALUES (2);
+### assertion: assert that slave has no temporary tables opened
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+Variable_name	Value
+Slave_open_temp_tables	0
+DROP TABLE t3, t1;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a int)
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) )
+slave-bin.000001	#	Query	#	#	use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ()
+slave-bin.000001	#	Query	#	#	use `test`; CREATE TEMPORARY TABLE t1_tmp (i1 int)
+slave-bin.000001	#	Query	#	#	use `test`; ALTER TABLE t1_tmp ADD COLUMN b INT
+slave-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t2` /* generated by server */
+slave-bin.000001	#	Query	#	#	use `test`; DROP TEMPORARY TABLE IF EXISTS `t1_tmp` /* generated by server */
+slave-bin.000001	#	Query	#	#	BEGIN
+slave-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+slave-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
+slave-bin.000001	#	Query	#	#	COMMIT
+slave-bin.000001	#	Query	#	#	use `test`; DROP TEMPORARY TABLE IF EXISTS `t2_tmp` /* generated by server */
+slave-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (2)
+slave-bin.000001	#	Query	#	#	use `test`; DROP TABLE t3, t1

=== modified file 'mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test'
--- a/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test	2009-03-24 08:55:03 +0000
+++ b/mysql-test/suite/rpl/t/rpl_temp_table_mix_row.test	2009-07-02 20:34:32 +0000
@@ -51,3 +51,98 @@ DROP TABLE t1;
 
 --echo [on slave]
 sync_slave_with_master;
+
+#
+# BUG#43046: mixed mode switch to row format with temp table lead to wrong
+# result
+#
+# NOTES
+# =====
+#  
+#  1. Temporary tables cannot be logged using the row-based
+#     format. Thus, once row-based logging is used, all subsequent
+#     statements using that table are unsafe, and we approximate this
+#     condition by treating all statements made by that client as
+#     unsafe until the client no longer holds any temporary tables.
+#
+#  2. Two different connections can use the same temporary table
+#     name without conflicting with each other or with an
+#     existing non-TEMPORARY table of the same name.
+#
+# DESCRIPTION
+# ===========
+#  
+#   The test is implemented as follows:
+#     1. create regular tables 
+#     2. create a temporary table t1_tmp: should be logged as statement
+#     3. issue an alter table: should be logged as statement
+#     4. issue statement that forces switch to RBR
+#     5. create another temporary table t2_tmp: should not be logged
+#     6. issue alter table on t1_tmp: should not be logged
+#     7. drop t1_tmp and regular table on same statement: should log both in
+#        statement format (but different statements)
+#     8. issue deterministic insert: logged as row (because t2_tmp still
+#        exists).
+#     9. drop t2_tmp and issue deterministic statement: should log drop and
+#        query in statement format (show switch back to STATEMENT format)
+#    10. in the end the slave should not have open temp tables.
+#  
+
+connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
+-- source include/master-slave-reset.inc
+-- connection master
+
+# action: setup environment
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TABLE t3 ( i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1) );
+CREATE TRIGGER tr1 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO t3 () VALUES ();
+
+# assertion: assert that CREATE is logged as STATEMENT
+CREATE TEMPORARY TABLE t1_tmp (i1 int);
+
+# assertion: assert that ALTER TABLE is logged as STATEMENT
+ALTER TABLE t1_tmp ADD COLUMN b INT;
+
+# action: force switch to RBR
+DELETE FROM t2;
+
+# assertion: assert that t2_tmp will not make into the binlog (RBR logging atm)
+CREATE TEMPORARY TABLE t2_tmp (a int);
+
+# assertion: assert that ALTER TABLE on t1_tmp will not make into the binlog
+ALTER TABLE t1_tmp ADD COLUMN c INT;
+
+-- echo ### assertion: assert that there is one open temp table on slave
+-- sync_slave_with_master
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+
+-- connection master
+
+# assertion: assert that both drops are logged
+DROP TABLE t1_tmp, t2;
+
+# assertion: assert that statement is logged as row (master still has one
+#            opened temporary table - t2_tmp.
+INSERT INTO t1 VALUES (1);
+
+# assertion: assert that DROP TABLE *is* logged despite CREATE is not.
+DROP TEMPORARY TABLE t2_tmp;
+
+# assertion: assert that statement is now logged as STMT (mixed mode switches
+#            back to STATEMENT).
+INSERT INTO t1 VALUES (2);
+
+-- sync_slave_with_master
+
+-- echo ### assertion: assert that slave has no temporary tables opened
+SHOW STATUS LIKE 'Slave_open_temp_tables';
+
+-- connection master
+
+# action: drop remaining tables
+DROP TABLE t3, t1;
+
+-- sync_slave_with_master
+
+-- source include/show_binlog_events.inc

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2009-06-02 11:46:19 +0000
+++ b/sql/sql_table.cc	2009-07-02 20:34:32 +0000
@@ -1797,6 +1797,7 @@ int mysql_rm_table_part2(THD *thd, TABLE
   int non_temp_tables_count= 0;
   bool some_tables_deleted=0, tmp_table_deleted=0, foreign_key_error=0;
   String built_query;
+  String built_tmp_query;
   DBUG_ENTER("mysql_rm_table_part2");
 
   LINT_INIT(alias);
@@ -1900,6 +1901,25 @@ int mysql_rm_table_part2(THD *thd, TABLE
     case  0:
       // removed temporary table
       tmp_table_deleted= 1;
+      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
+          thd->current_stmt_binlog_row_based)
+      {
+        if (built_tmp_query.is_empty()) 
+        {
+          built_tmp_query.set_charset(system_charset_info);
+          built_tmp_query.append("DROP TEMPORARY TABLE IF EXISTS ");
+        }
+
+        built_tmp_query.append("`");
+        if (thd->db == NULL || strcmp(db,thd->db) != 0)
+        {
+          built_tmp_query.append(db);
+          built_tmp_query.append("`.`");
+        }
+        built_tmp_query.append(table->table_name);
+        built_tmp_query.append("`,");
+      }
+
       continue;
     case -1:
       DBUG_ASSERT(thd->in_sub_stmt);
@@ -2092,6 +2112,28 @@ int mysql_rm_table_part2(THD *thd, TABLE
         In both these cases, nothing should be written to the binary
         log.
       */
+
+
+      /*
+        One needs to always log any temporary table drop, if:
+          1. thread logging format is mixed mode; AND
+          2. current statement logging format is set to row.
+      */
+      if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
+          thd->current_stmt_binlog_row_based &&
+          tmp_table_deleted)
+      {
+        /*
+          In this case we have deleted only temporary tables but we are using
+          row based logging for the statement. However, thread uses mixed mode
+          format, thence we need to log the dropping as we cannot tell for
+          sure whether the create was logged as statement previously or not, ie,
+          before switching to row mode.
+        */
+        built_tmp_query.chop();                  // Chop of the last comma
+        built_tmp_query.append(" /* generated by server */");
+        write_bin_log(thd, !error, built_tmp_query.ptr(), built_tmp_query.length());
+      }
     }
   }
 err:


Attachment: [text/bzr-bundle] bzr/luis.soares@sun.com-20090702203432-o268y9p8nt53x8m1.bundle
Thread
bzr commit into mysql-azalea branch (luis.soares:3357) Bug#43046Luis Soares2 Jul
  • Re: bzr commit into mysql-azalea branch (luis.soares:3357) Bug#43046He Zhenxing6 Jul
  • Re: bzr commit into mysql-azalea branch (luis.soares:3357) Bug#43046Alfranio Correia8 Jul