Debian Codegraph @

mysql-test/suite/binlog/t/binlog_switch_inside_trans.test

raw
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
8 source include/have_myisam.inc;
9 source include/have_binlog_format_row.inc;
10
11 set @save_binlog_format= @@global.binlog_format;
12 set @save_binlog_dirct= @@global.binlog_direct_non_transactional_updates;
13 create table t1 (a int) engine= myisam;
14 create table t2 (a int) engine= innodb;
15
16 SELECT @@session.binlog_format;
17 SELECT @@session.binlog_direct_non_transactional_updates;
18 SELECT @@session.sql_log_bin;
19 SET 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.
24 set @@session.binlog_format= statement;
25 set @@session.binlog_direct_non_transactional_updates= TRUE;
26 set @@session.sql_log_bin= FALSE;
27 SELECT @@session.binlog_format;
28 SELECT @@session.binlog_direct_non_transactional_updates;
29 SELECT @@session.sql_log_bin;
30
31 begin;
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
41 set @@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
53 set @@session.sql_log_bin= FALSE;
54 commit;
55
56 begin;
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
67 set @@session.sql_log_bin= FALSE;
68 commit;
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.
74 set AUTOCOMMIT=0;
75 set @@session.binlog_format= 'row';
76 set @@session.binlog_direct_non_transactional_updates= FALSE;
77 set @@session.sql_log_bin= TRUE;
78 SELECT @@session.binlog_format;
79 SELECT @@session.binlog_direct_non_transactional_updates;
80 SELECT @@session.sql_log_bin;
81
82 insert 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
89 set @@session.binlog_format= statement;
90 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
91 set @@session.binlog_direct_non_transactional_updates= TRUE;
92 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN
93 set @@session.sql_log_bin= FALSE;
94 SELECT @@session.binlog_format;
95 SELECT @@session.binlog_direct_non_transactional_updates;
96 SELECT @@session.sql_log_bin;
97 commit;
98
99 insert 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
106 set @@session.binlog_format= statement;
107 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_BINLOG_DIRECT
108 set @@session.binlog_direct_non_transactional_updates= TRUE;
109 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SQL_LOG_BIN
110 set @@session.sql_log_bin= FALSE;
111 SELECT @@session.binlog_format;
112 SELECT @@session.binlog_direct_non_transactional_updates;
113 SELECT @@session.sql_log_bin;
114 commit;
115
116 begin;
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;
127 commit;
128
129 set @@global.binlog_format= @save_binlog_format;
130 set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct;
131
132 create table t3(a int, b int) engine= innodb;
133 create table t4(a int) engine= innodb;
134 create table t5(a int) engine= innodb;
135 delimiter |;
136 eval 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);
141 end |
142 delimiter ;|
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
148 insert into t3(a,b) values(1,1);
149 SELECT @@session.binlog_format;
150
151 create table t6(a int, b int) engine= innodb;
152 create table t7(a int) engine= innodb;
153 create table t8(a int) engine= innodb;
154 delimiter |;
155 eval 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);
160 end |
161 delimiter ;|
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
168 insert into t6(a,b) values(1,1);
169 SELECT @@session.binlog_direct_non_transactional_updates;
170
171 create table t9(a int, b int) engine= innodb;
172 create table t10(a int) engine= innodb;
173 create table t11(a int) engine= innodb;
174 delimiter |;
175 eval 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);
180 end |
181 delimiter ;|
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
187 insert into t9(a,b) values(1,1);
188 SELECT @@session.sql_log_bin;
189
190 drop table t1;
191 drop table t2;
192 drop table t3;
193 drop table t4;
194 drop table t5;
195 drop table t6;
196 drop table t7;
197 drop table t8;
198 drop table t9;
199 drop table t10;
200 drop table t11;