bzr commit into mysql-5.1-bugteam branch (Dao-Gang.Qu:3201) Bug#47863

[Dao-Gang.Qu]

#At file:///home/daogangqu/mysql/bzrwork/bug47863/mysql-5.1-bugteam/ based on revid:jorgen.loland@stripped

 3201 Dao-Gang.Qu@stripped	2009-12-03
      Bug #47863  	binlog_format should be writable only at transaction boundaries
            
      When @@session.binlog_format is modified inside a transaction, 
      it can cause slave to go out of sync. This is true after WL#2687, 
      and possibly before that too (it's harder to know since the behavior 
      before WL#2687 is not completely understood).
            
      To fix the problem, In 5.1, a warning is logged when the @@session.binlog_format 
      is set inside a transaction. In 5.1-rep+3, an error is given when the 
      @@session.binlog_format is set inside a transaction.
     @ mysql-test/suite/rpl/r/rpl_binlog_format.result
        Test Result for bug#47863.
     @ mysql-test/suite/rpl/t/rpl_binlog_format.test
        Added test to verify if a warning will be logged, when the session variable 
        'binlog_format' is set inside a transaction, and the session variable 
        'binlog_format' is read-only inside a sub-statements.
     @ sql/set_var.cc
        Added code to log a warning when the @@session.binlog_format 
        is set inside a transaction.

    added:
      mysql-test/suite/rpl/r/rpl_binlog_format.result
      mysql-test/suite/rpl/t/rpl_binlog_format.test
    modified:
      sql/set_var.cc
=== added file 'mysql-test/suite/rpl/r/rpl_binlog_format.result'
--- a/mysql-test/suite/rpl/r/rpl_binlog_format.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_binlog_format.result	2009-12-03 05:24:46 +0000
@@ -0,0 +1,58 @@
+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) engine= myisam;
+create table t2 (a int) engine= innodb;
+set @@session.binlog_format= statement;
+# The value of 'binlog_format' is changed to STATEMENT.
+insert into t1 values (1);
+begin;
+insert into t2 select * from t1;
+set @@session.binlog_format= row;
+# A warning is logged after set the @@session.binlog_format 
+# inside a transaction.
+insert into t1 values (2);
+commit;
+# The value of 'binlog_format' is changed to ROW.
+select * from t2;
+a
+1
+select * from t1;
+a
+1
+2
+select * from t2;
+a
+1
+select * from t1;
+a
+1
+2
+create table t3(a int, b int) engine= innodb;
+create table t4(a int) engine= innodb;
+create table t5(a int) engine= innodb;
+create trigger tr2 after insert on t3 for each row begin
+insert into t4(a) values(1);
+set @@session.binlog_format= row; 
+insert into t4(a) values(2);
+insert into t5(a) values(3);
+end |
+set @@session.binlog_format= statement;
+# The value of 'binlog_format' is changed to STATEMENT.
+# Check the session variable 'binlog_format' is read-only
+# in sub-statements.
+insert into t3(a,b) values(1,1);
+ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
+# The value of 'binlog_format' is not changed to ROW.
+select * from t4;
+a
+select * from t4;
+a
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;

=== added file 'mysql-test/suite/rpl/t/rpl_binlog_format.test'
--- a/mysql-test/suite/rpl/t/rpl_binlog_format.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_binlog_format.test	2009-12-03 05:24:46 +0000
@@ -0,0 +1,86 @@
+#
+# BUG#47863
+# This test verifies if a warning will be logged, when the session variable 
+# 'binlog_format' is set inside a transaction, and the session variable 
+# 'binlog_format' is read-only inside a sub-statements.
+#
+
+source include/master-slave.inc;
+source include/have_innodb.inc;
+source include/have_binlog_format_row.inc;
+
+create table t1 (a int) engine= myisam;
+create table t2 (a int) engine= innodb;
+
+set @@session.binlog_format= statement;
+if (`SELECT @@session.binlog_format = 'STATEMENT'`)
+{
+  --echo # The value of 'binlog_format' is changed to STATEMENT.
+}
+
+insert into t1 values (1);
+            
+begin;      
+  insert into t2 select * from t1;
+  set @@session.binlog_format= row;
+--echo # A warning is logged after set the @@session.binlog_format 
+--echo # inside a transaction.
+  insert into t1 values (2);
+commit;
+
+if (`SELECT @@session.binlog_format = 'row'`)
+{
+  --echo # The value of 'binlog_format' is changed to ROW.
+}
+
+select * from t2; 
+select * from t1;
+ 
+sync_slave_with_master;
+connection slave; 
+select * from t2; 
+select * from t1;
+
+connection master;
+create table t3(a int, b int) engine= innodb;
+create table t4(a int) engine= innodb;
+create table t5(a int) engine= innodb;
+delimiter |;
+eval create trigger tr2 after insert on t3 for each row begin
+    insert into t4(a) values(1);
+    set @@session.binlog_format= row; 
+    insert into t4(a) values(2);
+    insert into t5(a) values(3);
+end |
+delimiter ;|
+
+set @@session.binlog_format= statement;
+if (`SELECT @@session.binlog_format = 'STATEMENT'`)
+{
+  --echo # The value of 'binlog_format' is changed to STATEMENT.
+}
+
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # in sub-statements.
+--error 1560
+insert into t3(a,b) values(1,1);
+
+if (`SELECT @@session.binlog_format = 'STATEMENT'`)
+{
+  --echo # The value of 'binlog_format' is not changed to ROW.
+}
+
+select * from t4;
+
+sync_slave_with_master;
+connection slave;
+select * from t4;
+
+connection master;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
+sync_slave_with_master;
+

=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc	2009-09-29 15:38:40 +0000
+++ b/sql/set_var.cc	2009-12-03 05:24:46 +0000
@@ -1293,6 +1293,16 @@ bool sys_var_thd_binlog_format::is_reado
     my_error(ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
     return 1;    
   }
+  /*
+    When @@session.binlog_format is modified inside a transaction, it can 
+    cause slave to go out of sync. This is true after WL#2687, and possibly 
+    before that too (it's harder to know since the behavior before WL#2687 
+    is not completely understood).
+  */
+  if (thd->active_transaction())
+    sql_print_warning("Setting @@session.binlog_format inside a transaction "
+                      "can cause the slave to go out of sync. In future "
+                      "versions, this warning will be upgraded to an error.");
   return sys_var_thd_enum::is_readonly();
 }
 


Attachment: [text/bzr-bundle] bzr/[e-mail]