Debian Codegraph @

mysql-test/suite/rpl/t/rpl_replica_read_lock.test

raw
1 ##############################################################################
2 # ==== Purpose ====
3 # The purpose of this test is to verify that during row lookup, applier thread
4 # retries the transaction when storage engine reports retryable errors
5 # (like 'HA_ERR_LOCK_WAIT_TIMEOUT' or 'HA_ERR_LOCK_DEADLOCK') instead of error
6 # out and stop execution. This test checks for all three(HASH_SCAN, INDEX_SCAN
7 # and TABLE_SCAN) row lookup algorithms.
8 #
9 # ==== Requirement ====
10 # When storage engine reports retryable errors(like 'HA_ERR_LOCK_WAIT_TIMEOUT'
11 # or 'HA_ERR_LOCK_DEADLOCK') back to applier thread, it should be retried as
12 # per # 'replica_transaction_retries'.
13 #
14 # ==== Implementation ====
15 # 1. Create source-replica topology
16 # 2. For each slave_rows_search_algorithms[INDEX_SCAN, HASH_SCAN(with index),
17 # HASH_SCAN(with two non-unique index), HASH_SCAN(with out index),
18 # TABLE_SCAN],
19 # 2.0 Configure source and replica
20 # 2.1 Stop replica
21 # 2.2 Execute DELETE transaction on source
22 # 2.3 Begin SELECT ... FOR UPDATE on replica
23 # 2:4 Start replica from another connection
24 # 2.5 Wait until the applier retries
25 # 2.6 Commit SELECT ... FOR UPDATE on replica
26 # 2.7 Restart replica applier
27 # 3. Clean up
28 #
29 # ==== References ====
30 # BUG#33107663:LOCKING READS ON THE REPLICA: CAN'T FIND RECORD IN 'T',
31 # ERROR_CODE: 1032; HANDLER ERROR HA_ERR_KEY_NOT_FOUND
32 ###############################################################################
33
34 # The status variable Replica_rows_last_search_algorithm_used is defined
35 # only in Debug mode
36 --source include/have_debug.inc
37 --source include/have_binlog_format_row.inc
38 --let $option_name = replica_transaction_retries
39 --let $option_operator = >
40 --let $option_value = 2
41 --source include/only_with_option.inc
42
43 --echo #
44 --echo # 1. Create source-replica topology
45 --source include/master-slave.inc
46
47 --source include/rpl_connection_slave.inc
48 --let $saved_innodb_lock_wait_timeout = `SELECT @@GLOBAL.innodb_lock_wait_timeout`
49 --let $saved_slave_rows_search_algorithms = `SELECT @@GLOBAL.slave_rows_search_algorithms`
50 SET GLOBAL innodb_lock_wait_timeout = 2;
51 --disable_warnings
52 SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';
53 --enable_warnings
54 CALL mtr.add_suppression("Replica SQL for channel.*Consider raising the value of the replica_transaction_retries variable.*");
55 CALL mtr.add_suppression("Replica SQL for channel.*The replica coordinator and worker threads are stopped.*");
56
57 --echo # 2. For each slave_rows_search_algorithms[INDEX_SCAN, HASH_SCAN(with
58 --echo # index), HASH_SCAN(with two non-unique index), HASH_SCAN(with out
59 --echo # index), TABLE_SCAN]
60
61 #0. INDEX_SCAN
62 #1. HASH_SCAN(with index)
63 #2. HASH_SCAN(with two non-unique index)
64 #3. HASH_SCAN(with out index)
65 #4. TABLE_SCAN
66 --let $i = 0
67 while ($i < 5) {
68
69 --echo #
70 --echo # 2.0 Configure source and replica
71 --source include/rpl_connection_master.inc
72 if ($i == 0){
73 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=innodb;
74 }
75 if ($i == 1){
76 CREATE TABLE t1 (a INT, b INT, key(a)) ENGINE=innodb;
77 }
78 if ($i == 2){
79 CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=innodb;
80 INSERT INTO t1 VALUES (10, 10), (10, 20), (10, 30), (20, 10), (20, 20);
81 }
82 if ($i > 2){
83 CREATE TABLE t1 (a INT, b INT) ENGINE=innodb;
84 }
85 if ($i != 2){
86 INSERT INTO t1 VALUES (10, 10), (20, 20);
87 }
88 --source include/sync_slave_sql_with_master.inc
89
90 --echo #
91 --echo # 2.1 Stop replica
92 --source include/stop_slave.inc
93
94 --echo #
95 --echo # 2.2 Execute DELETE transaction on source
96 --source include/rpl_connection_master.inc
97 BEGIN;
98 DELETE FROM t1;
99 COMMIT;
100
101 --echo #
102 --echo # 2.3 Begin SELECT ... FOR UPDATE on replica
103 --source include/rpl_connection_slave.inc
104 if ($i == 4)
105 {
106 --disable_warnings
107 SET GLOBAL slave_rows_search_algorithms = 'TABLE_SCAN';
108 --enable_warnings
109 }
110
111 BEGIN;
112 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
113 # Save value of 'count_transactions_retries' before lock_wait_timeout
114 let $count_transactions_retries= query_get_value(
115 "select count_transactions_retries from
116 performance_schema.replication_applier_status",count_transactions_retries, 1);
117
118 --echo #
119 --echo # 2.4 Start replica from another connection
120 --source include/rpl_connection_slave1.inc
121 --source include/start_slave.inc
122 --echo #
123 --echo # 2.5 Wait until the applier retries:'count_transactions_retries' will be
124 --echo # incremented
125 --source include/rpl_connection_slave.inc
126 let $status_col= count_transactions_retries;
127 let $status_col_value= $count_transactions_retries;
128 let $table=replication_applier_status;
129 let $status_col_comparsion= >;
130 --source include/wait_for_rpl_pfs_status.inc
131 --echo #
132 --echo # 2.6 Commit SELECT ... FOR UPDATE on replica
133 COMMIT;
134
135 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
136
137 --echo #
138 --echo # 2.7 Restart replica applier
139 # This is required to avoid race condition. If the mysqltest process executes
140 # very slowly, the applier may retry multiple times, and eventually reach the
141 # maximum number of retries and will error out(#1205 ER_LOCK_WAIT_TIMEOUT) and
142 # stop.
143 --disable_warnings
144 --let $slave_sql_errno= 0,1205
145 --source include/stop_slave_sql.inc
146 --enable_warnings
147 --source include/start_slave_sql.inc
148
149 # Drop table for the next test
150 --source include/rpl_connection_master.inc
151 DROP TABLE t1;
152 --source include/sync_slave_sql_with_master.inc
153 --inc $i
154 }
155
156 --echo #
157 --echo # 3. Clean up
158 --source include/rpl_connection_slave.inc
159 --replace_result $saved_innodb_lock_wait_timeout INNODB_LOCK_WAIT_TIMEOUT
160 eval SET GLOBAL innodb_lock_wait_timeout = $saved_innodb_lock_wait_timeout;
161 --replace_result $saved_slave_rows_search_algorithms SLAVE_ROWS_SEARCH_ALGORITHMS
162 --disable_warnings
163 eval SET GLOBAL slave_rows_search_algorithms = "$saved_slave_rows_search_algorithms";
164 --enable_warnings
165
166 --source include/rpl_end.inc