bzr commit into mysql-5.1-rep+3 branch (Dao-Gang.Qu:3123) Bug#47863

[Dao-Gang.Qu]

#At file:///home/daogangqu/mysql/bzrwork/bug47863/mysql-5.1-rep%2B3/ based on revid:zhenxing.he@stripped

 3123 Dao-Gang.Qu@stripped	2009-11-26
      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.
      
      To fix the problem, make the SESSION variable 'binlog_format' 
      read-only in 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 the test file to verify if the session variable 'binlog_format' 
        is read-only in transaction and sub-statements, and is writable at 
        transaction boundaries.
     @ sql/set_var.cc
        Added code to make the SESSION variable 'binlog_format' 
        read-only in 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-11-26 05:58:08 +0000
@@ -0,0 +1,68 @@
+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;
+call mtr.add_suppression("Unsafe statement binlogged in statement format "
+                         "since BINLOG_FORMAT .* STATEMENT. Reason for "
+                         "unsafeness: Non-transactional reads or writes "
+                         "are unsafe if they occur after transactional "
+                         "reads or writes inside a transaction.");
+create table t1 (a int) engine= myisam;
+create table t2 (a int) engine= innodb;
+# Check the session variable 'binlog_format' is writable
+# at transaction bouundaries.
+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;
+Warnings:
+Note	1592	Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction.
+# Check the session variable 'binlog_format' is read-only
+# in transaction.
+set @@session.binlog_format= row;
+ERROR HY000: SESSION variable 'binlog_format(in transaction)' is read-only. Use SET (null) to assign the value
+insert into t1 values (1);
+Warnings:
+Note	1592	Unsafe statement binlogged in statement format since BINLOG_FORMAT = STATEMENT. Reason for unsafeness: Non-transactional reads or writes are unsafe if they occur after transactional reads or writes inside a transaction.
+commit;
+# The value of 'binlog_format' is not changed to ROW.
+select * from t2;
+a
+1
+select * from t1;
+a
+1
+1
+select * from t2;
+a
+1
+select * from t1;
+a
+1
+1
+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 |
+# 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-11-26 05:58:08 +0000
@@ -0,0 +1,88 @@
+#
+# BUG#47863
+# This test verifies if the session variable 'binlog_format' 
+# is read-only in transaction and sub-statements, and is 
+# writable at transaction boundaries.
+#
+
+source include/master-slave.inc;
+source include/have_innodb.inc;
+source include/have_binlog_format_row.inc;
+
+call mtr.add_suppression("Unsafe statement binlogged in statement format "
+                         "since BINLOG_FORMAT .* STATEMENT. Reason for "
+                         "unsafeness: Non-transactional reads or writes "
+                         "are unsafe if they occur after transactional "
+                         "reads or writes inside a transaction.");
+create table t1 (a int) engine= myisam;
+create table t2 (a int) engine= innodb;
+
+--echo # Check the session variable 'binlog_format' is writable
+--echo # at transaction bouundaries.
+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;
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # in transaction.
+--error 1621
+  set @@session.binlog_format= row;
+  insert into t1 values (1);
+commit;
+
+if (`SELECT @@session.binlog_format = 'STATEMENT'`)
+{
+  --echo # The value of 'binlog_format' is not 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 ;|
+
+--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-11-10 18:45:15 +0000
+++ b/sql/set_var.cc	2009-11-26 05:58:08 +0000
@@ -1293,6 +1293,14 @@ bool sys_var_thd_binlog_format::is_reado
     my_error(ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
     return 1;    
   }
+  /*
+    Make the SESSION variable 'binlog_format' read-only in transaction.
+  */
+  if (thd->active_transaction())
+  {
+    my_error(ER_VARIABLE_IS_READONLY, MYF(0), "SESSION", "binlog_format(in transaction)");
+    return 1;
+  }
   return sys_var_thd_enum::is_readonly();
 }
 


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