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
|
##############################################################################
# ==== Purpose ====
# The purpose of this test is to verify that during row lookup, applier thread
# retries the transaction when storage engine reports retryable errors
# (like 'HA_ERR_LOCK_WAIT_TIMEOUT' or 'HA_ERR_LOCK_DEADLOCK') instead of error
# out and stop execution. This test checks for all three(HASH_SCAN, INDEX_SCAN
# and TABLE_SCAN) row lookup algorithms.
#
# ==== Requirement ====
# When storage engine reports retryable errors(like 'HA_ERR_LOCK_WAIT_TIMEOUT'
# or 'HA_ERR_LOCK_DEADLOCK') back to applier thread, it should be retried as
# per # 'replica_transaction_retries'.
#
# ==== Implementation ====
# 1. Create source-replica topology
# 2. For each slave_rows_search_algorithms[INDEX_SCAN, HASH_SCAN(with index),
# HASH_SCAN(with two non-unique index), HASH_SCAN(with out index),
# TABLE_SCAN],
# 2.0 Configure source and replica
# 2.1 Stop replica
# 2.2 Execute DELETE transaction on source
# 2.3 Begin SELECT ... FOR UPDATE on replica
# 2:4 Start replica from another connection
# 2.5 Wait until the applier retries
# 2.6 Commit SELECT ... FOR UPDATE on replica
# 2.7 Restart replica applier
# 3. Clean up
#
# ==== References ====
# BUG#33107663:LOCKING READS ON THE REPLICA: CAN'T FIND RECORD IN 'T',
# ERROR_CODE: 1032; HANDLER ERROR HA_ERR_KEY_NOT_FOUND
###############################################################################
# The status variable Replica_rows_last_search_algorithm_used is defined
# only in Debug mode
--source include/have_debug.inc
--source include/have_binlog_format_row.inc
--let $option_name = replica_transaction_retries
--let $option_operator = >
--let $option_value = 2
--source include/only_with_option.inc
--echo #
--echo # 1. Create source-replica topology
--source include/master-slave.inc
--source include/rpl_connection_slave.inc
--let $saved_innodb_lock_wait_timeout = `SELECT @@GLOBAL.innodb_lock_wait_timeout`
--let $saved_slave_rows_search_algorithms = `SELECT @@GLOBAL.slave_rows_search_algorithms`
SET GLOBAL innodb_lock_wait_timeout = 2;
--disable_warnings
SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';
--enable_warnings
CALL mtr.add_suppression("Replica SQL for channel.*Consider raising the value of the replica_transaction_retries variable.*");
CALL mtr.add_suppression("Replica SQL for channel.*The replica coordinator and worker threads are stopped.*");
--echo # 2. For each slave_rows_search_algorithms[INDEX_SCAN, HASH_SCAN(with
--echo # index), HASH_SCAN(with two non-unique index), HASH_SCAN(with out
--echo # index), TABLE_SCAN]
#0. INDEX_SCAN
#1. HASH_SCAN(with index)
#2. HASH_SCAN(with two non-unique index)
#3. HASH_SCAN(with out index)
#4. TABLE_SCAN
--let $i = 0
while ($i < 5) {
--echo #
--echo # 2.0 Configure source and replica
--source include/rpl_connection_master.inc
if ($i == 0){
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=innodb;
}
if ($i == 1){
CREATE TABLE t1 (a INT, b INT, key(a)) ENGINE=innodb;
}
if ($i == 2){
CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=innodb;
INSERT INTO t1 VALUES (10, 10), (10, 20), (10, 30), (20, 10), (20, 20);
}
if ($i > 2){
CREATE TABLE t1 (a INT, b INT) ENGINE=innodb;
}
if ($i != 2){
INSERT INTO t1 VALUES (10, 10), (20, 20);
}
--source include/sync_slave_sql_with_master.inc
--echo #
--echo # 2.1 Stop replica
--source include/stop_slave.inc
--echo #
--echo # 2.2 Execute DELETE transaction on source
--source include/rpl_connection_master.inc
BEGIN;
DELETE FROM t1;
COMMIT;
--echo #
--echo # 2.3 Begin SELECT ... FOR UPDATE on replica
--source include/rpl_connection_slave.inc
if ($i == 4)
{
--disable_warnings
SET GLOBAL slave_rows_search_algorithms = 'TABLE_SCAN';
--enable_warnings
}
BEGIN;
SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
# Save value of 'count_transactions_retries' before lock_wait_timeout
let $count_transactions_retries= query_get_value(
"select count_transactions_retries from
performance_schema.replication_applier_status",count_transactions_retries, 1);
--echo #
--echo # 2.4 Start replica from another connection
--source include/rpl_connection_slave1.inc
--source include/start_slave.inc
--echo #
--echo # 2.5 Wait until the applier retries:'count_transactions_retries' will be
--echo # incremented
--source include/rpl_connection_slave.inc
let $status_col= count_transactions_retries;
let $status_col_value= $count_transactions_retries;
let $table=replication_applier_status;
let $status_col_comparsion= >;
--source include/wait_for_rpl_pfs_status.inc
--echo #
--echo # 2.6 Commit SELECT ... FOR UPDATE on replica
COMMIT;
SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
--echo #
--echo # 2.7 Restart replica applier
# This is required to avoid race condition. If the mysqltest process executes
# very slowly, the applier may retry multiple times, and eventually reach the
# maximum number of retries and will error out(#1205 ER_LOCK_WAIT_TIMEOUT) and
# stop.
--disable_warnings
--let $slave_sql_errno= 0,1205
--source include/stop_slave_sql.inc
--enable_warnings
--source include/start_slave_sql.inc
# Drop table for the next test
--source include/rpl_connection_master.inc
DROP TABLE t1;
--source include/sync_slave_sql_with_master.inc
--inc $i
}
--echo #
--echo # 3. Clean up
--source include/rpl_connection_slave.inc
--replace_result $saved_innodb_lock_wait_timeout INNODB_LOCK_WAIT_TIMEOUT
eval SET GLOBAL innodb_lock_wait_timeout = $saved_innodb_lock_wait_timeout;
--replace_result $saved_slave_rows_search_algorithms SLAVE_ROWS_SEARCH_ALGORITHMS
--disable_warnings
eval SET GLOBAL slave_rows_search_algorithms = "$saved_slave_rows_search_algorithms";
--enable_warnings
--source include/rpl_end.inc
|
