1# 2# BUG#47863 3# This test verifies if the session variable 'binlog_format' and 4# 'binlog_direct_non_transactional_updates' are read-only inside 5# a transaction and in sub-statements. 6# 7 8source include/have_innodb.inc; 9source include/have_binlog_format_row.inc; 10 11set @save_binlog_format= @@global.binlog_format; 12set @save_binlog_dirct= @@global.binlog_direct_non_transactional_updates; 13create table t1 (a int) engine= myisam; 14create table t2 (a int) engine= innodb; 15 16SELECT @@session.binlog_format; 17SELECT @@session.binlog_direct_non_transactional_updates; 18SELECT @@session.sql_log_bin; 19SET AUTOCOMMIT=1; 20--echo # Test that the session variable 'binlog_format', 21--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' 22--echo # are writable outside a transaction. 23--echo # Current session values are ROW, FALSE, TRUE, respectively. 24set @@session.binlog_format= statement; 25set @@session.binlog_direct_non_transactional_updates= TRUE; 26set @@session.sql_log_bin= FALSE; 27SELECT @@session.binlog_format; 28SELECT @@session.binlog_direct_non_transactional_updates; 29SELECT @@session.sql_log_bin; 30 31begin; 32--echo # Test that the session variable 'binlog_format', 33--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 34--echo # read-only inside a transaction with no preceding updates. 35--echo # Current session values are STATEMENT, TRUE, FALSE, respectively. 36--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 37 set @@session.binlog_format= mixed; 38--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT 39 set @@session.binlog_direct_non_transactional_updates= FALSE; 40--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN 41set @@session.sql_log_bin= FALSE; 42 43 insert into t2 values (1); 44--echo # Test that the session variable 'binlog_format', 45--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 46--echo # read-only inside a transaction with preceding transactional updates. 47--echo # Current session values are STATEMENT, TRUE and FALSE, respectively. 48--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 49 set @@session.binlog_format= row; 50--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT 51 set @@session.binlog_direct_non_transactional_updates= FALSE; 52--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN 53set @@session.sql_log_bin= FALSE; 54commit; 55 56begin; 57 insert into t1 values (2); 58--echo # Test that the session variable 'binlog_format' 59--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 60--echo # read-only inside a transaction with preceding non-transactional updates. 61--echo # Current session values are STATEMENT, TRUE, FALSE, respectively. 62--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 63 set @@session.binlog_format= mixed; 64--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT 65 set @@session.binlog_direct_non_transactional_updates= FALSE; 66--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN 67set @@session.sql_log_bin= FALSE; 68commit; 69 70--echo # Test that the session variable 'binlog_format', 71--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 72--echo # writable when AUTOCOMMIT=0, before a transaction has started. 73--echo # Current session values are STATEMENT, TRUE, FALSE, respectively. 74set AUTOCOMMIT=0; 75set @@session.binlog_format= row; 76set @@session.binlog_direct_non_transactional_updates= FALSE; 77set @@session.sql_log_bin= TRUE; 78SELECT @@session.binlog_format; 79SELECT @@session.binlog_direct_non_transactional_updates; 80SELECT @@session.sql_log_bin; 81 82insert into t1 values (3); 83--echo # Test that the session variable 'binlog_format', 84--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 85--echo # read-only inside an AUTOCOMMIT=0 transaction 86--echo # with preceding non-transactional updates. 87--echo # Current session values are ROW, FALSE, TRUE, respectively. 88--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 89set @@session.binlog_format= statement; 90--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT 91set @@session.binlog_direct_non_transactional_updates= TRUE; 92--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN 93set @@session.sql_log_bin= FALSE; 94SELECT @@session.binlog_format; 95SELECT @@session.binlog_direct_non_transactional_updates; 96SELECT @@session.sql_log_bin; 97commit; 98 99insert into t2 values (4); 100--echo # Test that the session variable 'binlog_format', 101--echo # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are 102--echo # read-only inside an AUTOCOMMIT=0 transaction with 103--echo # preceding transactional updates. 104--echo # Current session values are ROW, FALSE, TRUE, respectively. 105--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_FORMAT 106set @@session.binlog_format= statement; 107--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT 108set @@session.binlog_direct_non_transactional_updates= TRUE; 109--error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN 110set @@session.sql_log_bin= FALSE; 111SELECT @@session.binlog_format; 112SELECT @@session.binlog_direct_non_transactional_updates; 113SELECT @@session.sql_log_bin; 114commit; 115 116begin; 117 insert into t2 values (5); 118--echo # Test that the global variable 'binlog_format' and 119--echo # 'binlog_direct_non_transactional_updates' are 120--echo # writable inside a transaction. 121--echo # Current session values are ROW, FALSE, TRUE respectively. 122 SELECT @@global.binlog_format; 123 set @@global.binlog_format= statement; 124 set @@global.binlog_direct_non_transactional_updates= TRUE; 125 SELECT @@global.binlog_format; 126 SELECT @@global.binlog_direct_non_transactional_updates; 127commit; 128 129set @@global.binlog_format= @save_binlog_format; 130set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct; 131 132create table t3(a int, b int) engine= innodb; 133create table t4(a int) engine= innodb; 134create table t5(a int) engine= innodb; 135delimiter |; 136eval create trigger tr1 after insert on t3 for each row begin 137 insert into t4(a) values(1); 138 set @@session.binlog_format= statement; 139 insert into t4(a) values(2); 140 insert into t5(a) values(3); 141end | 142delimiter ;| 143 144--echo # Test that the session variable 'binlog_format' is read-only 145--echo # in sub-statements. 146--echo # Current session value is ROW. 147--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT 148insert into t3(a,b) values(1,1); 149SELECT @@session.binlog_format; 150 151create table t6(a int, b int) engine= innodb; 152create table t7(a int) engine= innodb; 153create table t8(a int) engine= innodb; 154delimiter |; 155eval create trigger tr2 after insert on t6 for each row begin 156 insert into t7(a) values(1); 157 set @@session.binlog_direct_non_transactional_updates= TRUE; 158 insert into t7(a) values(2); 159 insert into t8(a) values(3); 160end | 161delimiter ;| 162 163--echo # Test that the session variable 164--echo # 'binlog_direct_non_transactional_updates' is 165--echo # read-only in sub-statements. 166--echo # Current session value is FALSE. 167--error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_DIRECT 168insert into t6(a,b) values(1,1); 169SELECT @@session.binlog_direct_non_transactional_updates; 170 171create table t9(a int, b int) engine= innodb; 172create table t10(a int) engine= innodb; 173create table t11(a int) engine= innodb; 174delimiter |; 175eval create trigger tr3 after insert on t9 for each row begin 176 insert into t10(a) values(1); 177 set @@session.sql_log_bin= TRUE; 178 insert into t10(a) values(2); 179 insert into t11(a) values(3); 180end | 181delimiter ;| 182 183--echo # Test that the session variable 'sql_log_bin' is 184--echo # read-only in sub-statements. 185--echo # Current session value is FALSE. 186--error ER_STORED_FUNCTION_PREVENTS_SWITCH_SQL_LOG_BIN 187insert into t9(a,b) values(1,1); 188SELECT @@session.sql_log_bin; 189 190drop table t1; 191drop table t2; 192drop table t3; 193drop table t4; 194drop table t5; 195drop table t6; 196drop table t7; 197drop table t8; 198drop table t9; 199drop table t10; 200drop table t11; 201