Debian Codegraph @

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

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