mix_innodb_myisam_binlog.test 6.98 KB
Newer Older
unknown's avatar
unknown committed
1
# Check that binlog is ok when a transaction mixes updates to InnoDB and
unknown's avatar
unknown committed
2 3 4 5
# MyISAM.
# It would be nice to make this a replication test, but in 4.0 the
# slave is always with --skip-innodb in the testsuite. I (Guilhem) however
# did some tests manually on a slave; tables are replicated fine and
6
# Exec_Master_Log_Pos advances as expected.
unknown's avatar
unknown committed
7

8 9 10
# Embedded server doesn't support binlogging
-- source include/not_embedded.inc

unknown's avatar
unknown committed
11 12
-- source include/have_innodb.inc

unknown's avatar
unknown committed
13 14 15 16
--disable_warnings
drop table if exists t1, t2;
--enable_warnings

unknown's avatar
unknown committed
17 18 19 20
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
unknown's avatar
unknown committed
21 22
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
unknown's avatar
unknown committed
23 24 25 26

reset master;

begin;
unknown's avatar
unknown committed
27 28
insert into t1 values(1);
insert into t2 select * from t1;
unknown's avatar
unknown committed
29 30
commit;

31
--replace_column 5 #
32
--replace_result "xid=15" "xid=8"
unknown's avatar
unknown committed
33
show binlog events from 98;
unknown's avatar
unknown committed
34

unknown's avatar
unknown committed
35 36
delete from t1;
delete from t2;
unknown's avatar
unknown committed
37 38 39
reset master;

begin;
unknown's avatar
unknown committed
40 41 42
insert into t1 values(2);
insert into t2 select * from t1;
# should say some changes to non-transact1onal tables couldn't be rolled back
unknown's avatar
unknown committed
43 44
rollback;

45
--replace_column 5 #
unknown's avatar
unknown committed
46
show binlog events from 98;
unknown's avatar
unknown committed
47

unknown's avatar
unknown committed
48 49
delete from t1;
delete from t2;
unknown's avatar
unknown committed
50 51 52
reset master;

begin;
unknown's avatar
unknown committed
53
insert into t1 values(3);
unknown's avatar
unknown committed
54
savepoint my_savepoint;
unknown's avatar
unknown committed
55 56
insert into t1 values(4);
insert into t2 select * from t1;
unknown's avatar
unknown committed
57 58 59
rollback to savepoint my_savepoint;
commit;

60
--replace_column 5 #
61
--replace_result "xid=48" "xid=25"
unknown's avatar
unknown committed
62
show binlog events from 98;
unknown's avatar
unknown committed
63

unknown's avatar
unknown committed
64 65
delete from t1;
delete from t2;
unknown's avatar
unknown committed
66 67 68
reset master;

begin;
unknown's avatar
unknown committed
69
insert into t1 values(5);
unknown's avatar
unknown committed
70
savepoint my_savepoint;
unknown's avatar
unknown committed
71 72
insert into t1 values(6);
insert into t2 select * from t1;
unknown's avatar
unknown committed
73
rollback to savepoint my_savepoint;
unknown's avatar
unknown committed
74
insert into t1 values(7);
unknown's avatar
unknown committed
75
commit;
unknown's avatar
unknown committed
76
select a from t1 order by a; # check that savepoints work :)
unknown's avatar
unknown committed
77

78
--replace_column 5 #
79
--replace_result "xid=70" "xid=37"
unknown's avatar
unknown committed
80
show binlog events from 98;
unknown's avatar
unknown committed
81 82

# and when ROLLBACK is not explicit?
unknown's avatar
unknown committed
83 84
delete from t1;
delete from t2;
unknown's avatar
unknown committed
85 86 87 88
reset master;

select get_lock("a",10);
begin;
unknown's avatar
unknown committed
89 90
insert into t1 values(8);
insert into t2 select * from t1;
unknown's avatar
unknown committed
91 92 93 94 95 96 97 98 99
disconnect con1;

