Debian Codegraph @

mysql-test/suite/ndb/t/ndb_read_local.inc

raw
1 # ndb_read_local.inc
2 # - Assumes a 2 node, NoOfReplicas=2 cluster
3 # - Runs various types of table accesses and joins using ReadCommitted
4 # - Uses ERROR_INSERT codes in TC and SPJ that check that all
5 # reads are TC-local (e.g. use TC local SPJ + LQH instances)
6 # If there are any non-local reads, then a data node will fail with
7 # ndbrequire(false), causing testcase failure.
8 # - This verifies that the benefit of locality is available for
9 # Read_Backup and FullyReplicated on 1 NG clusters.
10 #
11 # TODO : Same test for FullyReplicated on > 1 NG clusters...
12
13 # Fetch object ids for tables, unique index, and, blob tables.
14 select id into @t1_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t1';
15 select id into @t1ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t1_id and type = 3;
16 select id into @t2_id from ndbinfo.ndb$dict_obj_info where fq_name = 'test/def/t2';
17 select id into @t2ui_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and type = 3;
18 select id into @t2bt_id from ndbinfo.ndb$dict_obj_info where parent_obj_id = @t2_id and fq_name like 'test/def/NDB$BLOB%';
19
20 # Prepare table t1 with data
21 --echo Put some data in.
22 --let $idx=$KEYS
23 --disable_query_log
24 while ($idx)
25 {
26 --eval insert into test.t1 values ($idx,$idx,$idx,$idx)
27 --dec $idx
28 }
29 --enable_query_log
30
31 --echo Make it more interesting for joining
32 update test.t1 set c=c+1, d=d+1;
33
34 # Count nodes belonging to some nodegroup
35 select count(distinct node_id) into @nodes
36 from ndbinfo.ndb$membership where group_id < 0xFFFFFF00;
37
38 --echo Check replicas
39 select count(distinct node_id)
40 from ndbinfo.operations_per_fragment
41 where fq_name='test/def/t1'
42 and fragment_num=0;
43
44 --echo Check nodes with table data
45 select count(distinct node_id)
46 from ndbinfo.operations_per_fragment
47 where fq_name='test/def/t1';
48
49 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t1_id`) {
50 die "Table t1 have not data on all nodegroups!";
51 }
52
53 --echo Check UI replicas
54 select count(distinct node_id)
55 from ndbinfo.operations_per_fragment
56 where table_id = @t1ui_id
57 and fragment_num=0;
58
59 --echo Check nodes with UI data
60 select count(distinct node_id)
61 from ndbinfo.operations_per_fragment
62 where table_id = @t1ui_id;
63
64 if (!$bug_ui) {
65 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t1ui_id`) {
66 die "Unique index for table t1 have not data on all nodegroups!";
67 } }
68
69 # Prepare table t2 with data
70
71 --echo Put some data in.
72 insert into test.t2 select a,b,c,repeat('BJC',300) from test.t1;
73
74 --echo Check blob table replicas
75 select count(distinct node_id)
76 from ndbinfo.operations_per_fragment
77 where table_id = @t2bt_id
78 and fragment_num=0;
79
80 --echo Check nodes with blob data
81 select count(distinct node_id)
82 from ndbinfo.operations_per_fragment
83 where table_id = @t2bt_id;
84
85 if (!$bug_blob) {
86 if (`select count(distinct node_id) <> @nodes from ndbinfo.operations_per_fragment where table_id = @t2bt_id`) {
87 die "Blob table for table t2 have not data on all nodegroups!";
88 } }
89
90 #
91 # Create a table 'hints' with one row and partition per node in some node group.
92 # Note, there are no relation between the row values and node ids.
93 # All partitions are expected to have its primary replica on different nodes.
94 # Each partition will have one row with values for i from 1 to number of nodes.
95 #
96 let $nodes = `select @nodes`;
97 let $i = $nodes;
98 eval set @partitions = 'PARTITION p$i VALUES IN ($i)';
99 dec $i;
100 while ($i)
101 {
102 eval set @partitions = concat(@partitions,',PARTITION p$i VALUES IN ($i)');
103 dec $i;
104 }
105 let $partitions = `select @partitions`;
106 set @@new = 1;
107 eval create table hints (i int primary key) engine=ndb partition by list(i) ($partitions);
108 let $i = $nodes;
109 while ($i)
110 {
111 eval insert into hints values ($i);
112 # Use 'eval select * from hints where i = $i;' first in a transaction to force
113 # connect to corresponding nodes DBTC.
114
115 --echo Hint node by $i of $nodes
116
117 --echo Tests on T1 (!Blobs)
118
119
120 --echo Test some basic NdbApi
121
122
123
124 --echo PK lookups
125 --replace_column 4 # 10 # 11 #
126 explain select * from test.t1 where t1.a=6;
127
128 --echo Error insert to ensure that TC requests are purely local
129 --exec $NDB_MGM -e "ALL ERROR 8083"
130
131 --let $idx=$KEYS
132 --disable_query_log
133 --disable_result_log
134 begin; # So all reads are from one node, defeating hinting
135 eval select * from hints where i = $i;
136 while ($idx)
137 {
138 --eval select * from test.t1 where a=$idx
139 --dec $idx
140 }
141 commit;
142 --enable_result_log
143 --enable_query_log
144
145 --exec $NDB_MGM -e "ALL ERROR 0"
146
147
148
149 if (!$bug_ui) {
150 --echo UI lookups
151 --replace_column 4 # 10 # 11 #
152 explain select * from test.t1 where t1.b=6;
153
154 --echo Error insert to ensure that TC requests are purely local
155 --exec $NDB_MGM -e "ALL ERROR 8083"
156
157 --let $idx=$KEYS
158 --disable_query_log
159 --disable_result_log
160 begin; # So all reads are from one node, defeating hinting
161 eval select * from hints where i = $i;
162 while ($idx)
163 {
164 --eval select * from test.t1 where b=$idx
165 --dec $idx
166 }
167 commit;
168 --enable_result_log
169 --enable_query_log
170
171 --exec $NDB_MGM -e "ALL ERROR 0"
172 }
173
174 --echo OI scan
175 --replace_column 4 # 10 # 11 #
176 explain select * from test.t1 where c > 6;
177
178 --echo Error insert to ensure that TC requests are purely local
179 --exec $NDB_MGM -e "ALL ERROR 8083"
180
181 --disable_query_log
182 --disable_result_log
183 begin;
184 eval select * from hints where i = $i;
185 select * from test.t1 where c > 6;
186 commit;
187 --enable_result_log
188 --enable_query_log
189
190 --exec $NDB_MGM -e "ALL ERROR 0"
191
192 --echo Table scan
193 --replace_column 4 # 10 # 11 #
194 explain select * from test.t1;
195
196 --echo Error insert to ensure that TC requests are purely local
197 --exec $NDB_MGM -e "ALL ERROR 8083"
198
199 --disable_query_log
200 --disable_result_log
201 begin;
202 eval select * from hints where i = $i;
203 select * from test.t1;
204 commit;
205 --enable_result_log
206 --enable_query_log
207
208 --exec $NDB_MGM -e "ALL ERROR 0"
209
210
211
212 --echo Test pushed queries (SPJ)
213
214 --echo Pushed pk->pk
215 --echo Lookup to get a.d, feeding pk lookup of b.a
216 --replace_column 4 # 10 # 11 #
217 explain select a.a, a.d, b.d
218 from
219 test.t1 a
220 join
221 test.t1 b
222 on
223 a.d = b.a
224 where a.a = 6;
225
226 --echo Error insert to ensure that SPJ requests are purely local
227 --exec $NDB_MGM -e "ALL ERROR 17014"
228
229 --let $idx=$KEYS
230 --disable_query_log
231 --disable_result_log
232 begin;
233 eval select * from hints where i = $i;
234 while ($idx)
235 {
236 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.a where a.a = $idx
237 --dec $idx
238 }
239 commit;
240 --enable_result_log
241 --enable_query_log
242
243 --exec $NDB_MGM -e "ALL ERROR 0"
244
245
246 --echo Pushed uk->uk
247 --echo Lookup to get a.d, feeding uk lookup of b.b
248 --replace_column 4 # 10 # 11 #
249 explain select a.a, a.d, b.d
250 from
251 test.t1 a
252 join
253 test.t1 b
254 on
255 a.d = b.b
256 where a.b = 6;
257
258 --echo Error insert to ensure that SPJ requests are purely local
259 --exec $NDB_MGM -e "ALL ERROR 17014"
260
261 --let $idx=$KEYS
262 --disable_query_log
263 --disable_result_log
264 begin;
265 eval select * from hints where i = $i;
266 while ($idx)
267 {
268 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.b where a.b = $idx
269 --dec $idx
270 }
271 commit;
272 --enable_result_log
273 --enable_query_log
274
275 --exec $NDB_MGM -e "ALL ERROR 0"
276
277 --echo Pushed uk->pk
278 --echo Lookup to get a.d, feeding pk lookup of b.a
279 --replace_column 4 # 10 # 11 #
280 explain select a.a, a.d, b.d
281 from
282 test.t1 a
283 join
284 test.t1 b
285 on
286 a.d = b.a
287 where a.b = 6;
288
289 --echo Error insert to ensure that SPJ requests are purely local
290 --exec $NDB_MGM -e "ALL ERROR 17014"
291
292 --let $idx=$KEYS
293 --disable_query_log
294 --disable_result_log
295 begin;
296 eval select * from hints where i = $i;
297 while ($idx)
298 {
299 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.a where a.b = $idx
300 --dec $idx
301 }
302 commit;
303 --enable_result_log
304 --enable_query_log
305
306 --exec $NDB_MGM -e "ALL ERROR 0"
307
308 --echo Pushed pk->uk
309 --echo Lookup to get a.d, feeding uk lookup of b.b
310 --replace_column 4 # 10 # 11 #
311 explain select a.a, a.d, b.d
312 from
313 test.t1 a
314 join
315 test.t1 b
316 on
317 a.d = b.b
318 where a.a = 6;
319
320 --echo Error insert to ensure that SPJ requests are purely local
321 --exec $NDB_MGM -e "ALL ERROR 17014"
322
323 --let $idx=$KEYS
324 --disable_query_log
325 --disable_result_log
326 begin;
327 eval select * from hints where i = $i;
328 while ($idx)
329 {
330 --eval select a.a, a.d, b.d from test.t1 a join test.t1 b on a.d = b.b where a.a = $idx
331 --dec $idx
332 }
333 commit;
334 --enable_result_log
335 --enable_query_log
336
337 --exec $NDB_MGM -e "ALL ERROR 0"
338
339
340
341 --echo Pushed scan originating sub-lookups
342 --echo Table scan to get a.d, feeding pk lookups of b.a
343 --replace_column 4 # 10 # 11 #
344 explain select a.a, a.d, b.d
345 from
346 test.t1 a
347 join
348 test.t1 b
349 on
350 a.d = b.a;
351
352 --echo Error insert to ensure that SPJ requests are purely local
353 --exec $NDB_MGM -e "ALL ERROR 17014"
354
355 --disable_result_log
356
357 begin;
358 eval select * from hints where i = $i;
359 select a.a, a.d, b.d
360 from
361 test.t1 a
362 join
363 test.t1 b
364 on
365 a.d = b.a;
366 commit;
367
368 --enable_result_log
369
370
371 --exec $NDB_MGM -e "ALL ERROR 0"
372
373
374
375 --echo Pushed scan originating sub-lookups
376 --echo Table scan to get a.d, feeding uk lookups of b.b
377 --replace_column 4 # 10 # 11 #
378 explain select a.a, a.d, b.d
379 from
380 test.t1 a
381 join
382 test.t1 b
383 on
384 a.d = b.b;
385
386 --echo Error insert to ensure that SPJ requests are purely local
387 --exec $NDB_MGM -e "ALL ERROR 17014"
388
389 --disable_result_log
390
391 begin;
392 eval select * from hints where i = $i;
393 select a.a, a.d, b.d
394 from
395 test.t1 a
396 join
397 test.t1 b
398 on
399 a.d = b.b;
400 commit;
401 --enable_result_log
402
403
404 --exec $NDB_MGM -e "ALL ERROR 0"
405
406 --echo Pushed scan originating sub-scans
407 --echo Range scan on a.a > 10 getting a.d, feeding range scans on b.c = a.d
408
409 --replace_column 4 # 10 # 11 #
410 explain select a.a, a.d, b.d
411 from
412 test.t1 a
413 join
414 test.t1 b
415 on
416 b.c=a.d
417 where
418 a.a>10;
419
420 --echo Error insert to ensure that SPJ requests are purely local
421 --exec $NDB_MGM -e "ALL ERROR 17014"
422
423 --disable_result_log
424
425 begin;
426 eval select * from hints where i = $i;
427 select a.a, a.d, b.d
428 from
429 test.t1 a
430 join
431 test.t1 b
432 on
433 b.c=a.d
434 where
435 a.a>10;
436 commit;
437
438 --enable_result_log
439
440
441 --exec $NDB_MGM -e "ALL ERROR 0"
442
443
444 --echo Tests on T2 (Blobs)
445
446 if (!$bug_blob)
447 {
448
449 --echo Test some basic NdbApi
450
451 --replace_column 4 # 10 # 11 #
452 explain select * from test.t2 where t2.a=6;
453
454 --echo Error insert to ensure that TC requests are purely local
455 --exec $NDB_MGM -e "ALL ERROR 8083"
456
457 --let $idx=$KEYS
458 --disable_query_log
459 --disable_result_log
460 begin; # So all reads are from one node, defeating hinting
461 eval select * from hints where i = $i;
462 while ($idx)
463 {
464 --eval select * from test.t2 where a=$idx
465 --dec $idx
466 }
467 commit;
468 --enable_result_log
469 --enable_query_log
470
471 --exec $NDB_MGM -e "ALL ERROR 0"
472
473
474 --echo UI lookups
475 --replace_column 4 # 10 # 11 #
476 explain select * from test.t2 where t2.b=6;
477
478 --echo Error insert to ensure that TC requests are purely local
479 --exec $NDB_MGM -e "ALL ERROR 8083"
480
481 --let $idx=$KEYS
482 --disable_query_log
483 --disable_result_log
484 begin; # So all reads are from one node, defeating hinting
485 eval select * from hints where i = $i;
486 while ($idx)
487 {
488 --eval select * from test.t2 where b=$idx
489 --dec $idx
490 }
491 commit;
492 --enable_result_log
493 --enable_query_log
494
495 --exec $NDB_MGM -e "ALL ERROR 0"
496
497 --echo OI scan
498 --replace_column 4 # 10 # 11 #
499 explain select * from test.t2 where c > 6;
500
501 --echo Error insert to ensure that TC requests are purely local
502 --exec $NDB_MGM -e "ALL ERROR 8083"
503
504 --disable_query_log
505 --disable_result_log
506 begin;
507 eval select * from hints where i = $i;
508 select * from test.t2 where c > 6;
509 commit;
510 --enable_result_log
511 --enable_query_log
512
513 --exec $NDB_MGM -e "ALL ERROR 0"
514
515 --echo Table scan
516 --replace_column 4 # 10 # 11 #
517 explain select * from test.t2;
518
519 --echo Error insert to ensure that TC requests are purely local
520 --exec $NDB_MGM -e "ALL ERROR 8083"
521
522 --disable_query_log
523 --disable_result_log
524 begin;
525 eval select * from hints where i = $i;
526 select * from test.t2;
527 commit;
528 --enable_result_log
529 --enable_query_log
530
531 --exec $NDB_MGM -e "ALL ERROR 0"
532 }
533
534 # Decrement $i to next node hint value and rerun all tests against a new node
535 dec $i;
536 }
537 drop table hints;
538
539 delete from t1;
540 delete from t2;
541