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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
# 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.
--source include/master-slave.inc
#create a new connection to the slave
--connect (slave2, 127.0.0.1, root, ,test, $SLAVE_MYPORT,)
# 0) Prepare tables and data
--echo *** Prepare tables and data ***
connection master;
eval CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
eval CREATE TABLE t2 (a INT) ENGINE=$engine_type;
eval CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
sync_slave_with_master;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
SHOW CREATE TABLE t3;
SELECT @@GLOBAL.slave_transaction_retries;
SELECT @@GLOBAL.innodb_lock_wait_timeout;
--source include/stop_slave.inc
connection master;
# Save position of BEGIN
let $master_pos_begin= query_get_value(SHOW MASTER STATUS, Position, 1);
BEGIN;
INSERT INTO t1 VALUES (1);
# We make a long transaction here
INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
INSERT INTO t3 VALUES (3);
COMMIT;
save_master_pos;
--echo
# 1) Test deadlock
# Block slave SQL thread, wait retries of transaction, unlock slave before lock timeout
--echo *** Test deadlock ***
connection slave;
BEGIN;
SELECT * FROM t1 FOR UPDATE;
# Save variable 'Slave_retried_transactions' before deadlock
let $slave_retried_transactions= query_get_value(SHOW GLOBAL STATUS LIKE 'Slave_retried_transactions', Value, 1);
--connection slave2
--source include/start_slave.inc
--connection slave
# Wait until SQL thread blocked: variable 'Slave_retried_transactions' will incremented
let $status_var= Slave_retried_transactions;
let $status_var_value= $slave_retried_transactions;
let $status_type= GLOBAL;
let $status_var_comparsion= >;
--source include/wait_for_status_var.inc
SELECT COUNT(*) FROM t2;
COMMIT;
source include/check_slave_is_running.inc;
sync_with_master;
# Check the data
SELECT * FROM t1;
SELECT * FROM t3;
--echo
# 2) Test lock wait timeout
# Block slave and wait lock timeout error
--echo *** Test lock wait timeout ***
connection slave;
--source include/stop_slave.inc
DELETE FROM t2;
# Set slave position to the BEGIN log event
RESET MASTER;
--replace_result $master_pos_begin MASTER_POS_BEGIN
eval CHANGE MASTER TO MASTER_LOG_POS=$master_pos_begin;
BEGIN;
# Hold lock
SELECT * FROM t1 FOR UPDATE;
# Wait until slave stopped with error 'Lock wait timeout exceeded'
--connection slave2
START SLAVE;
--connection slave
let $slave_sql_errno= 1205;
--source include/wait_for_slave_sql_error.inc
SELECT COUNT(*) FROM t2;
COMMIT;
--source include/start_slave.inc
sync_with_master;
# Check data from tables
SELECT * FROM t1;
SELECT * FROM t3;
# Check that no error is reported
source include/check_slave_is_running.inc;
--echo
# 3) Test lock wait timeout and purged relay log
# Set max_relay_log_size=0, block slave and wait lock timeout error.
# Restart slave and check that no erros appear
--echo *** Test lock wait timeout and purged relay logs ***
connection slave;
SET @my_max_relay_log_size= @@global.max_relay_log_size;
SET global max_relay_log_size=0;
--source include/stop_slave.inc
DELETE FROM t2;
# Set slave position to the BEGIN log event
RESET MASTER;
--replace_result $master_pos_begin MASTER_POS_BEGIN
eval CHANGE MASTER TO MASTER_LOG_POS=$master_pos_begin;
BEGIN;
# Hold lock
SELECT * FROM t1 FOR UPDATE;
# Wait until slave stopped with error 'Lock wait timeout exceeded'
--connection slave2
START SLAVE;
--connection slave
let $slave_sql_errno= 1205;
--source include/wait_for_slave_sql_error.inc
SELECT COUNT(*) FROM t2;
COMMIT;
--source include/start_slave.inc
sync_with_master;
# Check data from tables
SELECT * FROM t1;
SELECT * FROM t3;
# Check that no error is reported
source include/check_slave_is_running.inc;
--echo
#
# bug#11748510/36524 incident of deadlock on slave is overdramatized
#
# Observe that the slave stopped when the number of transation retries
# exceeds @@global.slave_transaction_retries
#
connection master;
--echo *** Test the deadlock warning to be escalated into the error ***
delete from t1;
delete from t2;
delete from t3;
sync_slave_with_master;
# make sure slave's unilateral row gone as well
delete from t1;
delete from t2;
delete from t3;
# the first attempt to run a deadlock scenario of p 1) leads to the error
set @save.slave_transaction_retries= @@global.slave_transaction_retries;
set @@global.slave_transaction_retries= 0;
source include/stop_slave.inc;
connection master;
BEGIN;
INSERT INTO t1 VALUES (1);
# We make a long transaction here
INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
INSERT INTO t3 VALUES (3);
COMMIT;
connection slave;
BEGIN;
SELECT count(*) as zero FROM t1 FOR UPDATE;
--connection slave2
START SLAVE;
--connection slave
--echo *** Now the slave must be stopped due to timeout ***
let $slave_sql_errno= 1205; # ER_LOCK_TIMEOUT
let $show_slave_sql_error= 0;
source include/wait_for_slave_sql_error.inc;
rollback;
set @@global.slave_transaction_retries= @save.slave_transaction_retries;
source include/start_slave.inc;
# Clean up
--echo *** Clean up ***
connection master;
DROP TABLE t1,t2,t3;
sync_slave_with_master;
SET global max_relay_log_size= @my_max_relay_log_size;
--echo End of 5.1 tests
--source include/rpl_end.inc
|