connection con2;
# We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
# guarantee that logging of the terminated con1 has been done yet (it may not
# even be started, so con1 may have not even attempted to lock the binlog yet;
# so SHOW BINLOG EVENTS may come before con1 does the loggin. To be sure that
# logging has been done, we use a user lock.
select get_lock("a",10);
100
--replace_column 5 #
unknown's avatar
unknown committed
101
show binlog events from 98;
unknown's avatar
unknown committed
102

unknown's avatar
unknown committed
103 104 105
# and when not in a transact1on?
delete from t1;
delete from t2;
unknown's avatar
unknown committed
106 107
reset master;

unknown's avatar
unknown committed
108 109
insert into t1 values(9);
insert into t2 select * from t1;
unknown's avatar
unknown committed
110

111
--replace_column 5 #
112
--replace_result "xid=119" "xid=60"
unknown's avatar
unknown committed
113
show binlog events from 98;
unknown's avatar
unknown committed
114

unknown's avatar
unknown committed
115
# Check that when the query updat1ng the MyISAM table is the first in the
unknown's avatar
unknown committed
116
# transaction, we log it immediately.
unknown's avatar
unknown committed
117 118
delete from t1;
delete from t2;
unknown's avatar
unknown committed
119 120
reset master;

unknown's avatar
unknown committed
121
insert into t1 values(10); # first make t1 non-empty
unknown's avatar
unknown committed
122
begin;
unknown's avatar
unknown committed
123
insert into t2 select * from t1;
124
--replace_column 5 #
125
--replace_result "xid=133" "xid=66"
unknown's avatar
unknown committed
126
show binlog events from 98;
unknown's avatar
unknown committed
127
insert into t1 values(11);
unknown's avatar
unknown committed
128 129
commit;

130
--replace_column 5 #
131
--replace_result "xid=133" "xid=66" "xid=136" "xid=68"
unknown's avatar
unknown committed
132
show binlog events from 98;
unknown's avatar
unknown committed
133 134


unknown's avatar
unknown committed
135 136
# Check that things work like before this BEGIN/ROLLBACK code was added,
# when t2 is INNODB 
unknown's avatar
unknown committed
137

unknown's avatar
unknown committed
138
alter table t2 engine=INNODB;
unknown's avatar
unknown committed
139

unknown's avatar
unknown committed
140 141
delete from t1;
delete from t2;
unknown's avatar
unknown committed
142 143 144
reset master;

begin;
unknown's avatar
unknown committed
145 146
insert into t1 values(12);
insert into t2 select * from t1;
unknown's avatar
unknown committed
147 148
commit;

149
--replace_column 5 #
150
--replace_result "xid=155" "xid=78"
unknown's avatar
unknown committed
151
show binlog events from 98;
unknown's avatar
unknown committed
152

unknown's avatar
unknown committed
153 154
delete from t1;
delete from t2;
unknown's avatar
unknown committed
155 156 157
reset master;

begin;
unknown's avatar
unknown committed
158 159
insert into t1 values(13);
insert into t2 select * from t1;
unknown's avatar
unknown committed
160 161
rollback;

162
--replace_column 5 #
unknown's avatar
unknown committed
163
show binlog events from 98;
unknown's avatar
unknown committed
164

unknown's avatar
unknown committed
165 166
delete from t1;
delete from t2;
unknown's avatar
unknown committed
167 168 169
reset master;

begin;
unknown's avatar
unknown committed
170
insert into t1 values(14);
unknown's avatar
unknown committed
171
savepoint my_savepoint;
unknown's avatar
unknown committed
172 173
insert into t1 values(15);
insert into t2 select * from t1;
unknown's avatar
unknown committed
174 175 176
rollback to savepoint my_savepoint;
commit;

177
--replace_column 5 #
178
--replace_result "xid=187" "xid=94"
unknown's avatar
unknown committed
179
show binlog events from 98;
unknown's avatar
unknown committed
180

