1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
# See if slave restarts the transaction after failing on an InnoDB deadlock error.
# Note: testing what happens when too many retries is possible, but
# needs large waits when running with --debug, so we don't do it.
# The same way, this test may not test what is expected when run
# under Valgrind, timings are too short then (with --valgrind I
# (Guilhem) have seen the test manage to provoke lock wait timeout
# error but not deadlock error; that is ok as code deals with the two
# errors in exactly the same way.
# We don't 'show status like 'slave_retried_transactions'' because this
# is not repeatable (depends on sleeps).
-- source include/master-slave.inc
connection master;
eval CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
eval CREATE TABLE t2 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
# requiring 'unique' for the timeout part of the test
eval CREATE TABLE t3 (a INT UNIQUE) ENGINE=$engine_type;
eval CREATE TABLE t4 (a INT) ENGINE=$engine_type;
show variables like 'slave_transaction_retries';
sync_slave_with_master;
show create table t1;
show create table t2;
show variables like 'slave_transaction_retries';
stop slave;
# 1) Test deadlock
connection master;
begin;
# Let's keep BEGIN and the locked statement in two different relay logs.
insert into t2 values (0); # t2,t1 actors of deadlock in repl-ed ta
#insert into t3 select * from t2 for update;
let $1=10;
disable_query_log;
while ($1)
{
eval insert into t3 values( $1 );
dec $1;
}
enable_query_log;
insert into t1 values(1);
commit;
save_master_pos;
connection slave;
begin;
# Let's make our transaction large so that it's repl-ed msta that's victim
let $1=100;
disable_query_log;
while ($1)
{
eval insert into t4 values( $1 );
dec $1;
}
enable_query_log;
select * from t1 for update; # t1,t2 on local slave's
start slave;
# bad option, todo: replicate a non-transactional t_sync with the transaction
# and use wait_until_rows_count macro below
--real_sleep 3 # hope that slave is blocked now
#let $count=11;
#let $table=t_sync;
#--include wait_until_rows_count.inc
select * from t2 for update /* dl */; # provoke deadlock, repl-ed should be victim
commit;
sync_with_master;
select * from t1; # check that repl-ed succeeded finally
select * from t2 /* must be 1 */;
# check that no error is reported
--replace_column 1 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 #
--replace_result $MASTER_MYPORT MASTER_MYPORT
--vertical_results
show slave status;
--horizontal_results
# 2) Test lock wait timeout
stop slave;
delete from t3;
change master to master_log_pos=548; # the BEGIN log event
begin;
select * from t2 for update; # hold lock
start slave;
--real_sleep 10 # repl-ed should have blocked, and be retrying
select count(*) from t3 /* must be zero */; # replaying begins after rollback
commit;
sync_with_master;
select * from t1; # check that repl-ed succeeded finally
select * from t2;
# check that no error is reported
--replace_column 1 # 7 # 8 # 9 # 11 # 16 # 22 # 23 # 33 #
--replace_result $MASTER_MYPORT MASTER_MYPORT
--vertical_results
show slave status;
--horizontal_results
# Now we repeat 2), but with BEGIN in the same relay log as
# COMMIT (to see if seeking into hot log is ok).
set @my_max_relay_log_size= @@global.max_relay_log_size;
set global max_relay_log_size=0;
# This is really copy-paste of 2) of above
stop slave;
delete from t3;
change master to master_log_pos=548;
begin;
select * from t2 for update;
start slave;
--real_sleep 10
select count(*) from t3 /* must be zero */; # replaying begins after rollback
commit;
sync_with_master;
select * from t1;
select * from t2;
--replace_column 1 # 7 # 8 # 9 # 11 # 16 # 22 # 23 # 33 # 35 # 36 #
--replace_result $MASTER_MYPORT MASTER_MYPORT
--vertical_results
show slave status;
--horizontal_results
connection master;
drop table t1,t2,t3,t4;
sync_slave_with_master;
set global max_relay_log_size= @my_max_relay_log_size;
--echo End of 5.1 tests
|
