#At file:///home/daogangqu/mysql/bzrwork/bug47863/mysql-5.1-rep%2B3/ based on revid:aelkin@stripped
3125 Dao-Gang.Qu@stripped 2009-12-18
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/binlog/r/binlog_format_switch_inside_trans.result
Test result for bug#47863.
@ mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test
Added test file to verify if the session variable 'binlog_format'
is read-only inside a transaction and in sub-statements.
@ sql/set_var.cc
Added code to make the session variable 'binlog_format'
read-only inside a transaction.
added:
mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result
mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test
modified:
sql/set_var.cc
sql/share/errmsg.txt
=== added file 'mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result'
--- a/mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/r/binlog_format_switch_inside_trans.result 2009-12-18 05:47:16 +0000
@@ -0,0 +1,97 @@
+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.");
+set @save_binlog_format= @@global.binlog_format;
+create table t1 (a int) engine= myisam;
+create table t2 (a int) engine= innodb;
+SELECT @@session.binlog_format;
+@@session.binlog_format
+ROW
+SET AUTOCOMMIT=1;
+set @@session.binlog_format= statement;
+SELECT @@session.binlog_format;
+@@session.binlog_format
+STATEMENT
+insert into t1 values (1);
+begin;
+# Check the session variable 'binlog_format' is read-only
+# inside a transaction.
+set @@session.binlog_format= mixed;
+ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
+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 modify @@session.binlog_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.
+# Check the session variable 'binlog_format' is read-only
+# inside a transaction.
+set @@session.binlog_format= statement;
+ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
+insert into t2 values (3);
+# Check the session variable 'binlog_format' is read-only
+# inside a transaction.
+set @@session.binlog_format= row;
+ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
+commit;
+set AUTOCOMMIT=0;
+set @@session.binlog_format= row;
+SELECT @@session.binlog_format;
+@@session.binlog_format
+ROW
+insert into t1 values (4);
+# Check the session variable 'binlog_format' is read-only
+# inside a transaction.
+set @@session.binlog_format= statement;
+ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
+SELECT @@session.binlog_format;
+@@session.binlog_format
+ROW
+insert into t2 values (5);
+# Check the session variable 'binlog_format' is read-only
+# inside a transaction.
+set @@session.binlog_format= row;
+ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
+SELECT @@session.binlog_format;
+@@session.binlog_format
+ROW
+begin;
+insert into t2 values (6);
+# Check the global variable 'binlog_format' is writable
+# inside a transaction.
+SELECT @@global.binlog_format;
+@@global.binlog_format
+ROW
+set @@global.binlog_format= statement;
+SELECT @@global.binlog_format;
+@@global.binlog_format
+STATEMENT
+commit;
+set @@global.binlog_format= @save_binlog_format;
+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= statement;
+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
+SELECT @@session.binlog_format;
+@@session.binlog_format
+ROW
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
=== added file 'mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test'
--- a/mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/binlog/t/binlog_format_switch_inside_trans.test 2009-12-18 05:47:16 +0000
@@ -0,0 +1,102 @@
+#
+# BUG#47863
+# This test verifies if the session variable 'binlog_format'
+# is read-only inside a transaction and in sub-statements.
+#
+
+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.");
+set @save_binlog_format= @@global.binlog_format;
+create table t1 (a int) engine= myisam;
+create table t2 (a int) engine= innodb;
+
+SELECT @@session.binlog_format;
+SET AUTOCOMMIT=1;
+set @@session.binlog_format= statement;
+SELECT @@session.binlog_format;
+
+insert into t1 values (1);
+begin;
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+ set @@session.binlog_format= mixed;
+
+ insert into t2 select * from t1;
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+ set @@session.binlog_format= row;
+
+ insert into t1 values (2);
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+ set @@session.binlog_format= statement;
+
+ insert into t2 values (3);
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+ set @@session.binlog_format= row;
+commit;
+
+set AUTOCOMMIT=0;
+set @@session.binlog_format= row;
+SELECT @@session.binlog_format;
+
+insert into t1 values (4);
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+set @@session.binlog_format= statement;
+SELECT @@session.binlog_format;
+
+insert into t2 values (5);
+--echo # Check the session variable 'binlog_format' is read-only
+--echo # inside a transaction.
+--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT
+set @@session.binlog_format= row;
+SELECT @@session.binlog_format;
+
+begin;
+ insert into t2 values (6);
+--echo # Check the global variable 'binlog_format' is writable
+--echo # inside a transaction.
+ SELECT @@global.binlog_format;
+ set @@global.binlog_format= statement;
+ SELECT @@global.binlog_format;
+commit;
+
+set @@global.binlog_format= @save_binlog_format;
+
+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= statement;
+ 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 ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT
+insert into t3(a,b) values(1,1);
+SELECT @@session.binlog_format;
+
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
+drop table t5;
+
=== 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-18 05:47:16 +0000
@@ -1245,6 +1245,14 @@ void fix_slave_exec_mode(enum_var_type t
bool sys_var_thd_binlog_format::check(THD *thd, set_var *var) {
/*
+ Make the session variable 'binlog_format' read-only inside a transaction.
+ */
+ if (thd->active_transaction() && (var->type == OPT_SESSION))
+ {
+ my_error(ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT, MYF(0));
+ return 1;
+ }
+ /*
All variables that affect writing to binary log (either format or
turning logging on and off) use the same checking. We call the
superclass ::check function to assign the variable correctly, and
=== 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-18 05:47:16 +0000
@@ -6249,3 +6249,5 @@ 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 modify @@session.binlog_format inside a transaction"
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20091218054716-mgo0nn6biiba6lm0.bundle
