mix_innodb_myisam_binlog.result 16.9 KB
Newer Older
unknown's avatar
unknown committed
1
drop table if exists t1, t2;
unknown's avatar
unknown committed
2 3
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
unknown's avatar
unknown committed
4 5
reset master;
begin;
unknown's avatar
unknown committed
6 7
insert into t1 values(1);
insert into t2 select * from t1;
unknown's avatar
unknown committed
8
commit;
unknown's avatar
unknown committed
9
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
10
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
11 12 13 14
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(1)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
15 16
delete from t1;
delete from t2;
unknown's avatar
unknown committed
17 18
reset master;
begin;
unknown's avatar
unknown committed
19 20
insert into t1 values(2);
insert into t2 select * from t1;
unknown's avatar
unknown committed
21
rollback;
22 23
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
unknown's avatar
unknown committed
24
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
25
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
26 27 28 29
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(2)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Query	#	#	use `test`; ROLLBACK
unknown's avatar
unknown committed
30 31
delete from t1;
delete from t2;
unknown's avatar
unknown committed
32 33
reset master;
begin;
unknown's avatar
unknown committed
34
insert into t1 values(3);
unknown's avatar
unknown committed
35
savepoint my_savepoint;
unknown's avatar
unknown committed
36 37
insert into t1 values(4);
insert into t2 select * from t1;
unknown's avatar
unknown committed
38
rollback to savepoint my_savepoint;
39 40
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
unknown's avatar
unknown committed
41
commit;
unknown's avatar
unknown committed
42
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
43
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
44 45 46 47 48 49 50
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(3)
master-bin.000001	#	Query	#	#	use `test`; savepoint my_savepoint
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(4)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Query	#	#	use `test`; rollback to savepoint my_savepoint
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
51 52
delete from t1;
delete from t2;
unknown's avatar
unknown committed
53 54
reset master;
begin;
unknown's avatar
unknown committed
55
insert into t1 values(5);
unknown's avatar
unknown committed
56
savepoint my_savepoint;
unknown's avatar
unknown committed
57 58
insert into t1 values(6);
insert into t2 select * from t1;
unknown's avatar
unknown committed
59
rollback to savepoint my_savepoint;
60 61
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
unknown's avatar
unknown committed
62
insert into t1 values(7);
unknown's avatar
unknown committed
63
commit;
unknown's avatar
unknown committed
64
select a from t1 order by a;
unknown's avatar
unknown committed
65 66 67
a
5
7
unknown's avatar
unknown committed
68
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
69
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
70 71 72 73 74 75 76 77
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(5)
master-bin.000001	#	Query	#	#	use `test`; savepoint my_savepoint
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(6)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Query	#	#	use `test`; rollback to savepoint my_savepoint
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(7)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
78 79
delete from t1;
delete from t2;
unknown's avatar
unknown committed
80 81 82 83 84
reset master;
select get_lock("a",10);
get_lock("a",10)
1
begin;
unknown's avatar
unknown committed
85 86
insert into t1 values(8);
insert into t2 select * from t1;
unknown's avatar
unknown committed
87 88 89
select get_lock("a",10);
get_lock("a",10)
1
unknown's avatar
unknown committed
90
show binlog events from <binlog_start>;
91
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
92 93 94 95
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(8)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Query	#	#	use `test`; ROLLBACK
unknown's avatar
unknown committed
96 97
delete from t1;
delete from t2;
unknown's avatar
unknown committed
98
reset master;
unknown's avatar
unknown committed
99 100
insert into t1 values(9);
insert into t2 select * from t1;
unknown's avatar
unknown committed
101
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
102
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
103 104 105 106
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(9)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
unknown's avatar
unknown committed
107 108
delete from t1;
delete from t2;
unknown's avatar
unknown committed
109
reset master;
unknown's avatar
unknown committed
110
insert into t1 values(10);
unknown's avatar
unknown committed
111
begin;
unknown's avatar
unknown committed
112
insert into t2 select * from t1;
unknown's avatar
unknown committed
113
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
114
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
115 116 117 118
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(10)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
unknown's avatar
unknown committed
119
insert into t1 values(11);
unknown's avatar
unknown committed
120
commit;
unknown's avatar
unknown committed
121
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
122
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
123 124 125 126 127 128 129
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(10)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(11)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
130
alter table t2 engine=INNODB;
unknown's avatar
unknown committed
131 132
delete from t1;
delete from t2;
unknown's avatar
unknown committed
133 134
reset master;
begin;
unknown's avatar
unknown committed
135 136
insert into t1 values(12);
insert into t2 select * from t1;
unknown's avatar
unknown committed
137
commit;
unknown's avatar
unknown committed
138
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
139
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
140 141 142 143
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(12)
master-bin.000001	#	Query	#	#	use `test`; insert into t2 select * from t1
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
144 145
delete from t1;
delete from t2;
unknown's avatar
unknown committed
146 147
reset master;
begin;
unknown's avatar
unknown committed
148 149
insert into t1 values(13);
insert into t2 select * from t1;
unknown's avatar
unknown committed
150
rollback;
unknown's avatar
unknown committed
151
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
152
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
153 154
delete from t1;
delete from t2;
unknown's avatar
unknown committed
155 156
reset master;
begin;
unknown's avatar
unknown committed
157
insert into t1 values(14);
unknown's avatar
unknown committed
158
savepoint my_savepoint;
unknown's avatar
unknown committed
159 160
insert into t1 values(15);
insert into t2 select * from t1;
unknown's avatar
unknown committed
161 162
rollback to savepoint my_savepoint;
commit;
unknown's avatar
unknown committed
163
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
164
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
165 166 167
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(14)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
unknown's avatar
unknown committed
168 169
delete from t1;
delete from t2;
unknown's avatar
unknown committed
170 171
reset master;
begin;
unknown's avatar
unknown committed
172
insert into t1 values(16);
unknown's avatar
unknown committed
173
savepoint my_savepoint;
unknown's avatar
unknown committed
174 175
insert into t1 values(17);
insert into t2 select * from t1;
unknown's avatar
unknown committed
176
rollback to savepoint my_savepoint;
unknown's avatar
unknown committed
177
insert into t1 values(18);
unknown's avatar
unknown committed
178
commit;
unknown's avatar
unknown committed
179
select a from t1 order by a;
unknown's avatar
unknown committed
180 181 182
a
16
18
unknown's avatar
unknown committed
183
show binlog events from <binlog_start>;
unknown's avatar
unknown committed
184
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
185 186 187 188
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(16)
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(18)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
189 190 191 192 193 194 195 196 197 198 199 200
delete from t1;
delete from t2;
alter table t2 type=MyISAM;
insert into t1 values (1);
begin;
select * from t1 for update;
a
1
select (@before:=unix_timestamp())*0;
(@before:=unix_timestamp())*0
0
begin;
unknown's avatar
unknown committed
201
select * from t1 for update;
202
insert into t2 values (20);
unknown's avatar
unknown committed
203
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
204 205 206 207 208 209
select (@after:=unix_timestamp())*0;
(@after:=unix_timestamp())*0
0
select (@after-@before) >= 2;
(@after-@before) >= 2
1
unknown's avatar
unknown committed
210
drop table t1,t2;
211 212 213 214 215 216 217 218 219 220 221 222
commit;
begin;
create temporary table ti (a int) engine=innodb;
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
insert into ti values(1);
set autocommit=0;
create temporary table t1 (a int) engine=myisam;
commit;
insert t1 values (1);
rollback;
223 224
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
225 226 227 228 229 230 231
create table t0 (n int);
insert t0 select * from t1;
set autocommit=1;
insert into t0 select GET_LOCK("lock1",null);
set autocommit=0;
create table t2 (n int) engine=innodb;
insert into t2 values (3);
232 233 234
select get_lock("lock1",60);
get_lock("lock1",60)
1
unknown's avatar
unknown committed
235
show binlog events from <binlog_start>;
236
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(16)
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values(18)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; delete from t1
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; delete from t2
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; alter table t2 type=MyISAM
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into t1 values (1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; insert into t2 values (20)
master-bin.000001	#	Query	#	#	use `test`; drop table t1,t2
master-bin.000001	#	Query	#	#	use `test`; create temporary table ti (a int) engine=innodb
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; insert into ti values(1)
master-bin.000001	#	Xid	#	#	COMMIT /* XID */
master-bin.000001	#	Query	#	#	use `test`; create temporary table t1 (a int) engine=myisam
master-bin.000001	#	Query	#	#	use `test`; insert t1 values (1)
master-bin.000001	#	Query	#	#	use `test`; create table t0 (n int)
master-bin.000001	#	Query	#	#	use `test`; insert t0 select * from t1
master-bin.000001	#	Query	#	#	use `test`; insert into t0 select GET_LOCK("lock1",null)
master-bin.000001	#	Query	#	#	use `test`; create table t2 (n int) engine=innodb
master-bin.000001	#	Query	#	#	use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`t1`,`test`.`ti`
264 265
do release_lock("lock1");
drop table t0,t2;
266 267 268 269 270 271 272 273 274 275 276 277 278
reset master;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
select get_lock("a",10);
get_lock("a",10)
1
begin;
insert into t1 values(8);
insert into t2 select * from t1;
select get_lock("a",10);
get_lock("a",10)
1
select
279
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
280
is not null;
281
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
282 283 284
is not null
1
select
285 286
@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%",
287
@a not like "%#%error_code=%error_code=%";
288 289
@a like "%#%error_code=0%ROLLBACK\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%" OR
@a like "%#%error_code=0%ROLLBACK\r\n/*!*/;%ROLLBACK /* added by mysqlbinlog */;%"	@a not like "%#%error_code=%error_code=%"
290
1	1
291
drop table t1, t2;
unknown's avatar
unknown committed
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362
create temporary table tt (a int unique);
create table ti (a int) engine=innodb;
reset master;
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	98		
begin;
insert into ti values (1);
insert into ti values (2) ;
insert into tt select * from ti;
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
select count(*) from tt /* 2 */;
count(*)
2
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	507		
show binlog events from 98;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	use `test`; BEGIN
master-bin.000001	#	Query	1	#	use `test`; insert into ti values (1)
master-bin.000001	#	Query	1	#	use `test`; insert into ti values (2)
master-bin.000001	#	Query	1	#	use `test`; insert into tt select * from ti
master-bin.000001	#	Query	1	#	use `test`; ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from ti /* that is what slave would miss - a bug */;
a
1
2
delete from ti;
delete from tt where a=1;
reset master;
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	98		
begin;
insert into ti values (1);
insert into ti values (2) /* to make the dup error in the following */;
insert into tt select * from ti /* one affected and error */;
ERROR 23000: Duplicate entry '2' for key 1
rollback;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	581		
show binlog events from 98;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	1	#	use `test`; BEGIN
master-bin.000001	#	Query	1	#	use `test`; insert into ti values (1)
master-bin.000001	#	Query	1	#	use `test`; insert into ti values (2) /* to make the dup error in the following */
master-bin.000001	#	Query	1	#	use `test`; insert into tt select * from ti /* one affected and error */
master-bin.000001	#	Query	1	#	use `test`; ROLLBACK
select count(*) from ti /* zero */;
count(*)
0
insert into ti select * from tt;
select * from tt /* that is what otherwise slave missed - the bug */;
a
1
2
drop table ti;
drop function if exists bug27417;
drop table if exists t1,t2;
CREATE TABLE t1 (a int NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int NOT NULL auto_increment, PRIMARY KEY (a));
363
create function bug27417(n int)
unknown's avatar
unknown committed
364 365 366 367 368 369 370 371 372 373 374 375 376
RETURNS int(11)
begin
insert into t1 values (null);
return n;
end|
reset master;
insert into t2 values (bug27417(1));
insert into t2 select bug27417(2);
reset master;
insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 1
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
377
master-bin.000001	196		
unknown's avatar
unknown committed
378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
/* only (!) with fixes for #23333 will show there is the query */;
select count(*) from t1 /* must be 3 */;
count(*)
3
reset master;
select count(*) from t2;
count(*)
2
delete from t2 where a=bug27417(3);
select count(*) from t2 /* nothing got deleted */;
count(*)
2
show master status;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	195		
/* the query must be in regardless of #23333 */;
select count(*) from t1 /* must be 5 */;
count(*)
5
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
affected rows: 0
select count(*) from t1 /* must be 7 */;
count(*)
7
drop table t1,t2;
403 404
CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
405 406 407
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
CREATE TABLE t5 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
408 409 410 411 412 413
insert into t2 values (1);
reset master;
insert into t2 values (bug27417(1));
ERROR 23000: Duplicate entry '1' for key 1
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
414
master-bin.000001	335		
415 416 417 418 419 420 421 422 423 424 425
select count(*) from t1 /* must be 1 */;
count(*)
1
delete from t1;
delete from t2;
insert into t2 values (2);
reset master;
insert into t2 select bug27417(1) union select bug27417(2);
ERROR 23000: Duplicate entry '2' for key 1
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
426
master-bin.000001	358		
427 428 429 430 431 432 433 434 435 436 437 438 439 440
select count(*) from t1 /* must be 2 */;
count(*)
2
delete from t1;
insert into t3 values (1,1),(2,3),(3,4);
reset master;
update t3 set b=b+bug27417(1);
ERROR 23000: Duplicate entry '4' for key 2
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	190		
select count(*) from t1 /* must be 2 */;
count(*)
2
441 442 443 444 445 446 447 448 449
delete from t3;
delete from t4;
insert into t3 values (1,1);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
ERROR 23000: Duplicate entry '2' for key 1
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
450
master-bin.000001	230		
451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
select count(*) from t1 /* must be 4 */;
count(*)
4
delete from t1;
delete from t3;
delete from t4;
insert into t3 values (1,1),(2,2);
insert into t4 values (1,1),(2,2);
reset master;
UPDATE t3,t4 SET t3.a=t4.a + bug27417(1);
ERROR 23000: Duplicate entry '2' for key 1
select count(*) from t1 /* must be 1 */;
count(*)
1
drop table t4;
466 467 468 469 470 471 472 473 474 475 476 477
delete from t1;
delete from t2;
delete from t3;
insert into t2 values (1);
insert into t3 values (1,1);
create trigger trg_del before delete on t2 for each row 
insert into t3 values (bug27417(1), 2);
reset master;
delete from t2;
ERROR 23000: Duplicate entry '1' for key 1
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
478
master-bin.000001	314		
479 480 481
select count(*) from t1 /* must be 1 */;
count(*)
1
482 483 484 485 486 487 488 489 490 491 492 493 494
drop trigger trg_del;
delete from t1;
delete from t2;
delete from t5;
create trigger trg_del_t2 after  delete on t2 for each row
insert into t1 values (1);
insert into t2 values (2),(3);
insert into t5 values (1),(2);
reset master;
delete t2.* from t2,t5 where t2.a=t5.a + 1;
ERROR 23000: Duplicate entry '1' for key 1
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
495
master-bin.000001	342		
496 497 498
select count(*) from t1 /* must be 1 */;
count(*)
1
499 500 501 502 503 504 505 506 507 508 509 510 511 512
delete from t1;
create table t4 (a int default 0, b int primary key) engine=innodb;
insert into t4 values (0, 17);
reset master;
load data infile '../std_data_ln/rpl_loaddata.dat' into table t4 (a, @b) set b= @b + bug27417(2);
ERROR 23000: Duplicate entry '17' for key 1
select * from t4;
a	b
0	17
select count(*) from t1 /* must be 2 */;
count(*)
2
show master status /* the offset must denote there is the query */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
513
master-bin.000001	444		
514 515
drop trigger trg_del_t2;
drop table t1,t2,t3,t4,t5;
516
drop function bug27417;
unknown's avatar
unknown committed
517
end of tests