mix_innodb_myisam_binlog.result 15 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 98;
unknown's avatar
unknown committed
10
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
11
master-bin.000001	98	Query	1	#	use `test`; BEGIN
12 13
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(1)
master-bin.000001	253	Query	1	#	use `test`; insert into t2 select * from t1
14
master-bin.000001	347	Xid	1	#	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 98;
unknown's avatar
unknown committed
25
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
26
master-bin.000001	98	Query	1	#	use `test`; BEGIN
27 28 29
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(2)
master-bin.000001	253	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	347	Query	1	#	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 98;
unknown's avatar
unknown committed
43
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
44
master-bin.000001	98	Query	1	#	use `test`; BEGIN
45 46 47 48 49
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(3)
master-bin.000001	253	Query	1	#	use `test`; savepoint my_savepoint
master-bin.000001	338	Query	1	#	use `test`; insert into t1 values(4)
master-bin.000001	425	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	519	Query	1	#	use `test`; rollback to savepoint my_savepoint
50
master-bin.000001	616	Xid	1	#	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 98;
unknown's avatar
unknown committed
69
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
70
master-bin.000001	98	Query	1	#	use `test`; BEGIN
71 72 73 74 75 76
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(5)
master-bin.000001	253	Query	1	#	use `test`; savepoint my_savepoint
master-bin.000001	338	Query	1	#	use `test`; insert into t1 values(6)
master-bin.000001	425	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	519	Query	1	#	use `test`; rollback to savepoint my_savepoint
master-bin.000001	616	Query	1	#	use `test`; insert into t1 values(7)
77
master-bin.000001	703	Xid	1	#	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
90 91 92 93 94 95
show binlog events from 98;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	98	Query	1	#	use `test`; BEGIN
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(8)
master-bin.000001	253	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	347	Query	1	#	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 98;
unknown's avatar
unknown committed
102
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
103
master-bin.000001	98	Query	1	#	use `test`; insert into t1 values(9)
104
master-bin.000001	185	Xid	1	#	COMMIT /* XID */
105
master-bin.000001	212	Query	1	#	use `test`; insert into t2 select * from t1
unknown's avatar
unknown committed
106 107
delete from t1;
delete from t2;
unknown's avatar
unknown committed
108
reset master;
unknown's avatar
unknown committed
109
insert into t1 values(10);
unknown's avatar
unknown committed
110
begin;
unknown's avatar
unknown committed
111
insert into t2 select * from t1;
unknown's avatar
unknown committed
112
show binlog events from 98;
unknown's avatar
unknown committed
113
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
114
master-bin.000001	98	Query	1	#	use `test`; insert into t1 values(10)
115
master-bin.000001	186	Xid	1	#	COMMIT /* XID */
116
master-bin.000001	213	Query	1	#	use `test`; insert into t2 select * from t1
unknown's avatar
unknown committed
117
insert into t1 values(11);
unknown's avatar
unknown committed
118
commit;
unknown's avatar
unknown committed
119
show binlog events from 98;
unknown's avatar
unknown committed
120
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
121
master-bin.000001	98	Query	1	#	use `test`; insert into t1 values(10)
122
master-bin.000001	186	Xid	1	#	COMMIT /* XID */
123 124 125
master-bin.000001	213	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	307	Query	1	#	use `test`; BEGIN
master-bin.000001	375	Query	1	#	use `test`; insert into t1 values(11)
126
master-bin.000001	463	Xid	1	#	COMMIT /* XID */
unknown's avatar
unknown committed
127
alter table t2 engine=INNODB;
unknown's avatar
unknown committed
128 129
delete from t1;
delete from t2;
unknown's avatar
unknown committed
130 131
reset master;
begin;
unknown's avatar
unknown committed
132 133
insert into t1 values(12);
insert into t2 select * from t1;
unknown's avatar
unknown committed
134
commit;
unknown's avatar
unknown committed
135
show binlog events from 98;
unknown's avatar
unknown committed
136
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
137
master-bin.000001	98	Query	1	#	use `test`; BEGIN
138 139
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(12)
master-bin.000001	254	Query	1	#	use `test`; insert into t2 select * from t1
140
master-bin.000001	348	Xid	1	#	COMMIT /* XID */
unknown's avatar
unknown committed
141 142
delete from t1;
delete from t2;
unknown's avatar
unknown committed
143 144
reset master;
begin;
unknown's avatar
unknown committed
145 146
insert into t1 values(13);
insert into t2 select * from t1;
unknown's avatar
unknown committed
147
rollback;
unknown's avatar
unknown committed
148
show binlog events from 98;
unknown's avatar
unknown committed
149
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
150 151
delete from t1;
delete from t2;
unknown's avatar
unknown committed
152 153
reset master;
begin;
unknown's avatar
unknown committed
154
insert into t1 values(14);
unknown's avatar
unknown committed
155
savepoint my_savepoint;
unknown's avatar
unknown committed
156 157
insert into t1 values(15);
insert into t2 select * from t1;
unknown's avatar
unknown committed
158 159
rollback to savepoint my_savepoint;
commit;
unknown's avatar
unknown committed
160
show binlog events from 98;
unknown's avatar
unknown committed
161
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
162
master-bin.000001	98	Query	1	#	use `test`; BEGIN
163
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(14)
164
master-bin.000001	254	Xid	1	#	COMMIT /* XID */
unknown's avatar
unknown committed
165 166
delete from t1;
delete from t2;
unknown's avatar
unknown committed
167 168
reset master;
begin;
unknown's avatar
unknown committed
169
insert into t1 values(16);
unknown's avatar
unknown committed
170
savepoint my_savepoint;
unknown's avatar
unknown committed
171 172
insert into t1 values(17);
insert into t2 select * from t1;
unknown's avatar
unknown committed
173
rollback to savepoint my_savepoint;
unknown's avatar
unknown committed
174
insert into t1 values(18);
unknown's avatar
unknown committed
175
commit;
unknown's avatar
unknown committed
176
select a from t1 order by a;
unknown's avatar
unknown committed
177 178 179
a
16
18
unknown's avatar
unknown committed
180
show binlog events from 98;
unknown's avatar
unknown committed
181
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
unknown's avatar
unknown committed
182
master-bin.000001	98	Query	1	#	use `test`; BEGIN
183 184
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(16)
master-bin.000001	254	Query	1	#	use `test`; insert into t1 values(18)
185
master-bin.000001	342	Xid	1	#	COMMIT /* XID */
186 187 188 189 190 191 192 193 194 195 196 197
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
198
select * from t1 for update;
199
insert into t2 values (20);
unknown's avatar
unknown committed
200
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
201 202 203 204 205 206
select (@after:=unix_timestamp())*0;
(@after:=unix_timestamp())*0
0
select (@after-@before) >= 2;
(@after-@before) >= 2
1
unknown's avatar
unknown committed
207
drop table t1,t2;
208 209 210 211 212 213 214 215 216 217 218 219
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;
220 221
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
222 223 224 225 226 227 228
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);
229 230 231
select get_lock("lock1",60);
get_lock("lock1",60)
1
232 233 234 235 236
show binlog events from 98;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	98	Query	1	#	use `test`; BEGIN
master-bin.000001	166	Query	1	#	use `test`; insert into t1 values(16)
master-bin.000001	254	Query	1	#	use `test`; insert into t1 values(18)
237
master-bin.000001	342	Xid	1	#	COMMIT /* XID */
238
master-bin.000001	369	Query	1	#	use `test`; delete from t1
239
master-bin.000001	446	Xid	1	#	COMMIT /* XID */
240
master-bin.000001	473	Query	1	#	use `test`; delete from t2
241
master-bin.000001	550	Xid	1	#	COMMIT /* XID */
242 243
master-bin.000001	577	Query	1	#	use `test`; alter table t2 type=MyISAM
master-bin.000001	666	Query	1	#	use `test`; insert into t1 values (1)
244
master-bin.000001	754	Xid	1	#	COMMIT /* XID */
245 246 247 248
master-bin.000001	781	Query	1	#	use `test`; insert into t2 values (20)
master-bin.000001	870	Query	1	#	use `test`; drop table t1,t2
master-bin.000001	949	Query	1	#	use `test`; create temporary table ti (a int) engine=innodb
master-bin.000001	1059	Query	1	#	use `test`; insert into ti values(1)
249
master-bin.000001	1146	Xid	1	#	COMMIT /* XID */
250 251 252 253 254 255 256
master-bin.000001	1173	Query	1	#	use `test`; create temporary table t1 (a int) engine=myisam
master-bin.000001	1283	Query	1	#	use `test`; insert t1 values (1)
master-bin.000001	1366	Query	1	#	use `test`; create table t0 (n int)
master-bin.000001	1452	Query	1	#	use `test`; insert t0 select * from t1
master-bin.000001	1541	Query	1	#	use `test`; insert into t0 select GET_LOCK("lock1",null)
master-bin.000001	1648	Query	1	#	use `test`; create table t2 (n int) engine=innodb
master-bin.000001	1748	Query	1	#	use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`t1`,`test`.`ti`
257 258
do release_lock("lock1");
drop table t0,t2;
259 260 261 262 263 264 265 266 267 268 269 270 271
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
272
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
273
is not null;
274
(@a:=load_file("MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
275 276 277
is not null
1
select
278
@a like "%#%error_code=0%ROLLBACK/*!*/;%ROLLBACK /* added by mysqlbinlog */;%",
279
@a not like "%#%error_code=%error_code=%";
280
@a like "%#%error_code=0%ROLLBACK/*!*/;%ROLLBACK /* added by mysqlbinlog */;%"	@a not like "%#%error_code=%error_code=%"
281
1	1
282
drop table t1, t2;
unknown's avatar
unknown committed
283 284 285 286 287 288 289 290 291 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 363 364 365 366 367
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));
create function bug27417(n int) 
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
368
master-bin.000001	196		
unknown's avatar
unknown committed
369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393
/* 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;
394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461
CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM;
CREATE TABLE t2 (a int, PRIMARY KEY (a)) ENGINE=InnoDB;
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique);
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
master-bin.000001	267		
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
master-bin.000001	290		
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
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
master-bin.000001	246		
select count(*) from t1 /* must be 1 */;
count(*)
1
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
master-bin.000001	376		
drop trigger trg_del;
unknown's avatar
unknown committed
462
drop table t1,t2,t3,t4;
463
drop function bug27417;
unknown's avatar
unknown committed
464
end of tests