Debian Codegraph @

mysql-test/suite/rpl/r/rpl_replica_read_lock.result

raw
1 include/only_with_option.inc [GLOBAL.replica_transaction_retries > 2]
2 #
3 # 1. Create source-replica topology
4 include/master-slave.inc
5 Warnings:
6 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
7 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
8 [connection master]
9 [connection slave]
10 SET GLOBAL innodb_lock_wait_timeout = 2;
11 SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';
12 CALL mtr.add_suppression("Replica SQL for channel.*Consider raising the value of the replica_transaction_retries variable.*");
13 CALL mtr.add_suppression("Replica SQL for channel.*The replica coordinator and worker threads are stopped.*");
14 # 2. For each slave_rows_search_algorithms[INDEX_SCAN, HASH_SCAN(with
15 # index), HASH_SCAN(with two non-unique index), HASH_SCAN(with out
16 # index), TABLE_SCAN]
17 #
18 # 2.0 Configure source and replica
19 [connection master]
20 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=innodb;
21 INSERT INTO t1 VALUES (10, 10), (20, 20);
22 include/sync_slave_sql_with_master.inc
23 #
24 # 2.1 Stop replica
25 include/stop_slave.inc
26 #
27 # 2.2 Execute DELETE transaction on source
28 [connection master]
29 BEGIN;
30 DELETE FROM t1;
31 COMMIT;
32 #
33 # 2.3 Begin SELECT ... FOR UPDATE on replica
34 [connection slave]
35 BEGIN;
36 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
37 a b
38 20 20
39 #
40 # 2.4 Start replica from another connection
41 [connection slave1]
42 include/start_slave.inc
43 #
44 # 2.5 Wait until the applier retries:'count_transactions_retries' will be
45 # incremented
46 [connection slave]
47 #
48 # 2.6 Commit SELECT ... FOR UPDATE on replica
49 COMMIT;
50 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
51 Variable_name Value
52 Replica_rows_last_search_algorithm_used INDEX_SCAN
53 #
54 # 2.7 Restart replica applier
55 include/stop_slave_sql.inc
56 include/start_slave_sql.inc
57 [connection master]
58 DROP TABLE t1;
59 include/sync_slave_sql_with_master.inc
60 #
61 # 2.0 Configure source and replica
62 [connection master]
63 CREATE TABLE t1 (a INT, b INT, key(a)) ENGINE=innodb;
64 INSERT INTO t1 VALUES (10, 10), (20, 20);
65 include/sync_slave_sql_with_master.inc
66 #
67 # 2.1 Stop replica
68 include/stop_slave.inc
69 #
70 # 2.2 Execute DELETE transaction on source
71 [connection master]
72 BEGIN;
73 DELETE FROM t1;
74 COMMIT;
75 #
76 # 2.3 Begin SELECT ... FOR UPDATE on replica
77 [connection slave]
78 BEGIN;
79 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
80 a b
81 20 20
82 #
83 # 2.4 Start replica from another connection
84 [connection slave1]
85 include/start_slave.inc
86 #
87 # 2.5 Wait until the applier retries:'count_transactions_retries' will be
88 # incremented
89 [connection slave]
90 #
91 # 2.6 Commit SELECT ... FOR UPDATE on replica
92 COMMIT;
93 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
94 Variable_name Value
95 Replica_rows_last_search_algorithm_used HASH_SCAN
96 #
97 # 2.7 Restart replica applier
98 include/stop_slave_sql.inc
99 include/start_slave_sql.inc
100 [connection master]
101 DROP TABLE t1;
102 include/sync_slave_sql_with_master.inc
103 #
104 # 2.0 Configure source and replica
105 [connection master]
106 CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=innodb;
107 INSERT INTO t1 VALUES (10, 10), (10, 20), (10, 30), (20, 10), (20, 20);
108 include/sync_slave_sql_with_master.inc
109 #
110 # 2.1 Stop replica
111 include/stop_slave.inc
112 #
113 # 2.2 Execute DELETE transaction on source
114 [connection master]
115 BEGIN;
116 DELETE FROM t1;
117 COMMIT;
118 #
119 # 2.3 Begin SELECT ... FOR UPDATE on replica
120 [connection slave]
121 BEGIN;
122 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
123 a b
124 10 20
125 20 20
126 #
127 # 2.4 Start replica from another connection
128 [connection slave1]
129 include/start_slave.inc
130 #
131 # 2.5 Wait until the applier retries:'count_transactions_retries' will be
132 # incremented
133 [connection slave]
134 #
135 # 2.6 Commit SELECT ... FOR UPDATE on replica
136 COMMIT;
137 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
138 Variable_name Value
139 Replica_rows_last_search_algorithm_used HASH_SCAN
140 #
141 # 2.7 Restart replica applier
142 include/stop_slave_sql.inc
143 include/start_slave_sql.inc
144 [connection master]
145 DROP TABLE t1;
146 include/sync_slave_sql_with_master.inc
147 #
148 # 2.0 Configure source and replica
149 [connection master]
150 CREATE TABLE t1 (a INT, b INT) ENGINE=innodb;
151 INSERT INTO t1 VALUES (10, 10), (20, 20);
152 include/sync_slave_sql_with_master.inc
153 #
154 # 2.1 Stop replica
155 include/stop_slave.inc
156 #
157 # 2.2 Execute DELETE transaction on source
158 [connection master]
159 BEGIN;
160 DELETE FROM t1;
161 COMMIT;
162 #
163 # 2.3 Begin SELECT ... FOR UPDATE on replica
164 [connection slave]
165 BEGIN;
166 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
167 a b
168 20 20
169 #
170 # 2.4 Start replica from another connection
171 [connection slave1]
172 include/start_slave.inc
173 #
174 # 2.5 Wait until the applier retries:'count_transactions_retries' will be
175 # incremented
176 [connection slave]
177 #
178 # 2.6 Commit SELECT ... FOR UPDATE on replica
179 COMMIT;
180 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
181 Variable_name Value
182 Replica_rows_last_search_algorithm_used HASH_SCAN
183 #
184 # 2.7 Restart replica applier
185 include/stop_slave_sql.inc
186 include/start_slave_sql.inc
187 [connection master]
188 DROP TABLE t1;
189 include/sync_slave_sql_with_master.inc
190 #
191 # 2.0 Configure source and replica
192 [connection master]
193 CREATE TABLE t1 (a INT, b INT) ENGINE=innodb;
194 INSERT INTO t1 VALUES (10, 10), (20, 20);
195 include/sync_slave_sql_with_master.inc
196 #
197 # 2.1 Stop replica
198 include/stop_slave.inc
199 #
200 # 2.2 Execute DELETE transaction on source
201 [connection master]
202 BEGIN;
203 DELETE FROM t1;
204 COMMIT;
205 #
206 # 2.3 Begin SELECT ... FOR UPDATE on replica
207 [connection slave]
208 SET GLOBAL slave_rows_search_algorithms = 'TABLE_SCAN';
209 BEGIN;
210 SELECT * FROM t1 WHERE b = 20 FOR UPDATE;
211 a b
212 20 20
213 #
214 # 2.4 Start replica from another connection
215 [connection slave1]
216 include/start_slave.inc
217 #
218 # 2.5 Wait until the applier retries:'count_transactions_retries' will be
219 # incremented
220 [connection slave]
221 #
222 # 2.6 Commit SELECT ... FOR UPDATE on replica
223 COMMIT;
224 SHOW STATUS LIKE 'Replica_rows_last_search_algorithm_used';
225 Variable_name Value
226 Replica_rows_last_search_algorithm_used TABLE_SCAN
227 #
228 # 2.7 Restart replica applier
229 include/stop_slave_sql.inc
230 include/start_slave_sql.inc
231 [connection master]
232 DROP TABLE t1;
233 include/sync_slave_sql_with_master.inc
234 #
235 # 3. Clean up
236 [connection slave]
237 SET GLOBAL innodb_lock_wait_timeout = INNODB_LOCK_WAIT_TIMEOUT;
238 SET GLOBAL slave_rows_search_algorithms = "SLAVE_ROWS_SEARCH_ALGORITHMS";
239 include/rpl_end.inc