Debian Codegraph @

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

raw
1 -- source include/not_group_replication_plugin.inc
2 -- source include/have_binlog_format_row.inc
3 -- source include/have_debug.inc
4 -- source include/master-slave.inc
5
6 #
7 # WL#5597 tests
8 #
9 # These tests check whether the correct algorithm for searching the
10 # rows was chosen, depending on the setting of
11 # @@global.slave_rows_search_algorithms and the table definition.
12 #
13 # We test all combinations, but leave out the offending ones:
14 # - @@global.slave_rows_search_algorithms= ''
15 # - @@global.slave_rows_search_algorithms= 'INDEX_SCAN'
16 #
17 # We do not allow setting only INDEX_SCAN or the empty value.
18 #
19
20 -- connection master
21
22 CREATE TABLE t1 (c1 INT);
23 CREATE TABLE t2 (c1 INT PRIMARY KEY);
24 CREATE TABLE t3 (c1 INT UNIQUE KEY NOT NULL);
25 CREATE TABLE t4 (c1 INT KEY);
26
27 INSERT INTO t1 VALUES (1);
28 INSERT INTO t2 VALUES (1);
29 INSERT INTO t3 VALUES (1);
30 INSERT INTO t4 VALUES (1);
31
32 --source include/sync_slave_sql_with_master.inc
33 SET @saved_slave_rows_search_algorithms= @@global.slave_rows_search_algorithms;
34
35 ###################### TABLE_SCAN assertions
36
37 -- connection slave
38 SET @@global.slave_rows_search_algorithms= 'TABLE_SCAN';
39 -- connection master
40
41 UPDATE t1 SET c1= 2;
42 -- let $expected_alg= 'TABLE_SCAN'
43 -- source include/rpl_hash_scan_assertion.inc
44
45 UPDATE t2 SET c1= 2;
46 -- let $expected_alg= 'TABLE_SCAN'
47 -- source include/rpl_hash_scan_assertion.inc
48
49 UPDATE t3 SET c1= 2;
50 -- let $expected_alg= 'TABLE_SCAN'
51 -- source include/rpl_hash_scan_assertion.inc
52
53 UPDATE t4 SET c1= 2;
54 -- let $expected_alg= 'TABLE_SCAN'
55 -- source include/rpl_hash_scan_assertion.inc
56
57 ###################### TABLE_SCAN,INDEX_SCAN
58
59 -- connection slave
60 SET @@global.slave_rows_search_algorithms= 'TABLE_SCAN,INDEX_SCAN';
61 -- connection master
62
63 UPDATE t1 SET c1= 3;
64 -- let $expected_alg= 'TABLE_SCAN'
65 -- source include/rpl_hash_scan_assertion.inc
66
67 UPDATE t2 SET c1= 3;
68 -- let $expected_alg= 'INDEX_SCAN'
69 -- source include/rpl_hash_scan_assertion.inc
70
71 UPDATE t3 SET c1= 3;
72 -- let $expected_alg= 'INDEX_SCAN'
73 -- source include/rpl_hash_scan_assertion.inc
74
75 UPDATE t4 SET c1= 3;
76 -- let $expected_alg= 'INDEX_SCAN'
77 -- source include/rpl_hash_scan_assertion.inc
78
79 ###################### TABLE_SCAN,HASH_SCAN
80
81 -- connection slave
82 SET @@global.slave_rows_search_algorithms= 'TABLE_SCAN,HASH_SCAN';
83 -- connection master
84
85 UPDATE t1 SET c1= 4;
86 -- let $expected_alg= 'HASH_SCAN'
87 -- source include/rpl_hash_scan_assertion.inc
88
89 UPDATE t2 SET c1= 4;
90 -- let $expected_alg= 'HASH_SCAN'
91 -- source include/rpl_hash_scan_assertion.inc
92
93 UPDATE t3 SET c1= 4;
94 -- let $expected_alg= 'HASH_SCAN'
95 -- source include/rpl_hash_scan_assertion.inc
96
97 UPDATE t4 SET c1= 4;
98 -- let $expected_alg= 'HASH_SCAN'
99 -- source include/rpl_hash_scan_assertion.inc
100
101 ###################### HASH_SCAN
102
103 -- connection slave
104 SET @@global.slave_rows_search_algorithms= 'HASH_SCAN';
105 -- connection master
106
107 UPDATE t1 SET c1= 5;
108 -- let $expected_alg= 'HASH_SCAN'
109 -- source include/rpl_hash_scan_assertion.inc
110
111 UPDATE t2 SET c1= 5;
112 -- let $expected_alg= 'HASH_SCAN'
113 -- source include/rpl_hash_scan_assertion.inc
114
115 UPDATE t3 SET c1= 5;
116 -- let $expected_alg= 'HASH_SCAN'
117 -- source include/rpl_hash_scan_assertion.inc
118
119 UPDATE t4 SET c1= 5;
120 -- let $expected_alg= 'HASH_SCAN'
121 -- source include/rpl_hash_scan_assertion.inc
122
123 ###################### HASH_SCAN,INDEX_SCAN
124
125 -- connection slave
126 SET @@global.slave_rows_search_algorithms= 'HASH_SCAN,INDEX_SCAN';
127 -- connection master
128
129 UPDATE t1 SET c1= 6;
130 -- let $expected_alg= 'HASH_SCAN'
131 -- source include/rpl_hash_scan_assertion.inc
132
133 UPDATE t2 SET c1= 6;
134 -- let $expected_alg= 'INDEX_SCAN'
135 -- source include/rpl_hash_scan_assertion.inc
136
137 UPDATE t3 SET c1= 6;
138 -- let $expected_alg= 'INDEX_SCAN'
139 -- source include/rpl_hash_scan_assertion.inc
140
141 UPDATE t4 SET c1= 6;
142 -- let $expected_alg= 'INDEX_SCAN'
143 -- source include/rpl_hash_scan_assertion.inc
144
145 ###################### HASH_SCAN,INDEX_SCAN,TABLE_SCAN
146
147 -- connection slave
148 SET @@global.slave_rows_search_algorithms= 'HASH_SCAN,INDEX_SCAN,TABLE_SCAN';
149 -- connection master
150
151 UPDATE t1 SET c1= 7;
152 -- let $expected_alg= 'HASH_SCAN'
153 -- source include/rpl_hash_scan_assertion.inc
154
155 UPDATE t2 SET c1= 7;
156 -- let $expected_alg= 'INDEX_SCAN'
157 -- source include/rpl_hash_scan_assertion.inc
158
159 UPDATE t3 SET c1= 7;
160 -- let $expected_alg= 'INDEX_SCAN'
161 -- source include/rpl_hash_scan_assertion.inc
162
163 UPDATE t4 SET c1= 7;
164 -- let $expected_alg= 'INDEX_SCAN'
165 -- source include/rpl_hash_scan_assertion.inc
166
167 # Begin of Test cases for Bug#18860225
168 # HASH_SCAN SEEMS BROKEN: CAN'T FIND RECORD IN 'T1' ERROR_CODE: 1032
169 # Test Case 1: Key type is <Int>
170 --connection slave
171 SET @@global.slave_rows_search_algorithms= 'HASH_SCAN';
172 --connection master
173 CREATE TABLE t5(c1 INT, c2 INT, KEY(c2));
174 INSERT INTO t5 VALUES (1,1),(2,2),(3,1),(4,NULL),(5,2),(6,3);
175 DELETE FROM t5 ORDER BY c1;
176 DROP TABLE t5;
177 --source include/sync_slave_sql_with_master.inc
178
179 # Test Case 2: Key type is <BigInt>
180 --connection master
181 CREATE TABLE t5(c1 BIGINT, c2 BIGINT, KEY(c2));
182 INSERT INTO t5 VALUES (1,1),(2,2),(3,1),(4,NULL),(5,2),(6,3);
183 DELETE FROM t5 ORDER BY c1;
184 DROP TABLE t5;
185 --source include/sync_slave_sql_with_master.inc
186
187 # Test Case 3: Key type is <Varchar>
188 --connection master
189 CREATE TABLE t5(c1 INT, c2 VARCHAR(20), KEY(c2));
190 INSERT INTO t5 VALUES (1,"value1"),(2,"value2"),(3,"value1"),(4,NULL),(5,"value2"),(6,"value3");
191 DELETE FROM t5 ORDER BY c1;
192 DROP TABLE t5;
193 --source include/sync_slave_sql_with_master.inc
194
195 # Test Case 4: Key is composite key and type is <Int,Varchar>
196 # and Varchar key having different length values
197 --connection master
198 CREATE TABLE t5(c1 INT, c2 INT, c3 VARCHAR(20), KEY(c2,c3));
199 INSERT INTO t5 VALUES (1,1,"ab"),(2,2,"cde"),(3,1,"12345"),(4,4,"random"),(5,NULL,NULL),(6,5,NULL),(7,NULL,"lmnopqr");
200 DELETE FROM t5 ORDER BY c1;
201 DROP TABLE t5;
202 --source include/sync_slave_sql_with_master.inc
203 # End of Test cases for Bug#18860225
204
205 # Test cases for BUG#26137159
206 # Verify that update and delete operations can be safely replicated when the
207 # partition function does not include the key used in the index
208
209 SET @@global.slave_rows_search_algorithms= 'INDEX_SCAN,HASH_SCAN';
210
211 call mtr.add_suppression("Could not execute Delete_rows event on table test.t6");
212 call mtr.add_suppression("Could not execute Update_rows event on table test.t6");
213 call mtr.add_suppression("Can't find record in 't6'");
214 # when mts is enabled
215 call mtr.add_suppression("The replica coordinator and worker threads are stopped");
216
217 --let $i= 1
218
219 while ($i<3)
220 {
221 # Check that the slave can delete/update a value that is not the index key
222 --source include/rpl_connection_master.inc
223 CREATE TABLE t6(num INT, year INT, KEY year (year))
224 PARTITION BY HASH(num) PARTITIONS 5;
225 INSERT INTO t6 VALUES(6, 1977);
226 INSERT INTO t6 VALUES(1, 1983);
227 INSERT INTO t6 VALUES(9, 1977);
228 if ($i==1)
229 {
230 DELETE FROM t6 WHERE num= 9;
231 }
232 if ($i==2)
233 {
234 UPDATE t6 SET num= 10 WHERE num= 9;
235 }
236 --source include/sync_slave_sql_with_master.inc
237 --source include/rpl_connection_master.inc
238 --let $diff_tables= master:t6, slave:t6
239 --source include/diff_tables.inc
240 DROP TABLE t6;
241 --source include/sync_slave_sql_with_master.inc
242
243 # Check that the slave can delete/update a repeated value that is not the
244 # index key
245 --source include/rpl_connection_master.inc
246 CREATE TABLE t6(num INT, year INT, KEY year (year))
247 PARTITION BY HASH(num) PARTITIONS 5;
248 INSERT INTO t6 VALUES(6, 1977);
249 INSERT INTO t6 VALUES(1, 1983);
250 INSERT INTO t6 VALUES(9, 1977);
251 INSERT INTO t6 VALUES(9, 1977);
252 INSERT INTO t6 VALUES(9, 1987);
253 INSERT INTO t6 VALUES(9, 9);
254 INSERT INTO t6 VALUES(9, 1555555500);
255 if ($i==1)
256 {
257 DELETE FROM t6 WHERE num= 9;
258 }
259 if ($i==2)
260 {
261 UPDATE t6 SET num= 10 WHERE num= 9;
262 }
263 --source include/sync_slave_sql_with_master.inc
264 --source include/rpl_connection_master.inc
265 --let $diff_tables= master:t6, slave:t6
266 --source include/diff_tables.inc
267 DROP TABLE t6;
268 --source include/sync_slave_sql_with_master.inc
269
270 # Check that the slave can delete/update a repeated value that is not the
271 # index key when using another column for key and a more complex partition
272 # function
273 --source include/rpl_connection_master.inc
274 CREATE TABLE t6(num INT, year INT, num_2 INT, KEY year (year))
275 PARTITION BY HASH(num_2-num) PARTITIONS 5;
276 INSERT INTO t6 VALUES(6, 1977, 1);
277 INSERT INTO t6 VALUES(1, 1983, 2);
278 INSERT INTO t6 VALUES(9, 1977, 1);
279 INSERT INTO t6 VALUES(9, 1983, 1);
280 INSERT INTO t6 VALUES(9, 1977, 3);
281 if ($i==1)
282 {
283 DELETE FROM t6 WHERE num_2= 1;
284 }
285 if ($i==2)
286 {
287 UPDATE t6 SET num= 10 WHERE num_2= 1;
288 }
289 --source include/sync_slave_sql_with_master.inc
290 --source include/rpl_connection_master.inc
291 --let $diff_tables= master:t6, slave:t6
292 --source include/diff_tables.inc
293 DROP TABLE t6;
294 --source include/sync_slave_sql_with_master.inc
295
296 # Check that the slave will error if trying to delete a non-existing value
297 --source include/stop_slave_sql.inc
298 --source include/rpl_connection_master.inc
299 CREATE TABLE t6(num INT, year INT, KEY year (year))
300 PARTITION BY HASH(num) PARTITIONS 5;
301 INSERT INTO t6 VALUES(6, 1977);
302 INSERT INTO t6 VALUES(1, 1983);
303 INSERT INTO t6 VALUES(9, 1977);
304 SET SQL_LOG_BIN= 0;
305 INSERT INTO t6 VALUES(10, 1977);
306 SET SQL_LOG_BIN= 1;
307 if ($i==1)
308 {
309 DELETE FROM t6 WHERE num= 10;
310 }
311 if ($i==2)
312 {
313 UPDATE t6 SET num= 9 WHERE num= 10;
314 }
315
316 --source include/rpl_connection_slave.inc
317 START SLAVE SQL_THREAD;
318 --let $slave_sql_errno= convert_error(ER_KEY_NOT_FOUND)
319 --source include/wait_for_slave_sql_error.inc
320
321 # update the slave to clear the error
322 --source include/stop_slave.inc
323 INSERT INTO t6 VALUES(10, 1977);
324 -- source include/start_slave.inc
325
326 --source include/rpl_connection_master.inc
327 DROP TABLE t6;
328 --source include/sync_slave_sql_with_master.inc
329
330 --inc $i
331 }
332
333
334 #clean Up
335 SET @@global.slave_rows_search_algorithms= @saved_slave_rows_search_algorithms;
336 --connection master
337 DROP TABLE IF EXISTS t1, t2, t3, t4;
338 --source include/sync_slave_sql_with_master.inc
339
340 -- source include/rpl_reset.inc
341
342 -- source include/rpl_end.inc
343