Debian Codegraph @

mysql-test/suite/binlog/r/binlog_switch_inside_trans.result

raw
1 set @save_binlog_format= @@global.binlog_format;
2 Warnings:
3 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
4 set @save_binlog_dirct= @@global.binlog_direct_non_transactional_updates;
5 create table t1 (a int) engine= myisam;
6 create table t2 (a int) engine= innodb;
7 SELECT @@session.binlog_format;
8 @@session.binlog_format
9 ROW
10 Warnings:
11 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
12 SELECT @@session.binlog_direct_non_transactional_updates;
13 @@session.binlog_direct_non_transactional_updates
14 0
15 SELECT @@session.sql_log_bin;
16 @@session.sql_log_bin
17 1
18 SET AUTOCOMMIT=1;
19 # Test that the session variable 'binlog_format',
20 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin'
21 # are writable outside a transaction.
22 # Current session values are ROW, FALSE, TRUE, respectively.
23 set @@session.binlog_format= statement;
24 Warnings:
25 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
26 set @@session.binlog_direct_non_transactional_updates= TRUE;
27 set @@session.sql_log_bin= FALSE;
28 SELECT @@session.binlog_format;
29 @@session.binlog_format
30 STATEMENT
31 Warnings:
32 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
33 SELECT @@session.binlog_direct_non_transactional_updates;
34 @@session.binlog_direct_non_transactional_updates
35 1
36 SELECT @@session.sql_log_bin;
37 @@session.sql_log_bin
38 0
39 begin;
40 # Test that the session variable 'binlog_format',
41 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
42 # read-only inside a transaction with no preceding updates.
43 # Current session values are STATEMENT, TRUE, FALSE, respectively.
44 set @@session.binlog_format= mixed;
45 ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
46 set @@session.binlog_direct_non_transactional_updates= FALSE;
47 ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction
48 set @@session.sql_log_bin= FALSE;
49 ERROR HY000: Cannot modify @@session.sql_log_bin inside a transaction
50 insert into t2 values (1);
51 # Test that the session variable 'binlog_format',
52 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
53 # read-only inside a transaction with preceding transactional updates.
54 # Current session values are STATEMENT, TRUE and FALSE, respectively.
55 set @@session.binlog_format= 'row';
56 ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
57 set @@session.binlog_direct_non_transactional_updates= FALSE;
58 ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction
59 set @@session.sql_log_bin= FALSE;
60 ERROR HY000: Cannot modify @@session.sql_log_bin inside a transaction
61 commit;
62 begin;
63 insert into t1 values (2);
64 # Test that the session variable 'binlog_format'
65 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
66 # read-only inside a transaction with preceding non-transactional updates.
67 # Current session values are STATEMENT, TRUE, FALSE, respectively.
68 set @@session.binlog_format= mixed;
69 ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
70 set @@session.binlog_direct_non_transactional_updates= FALSE;
71 ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction
72 set @@session.sql_log_bin= FALSE;
73 ERROR HY000: Cannot modify @@session.sql_log_bin inside a transaction
74 commit;
75 # Test that the session variable 'binlog_format',
76 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
77 # writable when AUTOCOMMIT=0, before a transaction has started.
78 # Current session values are STATEMENT, TRUE, FALSE, respectively.
79 set AUTOCOMMIT=0;
80 set @@session.binlog_format= 'row';
81 Warnings:
82 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
83 set @@session.binlog_direct_non_transactional_updates= FALSE;
84 set @@session.sql_log_bin= TRUE;
85 SELECT @@session.binlog_format;
86 @@session.binlog_format
87 ROW
88 Warnings:
89 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
90 SELECT @@session.binlog_direct_non_transactional_updates;
91 @@session.binlog_direct_non_transactional_updates
92 0
93 SELECT @@session.sql_log_bin;
94 @@session.sql_log_bin
95 1
96 insert into t1 values (3);
97 # Test that the session variable 'binlog_format',
98 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
99 # read-only inside an AUTOCOMMIT=0 transaction
100 # with preceding non-transactional updates.
101 # Current session values are ROW, FALSE, TRUE, respectively.
102 set @@session.binlog_format= statement;
103 ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
104 set @@session.binlog_direct_non_transactional_updates= TRUE;
105 ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction
106 set @@session.sql_log_bin= FALSE;
107 ERROR HY000: Cannot modify @@session.sql_log_bin inside a transaction
108 SELECT @@session.binlog_format;
109 @@session.binlog_format
110 ROW
111 Warnings:
112 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
113 SELECT @@session.binlog_direct_non_transactional_updates;
114 @@session.binlog_direct_non_transactional_updates
115 0
116 SELECT @@session.sql_log_bin;
117 @@session.sql_log_bin
118 1
119 commit;
120 insert into t2 values (4);
121 # Test that the session variable 'binlog_format',
122 # 'binlog_direct_non_transactional_updates' and 'sql_log_bin' are
123 # read-only inside an AUTOCOMMIT=0 transaction with
124 # preceding transactional updates.
125 # Current session values are ROW, FALSE, TRUE, respectively.
126 set @@session.binlog_format= statement;
127 ERROR HY000: Cannot modify @@session.binlog_format inside a transaction
128 set @@session.binlog_direct_non_transactional_updates= TRUE;
129 ERROR HY000: Cannot modify @@session.binlog_direct_non_transactional_updates inside a transaction
130 set @@session.sql_log_bin= FALSE;
131 ERROR HY000: Cannot modify @@session.sql_log_bin inside a transaction
132 SELECT @@session.binlog_format;
133 @@session.binlog_format
134 ROW
135 Warnings:
136 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
137 SELECT @@session.binlog_direct_non_transactional_updates;
138 @@session.binlog_direct_non_transactional_updates
139 0
140 SELECT @@session.sql_log_bin;
141 @@session.sql_log_bin
142 1
143 commit;
144 begin;
145 insert into t2 values (5);
146 # Test that the global variable 'binlog_format' and
147 # 'binlog_direct_non_transactional_updates' are
148 # writable inside a transaction.
149 # Current session values are ROW, FALSE, TRUE respectively.
150 SELECT @@global.binlog_format;
151 @@global.binlog_format
152 ROW
153 Warnings:
154 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
155 set @@global.binlog_format= statement;
156 Warnings:
157 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
158 set @@global.binlog_direct_non_transactional_updates= TRUE;
159 SELECT @@global.binlog_format;
160 @@global.binlog_format
161 STATEMENT
162 Warnings:
163 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
164 SELECT @@global.binlog_direct_non_transactional_updates;
165 @@global.binlog_direct_non_transactional_updates
166 1
167 commit;
168 set @@global.binlog_format= @save_binlog_format;
169 Warnings:
170 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
171 set @@global.binlog_direct_non_transactional_updates= @save_binlog_dirct;
172 create table t3(a int, b int) engine= innodb;
173 create table t4(a int) engine= innodb;
174 create table t5(a int) engine= innodb;
175 create trigger tr1 after insert on t3 for each row begin
176 insert into t4(a) values(1);
177 set @@session.binlog_format= statement;
178 insert into t4(a) values(2);
179 insert into t5(a) values(3);
180 end |
181 # Test that the session variable 'binlog_format' is read-only
182 # in sub-statements.
183 # Current session value is ROW.
184 insert into t3(a,b) values(1,1);
185 ERROR HY000: Cannot change the binary logging format inside a stored function or trigger
186 SELECT @@session.binlog_format;
187 @@session.binlog_format
188 ROW
189 Warnings:
190 Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
191 create table t6(a int, b int) engine= innodb;
192 create table t7(a int) engine= innodb;
193 create table t8(a int) engine= innodb;
194 create trigger tr2 after insert on t6 for each row begin
195 insert into t7(a) values(1);
196 set @@session.binlog_direct_non_transactional_updates= TRUE;
197 insert into t7(a) values(2);
198 insert into t8(a) values(3);
199 end |
200 # Test that the session variable
201 # 'binlog_direct_non_transactional_updates' is
202 # read-only in sub-statements.
203 # Current session value is FALSE.
204 insert into t6(a,b) values(1,1);
205 ERROR HY000: Cannot change the binlog direct flag inside a stored function or trigger
206 SELECT @@session.binlog_direct_non_transactional_updates;
207 @@session.binlog_direct_non_transactional_updates
208 0
209 create table t9(a int, b int) engine= innodb;
210 create table t10(a int) engine= innodb;
211 create table t11(a int) engine= innodb;
212 create trigger tr3 after insert on t9 for each row begin
213 insert into t10(a) values(1);
214 set @@session.sql_log_bin= TRUE;
215 insert into t10(a) values(2);
216 insert into t11(a) values(3);
217 end |
218 # Test that the session variable 'sql_log_bin' is
219 # read-only in sub-statements.
220 # Current session value is FALSE.
221 insert into t9(a,b) values(1,1);
222 ERROR HY000: Cannot change the sql_log_bin inside a stored function or trigger
223 SELECT @@session.sql_log_bin;
224 @@session.sql_log_bin
225 1
226 drop table t1;
227 drop table t2;
228 drop table t3;
229 drop table t4;
230 drop table t5;
231 drop table t6;
232 drop table t7;
233 drop table t8;
234 drop table t9;
235 drop table t10;
236 drop table t11;