#At file:///home/daogangqu/mysql/bzrwork/bug47863/mysql-5.1-rep%2B3/ based on revid:aelkin@stripped7-t2pa06cs60hjrptn
3125 Dao-Gang.Qu@stripped 2009-12-10
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 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 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 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
sql/share/errmsg.txt
=== 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-10 09:47:58 +0000
@@ -0,0 +1,66 @@
+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;
+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
+# inside a transaction.
+set @@session.binlog_format= row;
+ERROR HY000: Cannot change the binary logging format inside a transaction
+insert into t1 values (2);
+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
+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 |
+# 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-10 09:47:58 +0000
@@ -0,0 +1,85 @@
+#
+# BUG#47863
+# This test verifies if the session variable 'binlog_format'
+# is read-only inside a transaction and in sub-statements.
+#
+
+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;
+
+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 # inside a transaction.
+--error 1658
+ set @@session.binlog_format= row;
+ insert into t1 values (2);
+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-30 18:20:26 +0000
+++ b/sql/set_var.cc 2009-12-10 09:47:58 +0000
@@ -1288,6 +1288,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 inside a transaction.
+ */
+ if (thd->active_transaction())
+ {
+ my_error(ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
+ return 1;
+ }
return sys_var_thd_enum::is_readonly();
}
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2009-11-30 18:20:26 +0000
+++ b/sql/share/errmsg.txt 2009-12-10 09:47:58 +0000
@@ -6249,3 +6249,6 @@ ER_DEBUG_SYNC_TIMEOUT
ER_DEBUG_SYNC_HIT_LIMIT
eng "debug sync point hit limit reached"
ger "Debug Sync Point Hit Limit erreicht"
+ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+ eng "Cannot change the binary logging format inside a transaction"
+ ger "Das Bin�og-Format kann innerhalb einer transaction"
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20091210094758-wu249gbnme1z1uyj.bundle
