Debian Codegraph @

mysql-test/suite/binlog/include/mix_innodb_myisam_side_effects.test

raw
1 # the file to be sourced from binlog.binlog_mix_innodb_myisam
2
3 #
4 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
5 # bug #28960 non-trans temp table changes with insert .. select
6 # not binlogged after rollback
7 #
8 # testing appearence of insert into temp_table in binlog.
9 # There are two branches of execution that require different setup.
10
11 # checking binlog content filled with row-based events due to
12 # a used stored function modifies non-transactional table
13
14 ## send_eof() branch
15
16 # prepare
17
18 create temporary table tt (a int unique);
19 create table ti (a int) engine=innodb;
20 reset master;
21
22 # action
23
24 begin;
25 insert into ti values (1);
26 insert into ti values (2) ;
27 # This is SAFE because --binlog-direct-non-transactional-updates=FALSE
28 insert into tt select * from ti;
29 rollback;
30
31 # check
32
33 select count(*) from tt /* 2 */;
34 source include/show_binlog_events.inc;
35 select count(*) from ti /* zero */;
36 insert into ti select * from tt;
37 select * from ti /* that is what slave would miss - bug#28960 */;
38
39
40 ## send_error() branch
41 delete from ti;
42 delete from tt where a=1;
43 reset master;
44
45 # action
46
47 begin;
48 insert into ti values (1);
49 insert into ti values (2) /* to make the dup error in the following */;
50 --error ER_DUP_ENTRY
51 insert into tt select * from ti /* one affected and error */;
52 rollback;
53
54 # check
55
56 source include/show_binlog_events.inc; # nothing in binlog with row bilog format
57 select count(*) from ti /* zero */;
58 insert into ti select * from tt;
59 select * from tt /* that is what otherwise slave missed - the bug */;
60
61 drop table ti;
62
63
64 #
65 # Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
66 #
67 # Testing asserts: if there is a side effect of modifying non-transactional
68 # table thd->no_trans_update.stmt must be TRUE;
69 # the assert is active with debug build
70 #
71
72 --disable_warnings
73 drop function if exists bug27417;
74 drop table if exists t1,t2;
75 --enable_warnings
76 # side effect table
77 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
78 # target tables
79 CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
80
81 delimiter |;
82 create function bug27417(n int)
83 RETURNS int(11)
84 begin
85 insert into t1 values (null);
86 return n;
87 end|
88 delimiter ;|
89
90 reset master;
91
92 # execute
93
94 insert into t2 values (bug27417(1));
95 insert into t2 select bug27417(2);
96 reset master;
97
98 --error ER_DUP_ENTRY
99 insert into t2 values (bug27417(2));
100 source include/show_binlog_events.inc; #only (!) with fixes for #23333 will show there is the query
101 select count(*) from t1 /* must be 3 */;
102
103 reset master;
104 select count(*) from t2;
105 delete from t2 where a=bug27417(3);
106 select count(*) from t2 /* nothing got deleted */;
107 source include/show_binlog_events.inc; # the query must be in regardless of #23333
108 select count(*) from t1 /* must be 5 */;
109
110 --enable_info
111 delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
112 --disable_info
113 select count(*) from t1 /* must be 7 */;
114
115 # function bug27417 remains for the following testing of bug#23333
116 drop table t1,t2;
117
118 #
119 # Bug#23333 using the patch (and the test) for bug#27471
120 # throughout the bug tests
121 # t1 - non-trans side effects gatherer;
122 # t2 - transactional table;
123 #
124 CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
125 CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
126 CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
127 CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
128 CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
129
130
131 #
132 # INSERT
133 #
134
135 # prepare
136
137 insert into t2 values (1);
138 reset master;
139
140 # execute
141
142 --error ER_DUP_ENTRY
143 insert into t2 values (bug27417(1));
144
145 # check
146
147 source include/show_binlog_events.inc; # must be event of the query
148 select count(*) from t1 /* must be 1 */;
149
150 #
151 # INSERT SELECT
152 #
153
154 # prepare
155 delete from t1;
156 delete from t2;
157 insert into t2 values (2);
158 reset master;
159
160 # execute
161
162 --error ER_DUP_ENTRY
163 insert into t2 select bug27417(1) union select bug27417(2);
164
165 # check
166
167 source include/show_binlog_events.inc; # must be events of the query
168 select count(*) from t1 /* must be 2 */;
169
170 #
171 # UPDATE inc multi-update
172 #
173
174 # prepare
175 delete from t1;
176 insert into t3 values (1,1),(2,3),(3,4);
177 reset master;
178
179 # execute
180 --error ER_DUP_ENTRY
181 update t3 set b=b+bug27417(1);
182
183 # check
184 source include/show_binlog_events.inc; # must be events of the query
185 select count(*) from t1 /* must be 2 */;
186
187 ## multi_update::send_eof() branch
188
189 # prepare
190 delete from t3;
191 delete from t4;
192 insert into t3 values (1,1);
193 insert into t4 values (1,1),(2,2);
194
195 reset master;
196
197 # execute
198 --error ER_DUP_ENTRY
199 UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
200
201 # check
202 source include/show_binlog_events.inc; # the offset must denote there is the query
203 select count(*) from t1 /* must be 4 */;
204
205 ## send_error() branch of multi_update
206
207 # prepare
208 delete from t1;
209 delete from t3;
210 delete from t4;
211 insert into t3 values (1,1),(2,2);
212 insert into t4 values (1,1),(2,2);
213
214 reset master;
215
216 # execute
217 --error ER_DUP_ENTRY
218 UPDATE t3,t4 SET t3.a = t4.a + bug27417(1) where t3.a = 1;
219
220 # check
221 select count(*) from t1 /* must be 1 */;
222
223 # cleanup
224 drop table t4;
225
226
227 #
228 # DELETE incl multi-delete
229 #
230
231 # prepare
232 delete from t1;
233 delete from t2;
234 delete from t3;
235 insert into t2 values (1);
236 insert into t3 values (1,1);
237 create trigger trg_del before delete on t2 for each row
238 insert into t3 values (bug27417(1), 2);
239 reset master;
240
241 # execute
242 --error ER_DUP_ENTRY
243 delete from t2;
244 # check
245 source include/show_binlog_events.inc; # the offset must denote there is the query
246 select count(*) from t1 /* must be 1 */;
247
248 # cleanup
249 drop trigger trg_del;
250
251 # prepare
252 delete from t1;
253 delete from t2;
254 delete from t5;
255 create trigger trg_del_t2 after delete on t2 for each row
256 insert into t1 values (1);
257 insert into t2 values (2),(3);
258 insert into t5 values (1),(2);
259 reset master;
260
261 # execute
262 --error ER_DUP_ENTRY
263 delete t2.* from t2,t5 where t2.a=t5.a + 1;
264
265 # check
266 source include/show_binlog_events.inc; # must be events of the query
267 select count(*) from t1 /* must be 1 */;
268
269
270 #
271 # LOAD DATA
272 #
273
274 # prepare
275 delete from t1;
276 create table t4 (a int default 0, b int primary key) engine=innodb;
277 insert into t4 values (0, 17);
278 reset master;
279
280 # execute
281 --error ER_DUP_ENTRY
282 load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
283 # check
284 select * from t4;
285 select count(*) from t1 /* must be 2 */;
286 source include/show_binlog_events.inc; # must be events of the query
287
288 #
289 # bug#23333 cleanup
290 #
291
292
293 drop trigger trg_del_t2;
294 drop table t1,t2,t3,t4,t5;
295 drop function bug27417;