unknown's avatar
unknown committed
181 182
delete from t1;
delete from t2;
unknown's avatar
unknown committed
183 184 185
reset master;

begin;
unknown's avatar
unknown committed
186
insert into t1 values(16);
unknown's avatar
unknown committed
187
savepoint my_savepoint;
unknown's avatar
unknown committed
188 189
insert into t1 values(17);
insert into t2 select * from t1;
unknown's avatar
unknown committed
190
rollback to savepoint my_savepoint;
unknown's avatar
unknown committed
191
insert into t1 values(18);
unknown's avatar
unknown committed
192
commit;
unknown's avatar
unknown committed
193
select a from t1 order by a; # check that savepoints work :)
unknown's avatar
unknown committed
194

195
--replace_column 5 #
196
--replace_result "xid=208" "xid=105"
unknown's avatar
unknown committed
197
show binlog events from 98;
unknown's avatar
unknown committed
198

199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
# Test for BUG#5714, where a MyISAM update in the transaction used to
# release row-level locks in InnoDB

connect (con3,localhost,root,,);

connection con3;
delete from t1;
delete from t2;
--disable_warnings
alter table t2 type=MyISAM;
--enable_warnings
insert into t1 values (1);
begin;
select * from t1 for update;

connection con2;
select (@before:=unix_timestamp())*0; # always give repeatable output
begin;
send select * from t1 for update;

connection con3;
insert into t2 values (20);

connection con2;
--error 1205
reap;
select (@after:=unix_timestamp())*0; # always give repeatable output
# verify that innodb_lock_wait_timeout was exceeded. When there was
# the bug, the reap would return immediately after the insert into t2.
select (@after-@before) >= 2;

unknown's avatar
unknown committed
230
drop table t1,t2;
231 232 233 234 235 236 237 238 239 240 241
commit;

# test for BUG#7947 - DO RELEASE_LOCK() not written to binlog on rollback in the middle
# of a transaction

connection con2;
begin;
create temporary table ti (a int) engine=innodb;
rollback;
insert into ti values(1);
set autocommit=0;
242 243 244 245
create temporary table t1 (a int) engine=myisam; 
commit;
insert t1 values (1); 
rollback;
246 247 248 249 250 251 252 253 254
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); 
disconnect con2;
connection con3;
255
select get_lock("lock1",60);
256 257 258
--replace_column 5 #
--replace_result "xid=208" "xid=105" "xid=227" "xid=114" "xid=230" "xid=115" "xid=234" "xid=117" "xid=261" "xid=132"
show binlog events from 98;
259 260 261
do release_lock("lock1");
drop table t0,t2;

262
# End of 4.1 tests
263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285

# Test for BUG#16559 (ROLLBACK should always have a zero error code in
# binlog). Has to be here and not earlier, as the SELECTs influence
# XIDs differently between normal and ps-protocol (and SHOW BINLOG
# EVENTS above read XIDs).

connect (con4,localhost,root,,);
connection con3;
reset master;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
select get_lock("a",10);
begin;
insert into t1 values(8);
insert into t2 select * from t1;
disconnect con3;

connection con4;
select get_lock("a",10); # wait for rollback to finish

# we check that the error code of the "ROLLBACK" event is 0 and not
# ER_SERVER_SHUTDOWN (i.e. disconnection just rolls back transaction
# and does not make slave to stop)
286 287
--exec $MYSQL_BINLOG --start-position=547 $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
288
eval select
289
(@a:=load_file("$MYSQLTEST_VARDIR/tmp/mix_innodb_myisam_binlog.output"))
290 291 292 293 294
is not null;
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
eval select
@a like "%#%error_code=0%ROLLBACK;%ROLLBACK /* added by mysqlbinlog */;%",
@a not like "%#%error_code=%error_code=%";
295
drop table t1, t2;