mix_innodb_myisam_binlog.result 6.5 KB
Newer Older
1
drop table if exists t1, t2;
2 3
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
4 5
reset master;
begin;
6 7
insert into t1 values(1);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
8
commit;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
9 10
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
11
master-bin.000001	95	Query	1	#	use `test`; BEGIN
12 13 14
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(1)
master-bin.000001	252	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	347	Query	1	#	use `test`; COMMIT
15 16
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
17 18
reset master;
begin;
19 20
insert into t1 values(2);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
21
rollback;
22 23
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
24 25
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
26
master-bin.000001	95	Query	1	#	use `test`; BEGIN
27 28 29
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(2)
master-bin.000001	252	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	347	Query	1	#	use `test`; ROLLBACK
30 31
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
32 33
reset master;
begin;
34
insert into t1 values(3);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
35
savepoint my_savepoint;
36 37
insert into t1 values(4);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
38
rollback to savepoint my_savepoint;
39 40
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
guilhem@mysql.com's avatar
guilhem@mysql.com committed
41
commit;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
42 43
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
44
master-bin.000001	95	Query	1	#	use `test`; BEGIN
45 46 47 48 49 50
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(3)
master-bin.000001	252	Query	1	#	use `test`; savepoint my_savepoint
master-bin.000001	338	Query	1	#	use `test`; insert into t1 values(4)
master-bin.000001	426	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	521	Query	1	#	use `test`; rollback to savepoint my_savepoint
master-bin.000001	619	Query	1	#	use `test`; COMMIT
51 52
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
53 54
reset master;
begin;
55
insert into t1 values(5);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
56
savepoint my_savepoint;
57 58
insert into t1 values(6);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
59
rollback to savepoint my_savepoint;
60 61
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
62
insert into t1 values(7);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
63
commit;
64
select a from t1 order by a;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
65 66 67
a
5
7
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
68 69
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
70
master-bin.000001	95	Query	1	#	use `test`; BEGIN
71 72 73 74 75 76 77
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(5)
master-bin.000001	252	Query	1	#	use `test`; savepoint my_savepoint
master-bin.000001	338	Query	1	#	use `test`; insert into t1 values(6)
master-bin.000001	426	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	521	Query	1	#	use `test`; rollback to savepoint my_savepoint
master-bin.000001	619	Query	1	#	use `test`; insert into t1 values(7)
master-bin.000001	707	Query	1	#	use `test`; COMMIT
78 79
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
80 81 82 83 84
reset master;
select get_lock("a",10);
get_lock("a",10)
1
begin;
85 86
insert into t1 values(8);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
87 88 89
select get_lock("a",10);
get_lock("a",10)
1
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
90 91
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
92
master-bin.000001	95	Query	1	#	use `test`; BEGIN
93 94 95
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(8)
master-bin.000001	252	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	347	Query	1	#	use `test`; ROLLBACK
96 97
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
98
reset master;
99 100
insert into t1 values(9);
insert into t2 select * from t1;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
101 102
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
103
master-bin.000001	95	Query	1	#	use `test`; insert into t1 values(9)
104
master-bin.000001	183	Query	1	#	use `test`; insert into t2 select * from t1
105 106
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
107
reset master;
108
insert into t1 values(10);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
109
begin;
110
insert into t2 select * from t1;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
111 112
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
113
master-bin.000001	95	Query	1	#	use `test`; insert into t1 values(10)
114
master-bin.000001	184	Query	1	#	use `test`; insert into t2 select * from t1
115
insert into t1 values(11);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
116
commit;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
117 118
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
119
master-bin.000001	95	Query	1	#	use `test`; insert into t1 values(10)
120 121 122 123
master-bin.000001	184	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	279	Query	1	#	use `test`; BEGIN
master-bin.000001	348	Query	1	#	use `test`; insert into t1 values(11)
master-bin.000001	437	Query	1	#	use `test`; COMMIT
124
alter table t2 engine=INNODB;
125 126
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
127 128
reset master;
begin;
129 130
insert into t1 values(12);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
131
commit;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
132 133
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
134
master-bin.000001	95	Query	1	#	use `test`; BEGIN
135 136 137
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(12)
master-bin.000001	253	Query	1	#	use `test`; insert into t2 select * from t1
master-bin.000001	348	Query	1	#	use `test`; COMMIT
138 139
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
140 141
reset master;
begin;
142 143
insert into t1 values(13);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
144
rollback;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
145 146
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
147 148
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
149 150
reset master;
begin;
151
insert into t1 values(14);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
152
savepoint my_savepoint;
153 154
insert into t1 values(15);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
155 156
rollback to savepoint my_savepoint;
commit;
guilhem@gbichot2's avatar
guilhem@gbichot2 committed
157 158
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
159
master-bin.000001	95	Query	1	#	use `test`; BEGIN
160 161
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(14)
master-bin.000001	253	Query	1	#	use `test`; COMMIT
162 163
delete from t1;
delete from t2;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
164 165
reset master;
begin;
166
insert into t1 values(16);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
167
savepoint my_savepoint;
168 169
insert into t1 values(17);
insert into t2 select * from t1;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
170
rollback to savepoint my_savepoint;
171
insert into t1 values(18);
guilhem@mysql.com's avatar
guilhem@mysql.com committed
172
commit;
173
select a from t1 order by a;
guilhem@mysql.com's avatar
guilhem@mysql.com committed
174 175 176
a
16
18
177 178 179
show binlog events from 95;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	95	Query	1	#	use `test`; BEGIN
180 181 182
master-bin.000001	164	Query	1	#	use `test`; insert into t1 values(16)
master-bin.000001	253	Query	1	#	use `test`; insert into t1 values(18)
master-bin.000001	342	Query	1	#	use `test`; COMMIT
183 184 185 186 187 188 189 190 191 192 193 194 195 196
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;
 select * from t1 for update;
insert into t2 values (20);
monty@mysql.com's avatar
monty@mysql.com committed
197
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
198 199 200 201 202 203
select (@after:=unix_timestamp())*0;
(@after:=unix_timestamp())*0
0
select (@after-@before) >= 2;
(@after-@before) >= 2
1
204
drop table t1,t2;