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 18create temporary table tt (a int unique); 19create table ti (a int) engine=innodb; 20reset master; 21 22# action 23 24begin; 25insert into ti values (1); 26insert into ti values (2) ; 27# This is SAFE because --binlog-direct-non-transactional-updates=FALSE 28insert into tt select * from ti; 29rollback; 30 31# check 32 33select count(*) from tt /* 2 */; 34source include/show_binlog_events.inc; 35select count(*) from ti /* zero */; 36insert into ti select * from tt; 37select * from ti /* that is what slave would miss - bug#28960 */; 38 39 40## send_error() branch 41delete from ti; 42delete from tt where a=1; 43reset master; 44 45# action 46 47begin; 48insert into ti values (1); 49insert into ti values (2) /* to make the dup error in the following */; 50--error ER_DUP_ENTRY 51insert into tt select * from ti /* one affected and error */; 52rollback; 53 54# check 55 56source include/show_binlog_events.inc; # nothing in binlog with row bilog format 57select count(*) from ti /* zero */; 58insert into ti select * from tt; 59select * from tt /* that is what otherwise slave missed - the bug */; 60 61drop 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 73drop function if exists bug27417; 74drop table if exists t1,t2; 75--enable_warnings 76# side effect table 77CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 78# target tables 79CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a)); 80 81delimiter |; 82create function bug27417(n int) 83RETURNS int(11) 84begin 85 insert into t1 values (null); 86 return n; 87end| 88delimiter ;| 89 90reset master; 91 92# execute 93 94insert into t2 values (bug27417(1)); 95insert into t2 select bug27417(2); 96reset master; 97 98--error ER_DUP_ENTRY 99insert into t2 values (bug27417(2)); 100source include/show_binlog_events.inc; #only (!) with fixes for #23333 will show there is the query 101select count(*) from t1 /* must be 3 */; 102 103reset master; 104select count(*) from t2; 105delete from t2 where a=bug27417(3); 106select count(*) from t2 /* nothing got deleted */; 107source include/show_binlog_events.inc; # the query must be in regardless of #23333 108select count(*) from t1 /* must be 5 */; 109 110--enable_info 111delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */; 112--disable_info 113select count(*) from t1 /* must be 7 */; 114 115# function bug27417 remains for the following testing of bug#23333 116drop 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# 124CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM; 125CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB; 126CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM; 127CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb; 128CREATE 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 293drop trigger trg_del_t2; 294drop table t1,t2,t3,t4,t5; 295drop function bug27417; 296