1
# the file to be sourced from binlog.binlog_mix_innodb_myisam
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
8
# testing appearence of insert into temp_table in binlog.
9
# There are two branches of execution that require different setup.
11
# checking binlog content filled with row-based events due to
12
# a used stored function modifies non-transactional table
18
create temporary table tt (a int unique);
19
create table ti (a int) engine=innodb;
25
insert into ti values (1);
26
insert into ti values (2) ;
27
insert into tt select * from ti;
32
select count(*) from tt /* 2 */;
33
source include/show_binlog_events.inc;
34
select count(*) from ti /* zero */;
35
insert into ti select * from tt;
36
select * from ti /* that is what slave would miss - bug#28960 */;
39
## send_error() branch
41
delete from tt where a=1;
47
insert into ti values (1);
48
insert into ti values (2) /* to make the dup error in the following */;
50
insert into tt select * from ti /* one affected and error */;
55
source include/show_binlog_events.inc; # nothing in binlog with row bilog format
56
select count(*) from ti /* zero */;
57
insert into ti select * from tt;
58
select * from tt /* that is what otherwise slave missed - the bug */;
64
# Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
66
# Testing asserts: if there is a side effect of modifying non-transactional
67
# table thd->no_trans_update.stmt must be TRUE;
68
# the assert is active with debug build
72
drop function if exists bug27417;
73
drop table if exists t1,t2;
76
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
78
CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
81
create function bug27417(n int)
84
insert into t1 values (null);
93
insert into t2 values (bug27417(1));
94
insert into t2 select bug27417(2);
98
insert into t2 values (bug27417(2));
99
source include/show_binlog_events.inc; #only (!) with fixes for #23333 will show there is the query
100
select count(*) from t1 /* must be 3 */;
103
select count(*) from t2;
104
delete from t2 where a=bug27417(3);
105
select count(*) from t2 /* nothing got deleted */;
106
source include/show_binlog_events.inc; # the query must be in regardless of #23333
107
select count(*) from t1 /* must be 5 */;
110
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
112
select count(*) from t1 /* must be 7 */;
114
# function bug27417 remains for the following testing of bug#23333
118
# Bug#23333 using the patch (and the test) for bug#27471
119
# throughout the bug tests
120
# t1 - non-trans side effects gatherer;
121
# t2 - transactional table;
123
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
124
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
125
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
126
CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
127
CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
136
insert into t2 values (1);
142
insert into t2 values (bug27417(1));
146
source include/show_binlog_events.inc; # must be event of the query
147
select count(*) from t1 /* must be 1 */;
156
insert into t2 values (2);
162
insert into t2 select bug27417(1) union select bug27417(2);
166
source include/show_binlog_events.inc; # must be events of the query
167
select count(*) from t1 /* must be 2 */;
170
# UPDATE inc multi-update
175
insert into t3 values (1,1),(2,3),(3,4);
180
update t3 set b=b+bug27417(1);
183
source include/show_binlog_events.inc; # must be events of the query
184
select count(*) from t1 /* must be 2 */;
186
## multi_update::send_eof() branch
191
insert into t3 values (1,1);
192
insert into t4 values (1,1),(2,2);
198
UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
201
source include/show_binlog_events.inc; # the offset must denote there is the query
202
select count(*) from t1 /* must be 4 */;
204
## send_error() branch of multi_update
210
insert into t3 values (1,1),(2,2);
211
insert into t4 values (1,1),(2,2);
217
UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
220
select count(*) from t1 /* must be 1 */;
227
# DELETE incl multi-delete
234
insert into t2 values (1);
235
insert into t3 values (1,1);
236
create trigger trg_del before delete on t2 for each row
237
insert into t3 values (bug27417(1), 2);
244
source include/show_binlog_events.inc; # the offset must denote there is the query
245
select count(*) from t1 /* must be 1 */;
248
drop trigger trg_del;
254
create trigger trg_del_t2 after delete on t2 for each row
255
insert into t1 values (1);
256
insert into t2 values (2),(3);
257
insert into t5 values (1),(2);
262
delete t2.* from t2,t5 where t2.a=t5.a + 1;
265
source include/show_binlog_events.inc; # must be events of the query
266
select count(*) from t1 /* must be 1 */;
275
create table t4 (a int default 0, b int primary key) engine=innodb;
276
insert into t4 values (0, 17);
281
load data infile '../../std_data/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
284
select count(*) from t1 /* must be 2 */;
285
source include/show_binlog_events.inc; # must be events of the query
292
drop trigger trg_del_t2;
293
drop table t1,t2,t3,t4,t5;
294
drop function bug27417;