Debian Codegraph @

mysql-test/suite/ndb/r/ndb_rb_fragment_count.result

raw
1 set @save_ndb_read_backup = @@global.ndb_read_backup;
2 set @@global.ndb_read_backup = ON;
3 create table t1
4 (a bigint, b bigint, c bigint, d bigint,
5 primary key (a,b,c,d))
6 engine=ndb;
7 insert into t1 values
8 (1,2,3,4),(2,3,4,5),(3,4,5,6),
9 (3,2,3,4),(1,3,4,5),(2,4,5,6),
10 (1,2,3,5),(2,3,4,8),(3,4,5,9),
11 (3,2,3,5),(1,3,4,8),(2,4,5,9),
12 (1,2,3,6),(2,3,4,6),(3,4,5,7),
13 (3,2,3,6),(1,3,4,6),(2,4,5,7),
14 (1,2,3,7),(2,3,4,7),(3,4,5,8),
15 (3,2,3,7),(1,3,4,7),(2,4,5,8),
16 (1,3,3,4),(2,4,4,5),(3,5,5,6),
17 (3,3,3,4),(1,4,4,5),(2,5,5,6),
18 (1,3,3,5),(2,4,4,8),(3,5,5,9),
19 (3,3,3,5),(1,4,4,8),(2,5,5,9),
20 (1,3,3,6),(2,4,4,6),(3,5,5,7),
21 (3,3,3,6),(1,4,4,6),(2,5,5,7),
22 (1,3,3,7),(2,4,4,7),(3,5,5,8),
23 (3,3,3,7),(1,4,4,7),(2,5,5,8);
24 select count(*) into @cnt_start
25 from information_schema.partitions
26 where table_schema = 'test' and table_name = 't1';
27 select node_id, fragment_num from ndbinfo.memory_per_fragment
28 where fq_name = 'test/def/t1' order by node_id, fragment_num;
29 node_id fragment_num
30 1 0
31 1 1
32 1 2
33 1 3
34 1 4
35 1 5
36 1 6
37 1 7
38 2 0
39 2 1
40 2 2
41 2 3
42 2 4
43 2 5
44 2 6
45 2 7
46 select count(*) as cnt from ndbinfo.memory_per_fragment
47 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
48 cnt
49 2
50 2
51 2
52 2
53 2
54 2
55 2
56 2
57 alter table t1 algorithm=copy,
58 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
59 select count(*) into @cnt_one_per_ldm_per_node
60 from information_schema.partitions
61 where table_schema = 'test' and table_name = 't1';
62 select count(*) as cnt from ndbinfo.memory_per_fragment
63 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
64 cnt
65 2
66 2
67 2
68 2
69 2
70 2
71 2
72 2
73 select node_id, fragment_num from ndbinfo.memory_per_fragment
74 where fq_name = 'test/def/t1' order by node_id, fragment_num;
75 node_id fragment_num
76 1 0
77 1 1
78 1 2
79 1 3
80 1 4
81 1 5
82 1 6
83 1 7
84 2 0
85 2 1
86 2 2
87 2 3
88 2 4
89 2 5
90 2 6
91 2 7
92 select count(*) as cnt from ndbinfo.memory_per_fragment
93 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
94 cnt
95 2
96 2
97 2
98 2
99 2
100 2
101 2
102 2
103 alter table t1 algorithm=copy,
104 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
105 select count(*) into @cnt_one_per_ldm_per_nodegroup
106 from information_schema.partitions
107 where table_schema = 'test' and table_name = 't1';
108 select node_id, fragment_num from ndbinfo.memory_per_fragment
109 where fq_name = 'test/def/t1' order by node_id, fragment_num;
110 node_id fragment_num
111 1 0
112 1 1
113 1 2
114 1 3
115 2 0
116 2 1
117 2 2
118 2 3
119 select count(*) as cnt from ndbinfo.memory_per_fragment
120 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
121 cnt
122 1
123 1
124 1
125 1
126 1
127 1
128 1
129 1
130 alter table t1 algorithm=copy,
131 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_NODE";
132 select count(*) into @cnt_one_per_node
133 from information_schema.partitions
134 where table_schema = 'test' and table_name = 't1';
135 select node_id, fragment_num from ndbinfo.memory_per_fragment
136 where fq_name = 'test/def/t1' order by node_id, fragment_num;
137 node_id fragment_num
138 1 0
139 1 1
140 2 0
141 2 1
142 select count(*) as cnt from ndbinfo.memory_per_fragment
143 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
144 cnt
145 1
146 1
147 1
148 1
149 alter table t1 algorithm=copy,
150 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
151 select count(*) into @cnt_one_per_nodegroup
152 from information_schema.partitions
153 where table_schema = 'test' and table_name = 't1';
154 select node_id, fragment_num from ndbinfo.memory_per_fragment
155 where fq_name = 'test/def/t1' order by node_id, fragment_num;
156 node_id fragment_num
157 1 0
158 2 0
159 select count(*) as cnt from ndbinfo.memory_per_fragment
160 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
161 cnt
162 1
163 1
164 drop table t1;
165 select @cnt_start = @cnt_one_per_ldm_per_node;
166 @cnt_start = @cnt_one_per_ldm_per_node
167 1
168 select @cnt_one_per_ldm_per_node > @cnt_one_per_ldm_per_nodegroup;
169 @cnt_one_per_ldm_per_node > @cnt_one_per_ldm_per_nodegroup
170 1
171 select @cnt_one_per_ldm_per_node > @cnt_one_per_node;
172 @cnt_one_per_ldm_per_node > @cnt_one_per_node
173 1
174 select @cnt_one_per_nodegroup <= @cnt_one_per_ldm_per_nodegroup;
175 @cnt_one_per_nodegroup <= @cnt_one_per_ldm_per_nodegroup
176 1
177 select @cnt_one_per_nodegroup = 1;
178 @cnt_one_per_nodegroup = 1
179 1
180 create table t1 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
181 engine=ndb;
182 select node_id, fragment_num from ndbinfo.memory_per_fragment
183 where fq_name = 'test/def/t1' order by node_id, fragment_num;
184 node_id fragment_num
185 1 0
186 1 1
187 1 2
188 1 3
189 1 4
190 1 5
191 1 6
192 1 7
193 2 0
194 2 1
195 2 2
196 2 3
197 2 4
198 2 5
199 2 6
200 2 7
201 select count(*) as cnt from ndbinfo.memory_per_fragment
202 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
203 cnt
204 2
205 2
206 2
207 2
208 2
209 2
210 2
211 2
212 create table t2 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
213 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
214 select node_id, fragment_num from ndbinfo.memory_per_fragment
215 where fq_name = 'test/def/t2' order by node_id, fragment_num;
216 node_id fragment_num
217 1 0
218 1 1
219 1 2
220 1 3
221 1 4
222 1 5
223 1 6
224 1 7
225 2 0
226 2 1
227 2 2
228 2 3
229 2 4
230 2 5
231 2 6
232 2 7
233 select count(*) as cnt from ndbinfo.memory_per_fragment
234 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
235 cnt
236 2
237 2
238 2
239 2
240 2
241 2
242 2
243 2
244 create table t3 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
245 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
246 select node_id, fragment_num from ndbinfo.memory_per_fragment
247 where fq_name = 'test/def/t3' order by node_id, fragment_num;
248 node_id fragment_num
249 1 0
250 1 1
251 1 2
252 1 3
253 2 0
254 2 1
255 2 2
256 2 3
257 select count(*) as cnt from ndbinfo.memory_per_fragment
258 where fq_name = 'test/def/t3' group by node_id, block_instance order by cnt desc;
259 cnt
260 1
261 1
262 1
263 1
264 1
265 1
266 1
267 1
268 create table t4 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
269 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_NODE";
270 select node_id, fragment_num from ndbinfo.memory_per_fragment
271 where fq_name = 'test/def/t4' order by node_id, fragment_num;
272 node_id fragment_num
273 1 0
274 1 1
275 2 0
276 2 1
277 select count(*) as cnt from ndbinfo.memory_per_fragment
278 where fq_name = 'test/def/t4' group by node_id, block_instance order by cnt desc;
279 cnt
280 1
281 1
282 1
283 1
284 create table t5 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
285 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
286 select node_id, fragment_num from ndbinfo.memory_per_fragment
287 where fq_name = 'test/def/t5' order by node_id, fragment_num;
288 node_id fragment_num
289 1 0
290 2 0
291 select count(*) as cnt from ndbinfo.memory_per_fragment
292 where fq_name = 'test/def/t5' group by node_id, block_instance order by cnt desc;
293 cnt
294 1
295 1
296 select count(*) into @cnt_t1_0
297 from information_schema.partitions
298 where table_schema = 'test' and table_name = 't1';
299 select count(*) into @cnt_t2_0
300 from information_schema.partitions
301 where table_schema = 'test' and table_name = 't2';
302 select count(*) into @cnt_t3_0
303 from information_schema.partitions
304 where table_schema = 'test' and table_name = 't3';
305 select count(*) into @cnt_t4_0
306 from information_schema.partitions
307 where table_schema = 'test' and table_name = 't4';
308 select count(*) into @cnt_t5_0
309 from information_schema.partitions
310 where table_schema = 'test' and table_name = 't5';
311 alter table t1 algorithm=inplace, reorganize partition;
312 select node_id, fragment_num from ndbinfo.memory_per_fragment
313 where fq_name = 'test/def/t1' order by node_id, fragment_num;
314 node_id fragment_num
315 1 0
316 1 1
317 1 2
318 1 3
319 1 4
320 1 5
321 1 6
322 1 7
323 2 0
324 2 1
325 2 2
326 2 3
327 2 4
328 2 5
329 2 6
330 2 7
331 select count(*) as cnt from ndbinfo.memory_per_fragment
332 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
333 cnt
334 2
335 2
336 2
337 2
338 2
339 2
340 2
341 2
342 alter table t2 algorithm=inplace, reorganize partition;
343 select node_id, fragment_num from ndbinfo.memory_per_fragment
344 where fq_name = 'test/def/t2' order by node_id, fragment_num;
345 node_id fragment_num
346 1 0
347 1 1
348 1 2
349 1 3
350 1 4
351 1 5
352 1 6
353 1 7
354 2 0
355 2 1
356 2 2
357 2 3
358 2 4
359 2 5
360 2 6
361 2 7
362 select count(*) as cnt from ndbinfo.memory_per_fragment
363 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
364 cnt
365 2
366 2
367 2
368 2
369 2
370 2
371 2
372 2
373 alter table t3 algorithm=inplace, reorganize partition;
374 select node_id, fragment_num from ndbinfo.memory_per_fragment
375 where fq_name = 'test/def/t3' order by node_id, fragment_num;
376 node_id fragment_num
377 1 0
378 1 1
379 1 2
380 1 3
381 2 0
382 2 1
383 2 2
384 2 3
385 select count(*) as cnt from ndbinfo.memory_per_fragment
386 where fq_name = 'test/def/t3' group by node_id, block_instance order by cnt desc;
387 cnt
388 1
389 1
390 1
391 1
392 1
393 1
394 1
395 1
396 alter table t4 algorithm=inplace, reorganize partition;
397 select node_id, fragment_num from ndbinfo.memory_per_fragment
398 where fq_name = 'test/def/t4' order by node_id, fragment_num;
399 node_id fragment_num
400 1 0
401 1 1
402 2 0
403 2 1
404 select count(*) as cnt from ndbinfo.memory_per_fragment
405 where fq_name = 'test/def/t4' group by node_id, block_instance order by cnt desc;
406 cnt
407 1
408 1
409 1
410 1
411 alter table t5 algorithm=inplace, reorganize partition;
412 select node_id, fragment_num from ndbinfo.memory_per_fragment
413 where fq_name = 'test/def/t5' order by node_id, fragment_num;
414 node_id fragment_num
415 1 0
416 2 0
417 select count(*) as cnt from ndbinfo.memory_per_fragment
418 where fq_name = 'test/def/t5' group by node_id, block_instance order by cnt desc;
419 cnt
420 1
421 1
422 select count(*) into @cnt_t1_1
423 from information_schema.partitions
424 where table_schema = 'test' and table_name = 't1';
425 select count(*) into @cnt_t2_1
426 from information_schema.partitions
427 where table_schema = 'test' and table_name = 't2';
428 select count(*) into @cnt_t3_1
429 from information_schema.partitions
430 where table_schema = 'test' and table_name = 't3';
431 select count(*) into @cnt_t4_1
432 from information_schema.partitions
433 where table_schema = 'test' and table_name = 't4';
434 select count(*) into @cnt_t5_1
435 from information_schema.partitions
436 where table_schema = 'test' and table_name = 't5';
437 select @cnt_t1_0 = @cnt_t1_1;
438 @cnt_t1_0 = @cnt_t1_1
439 1
440 select @cnt_t2_0 = @cnt_t2_1;
441 @cnt_t2_0 = @cnt_t2_1
442 1
443 select @cnt_t3_0 = @cnt_t3_1;
444 @cnt_t3_0 = @cnt_t3_1
445 1
446 select @cnt_t4_0 = @cnt_t4_1;
447 @cnt_t4_0 = @cnt_t4_1
448 1
449 select @cnt_t5_0 = @cnt_t5_1;
450 @cnt_t5_0 = @cnt_t5_1
451 1
452 alter table t1 algorithm=inplace, add partition partitions 1;
453 select node_id, fragment_num from ndbinfo.memory_per_fragment
454 where fq_name = 'test/def/t1' order by node_id, fragment_num;
455 node_id fragment_num
456 1 0
457 1 1
458 1 2
459 1 3
460 1 4
461 1 5
462 1 6
463 1 7
464 1 8
465 2 0
466 2 1
467 2 2
468 2 3
469 2 4
470 2 5
471 2 6
472 2 7
473 2 8
474 select count(*) as cnt from ndbinfo.memory_per_fragment
475 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
476 cnt
477 3
478 3
479 2
480 2
481 2
482 2
483 2
484 2
485 alter table t2 algorithm=inplace, add partition partitions 1;
486 select node_id, fragment_num from ndbinfo.memory_per_fragment
487 where fq_name = 'test/def/t2' order by node_id, fragment_num;
488 node_id fragment_num
489 1 0
490 1 1
491 1 2
492 1 3
493 1 4
494 1 5
495 1 6
496 1 7
497 1 8
498 2 0
499 2 1
500 2 2
501 2 3
502 2 4
503 2 5
504 2 6
505 2 7
506 2 8
507 select count(*) as cnt from ndbinfo.memory_per_fragment
508 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
509 cnt
510 3
511 3
512 2
513 2
514 2
515 2
516 2
517 2
518 alter table t3 algorithm=inplace, add partition partitions 1;
519 select node_id, fragment_num from ndbinfo.memory_per_fragment
520 where fq_name = 'test/def/t3' order by node_id, fragment_num;
521 node_id fragment_num
522 1 0
523 1 1
524 1 2
525 1 3
526 1 4
527 2 0
528 2 1
529 2 2
530 2 3
531 2 4
532 select count(*) as cnt from ndbinfo.memory_per_fragment
533 where fq_name = 'test/def/t3' group by node_id, block_instance order by cnt desc;
534 cnt
535 2
536 2
537 1
538 1
539 1
540 1
541 1
542 1
543 alter table t4 algorithm=inplace, add partition partitions 1;
544 select node_id, fragment_num from ndbinfo.memory_per_fragment
545 where fq_name = 'test/def/t4' order by node_id, fragment_num;
546 node_id fragment_num
547 1 0
548 1 1
549 1 2
550 2 0
551 2 1
552 2 2
553 select count(*) as cnt from ndbinfo.memory_per_fragment
554 where fq_name = 'test/def/t4' group by node_id, block_instance order by cnt desc;
555 cnt
556 1
557 1
558 1
559 1
560 1
561 1
562 alter table t5 algorithm=inplace, add partition partitions 1;
563 select node_id, fragment_num from ndbinfo.memory_per_fragment
564 where fq_name = 'test/def/t5' order by node_id, fragment_num;
565 node_id fragment_num
566 1 0
567 1 1
568 2 0
569 2 1
570 select count(*) as cnt from ndbinfo.memory_per_fragment
571 where fq_name = 'test/def/t5' group by node_id, block_instance order by cnt desc;
572 cnt
573 1
574 1
575 1
576 1
577 select count(*) into @cnt_t1_2
578 from information_schema.partitions
579 where table_schema = 'test' and table_name = 't1';
580 select count(*) into @cnt_t2_2
581 from information_schema.partitions
582 where table_schema = 'test' and table_name = 't2';
583 select count(*) into @cnt_t3_2
584 from information_schema.partitions
585 where table_schema = 'test' and table_name = 't3';
586 select count(*) into @cnt_t4_2
587 from information_schema.partitions
588 where table_schema = 'test' and table_name = 't4';
589 select count(*) into @cnt_t5_2
590 from information_schema.partitions
591 where table_schema = 'test' and table_name = 't5';
592 select @cnt_t1_2;
593 @cnt_t1_2
594 9
595 select @cnt_t2_2;
596 @cnt_t2_2
597 9
598 select @cnt_t3_2;
599 @cnt_t3_2
600 5
601 select @cnt_t4_2;
602 @cnt_t4_2
603 3
604 select @cnt_t5_2;
605 @cnt_t5_2
606 2
607 select @cnt_t1_0 < @cnt_t1_2;
608 @cnt_t1_0 < @cnt_t1_2
609 1
610 select @cnt_t2_0 < @cnt_t2_2;
611 @cnt_t2_0 < @cnt_t2_2
612 1
613 select @cnt_t3_0 < @cnt_t3_2;
614 @cnt_t3_0 < @cnt_t3_2
615 1
616 select @cnt_t4_0 < @cnt_t4_2;
617 @cnt_t4_0 < @cnt_t4_2
618 1
619 select @cnt_t5_0 < @cnt_t5_2;
620 @cnt_t5_0 < @cnt_t5_2
621 1
622 drop table t1, t2, t3, t4, t5;
623 create table t1 (a int primary key) engine ndb
624 COMMENT="NDB_TABLE=NOLOGGING=1";
625 alter table t1 algorithm=inplace,
626 COMMENT="NDB_TABLE=NOLOGGING=0";
627 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter NOLOGGING inplace. Try ALGORITHM=COPY.
628 drop table t1;
629 create table t1 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
630 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
631 alter table t1 algorithm=inplace,
632 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
633 select count(*) into @cnt_t1_3
634 from information_schema.partitions
635 where table_schema = 'test' and table_name = 't1';
636 select @cnt_t1_0 = @cnt_t1_3;
637 @cnt_t1_0 = @cnt_t1_3
638 1
639 select count(*) into @cnt_t1_4
640 from information_schema.partitions
641 where table_schema = 'test' and table_name = 't1';
642 select @cnt_t1_0 < @cnt_t1_4;
643 @cnt_t1_0 < @cnt_t1_4
644 0
645 drop table t1;
646 create table t1 (a bigint, b bigint, c bigint, d bigint, primary key (a,b,c,d))
647 engine=ndb comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
648 alter table t1 algorithm=inplace,
649 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
650 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding column(s) or add/reorganize partition not supported online. Try ALGORITHM=COPY.
651 alter table t1 algorithm=copy,
652 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE,NOLOGGING=1";
653 select node_id, fragment_num from ndbinfo.memory_per_fragment
654 where fq_name = 'test/def/t1' order by node_id, fragment_num;
655 node_id fragment_num
656 1 0
657 2 0
658 select count(*) as cnt from ndbinfo.memory_per_fragment
659 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
660 cnt
661 1
662 1
663 show create table t1;
664 Table Create Table
665 t1 CREATE TABLE `t1` (
666 `a` bigint NOT NULL,
667 `b` bigint NOT NULL,
668 `c` bigint NOT NULL,
669 `d` bigint NOT NULL,
670 PRIMARY KEY (`a`,`b`,`c`,`d`)
671 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE,NOLOGGING=1'
672 alter table t1 algorithm=inplace,
673 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_NODE";
674 select node_id, fragment_num from ndbinfo.memory_per_fragment
675 where fq_name = 'test/def/t1' order by node_id, fragment_num;
676 node_id fragment_num
677 1 0
678 1 1
679 2 0
680 2 1
681 select count(*) as cnt from ndbinfo.memory_per_fragment
682 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
683 cnt
684 1
685 1
686 1
687 1
688 select count(*) into @cnt_t1_5
689 from information_schema.partitions
690 where table_schema = 'test' and table_name = 't1';
691 select @cnt_t1_5;
692 @cnt_t1_5
693 2
694 show create table t1;
695 Table Create Table
696 t1 CREATE TABLE `t1` (
697 `a` bigint NOT NULL,
698 `b` bigint NOT NULL,
699 `c` bigint NOT NULL,
700 `d` bigint NOT NULL,
701 PRIMARY KEY (`a`,`b`,`c`,`d`)
702 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,PARTITION_BALANCE=FOR_RP_BY_NODE'
703 alter table t1 algorithm=inplace,
704 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
705 select node_id, fragment_num from ndbinfo.memory_per_fragment
706 where fq_name = 'test/def/t1' order by node_id, fragment_num;
707 node_id fragment_num
708 1 0
709 1 1
710 1 2
711 1 3
712 2 0
713 2 1
714 2 2
715 2 3
716 select count(*) as cnt from ndbinfo.memory_per_fragment
717 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
718 cnt
719 1
720 1
721 1
722 1
723 1
724 1
725 1
726 1
727 select count(*) into @cnt_t1_6
728 from information_schema.partitions
729 where table_schema = 'test' and table_name = 't1';
730 select @cnt_t1_6;
731 @cnt_t1_6
732 4
733 show create table t1;
734 Table Create Table
735 t1 CREATE TABLE `t1` (
736 `a` bigint NOT NULL,
737 `b` bigint NOT NULL,
738 `c` bigint NOT NULL,
739 `d` bigint NOT NULL,
740 PRIMARY KEY (`a`,`b`,`c`,`d`)
741 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,PARTITION_BALANCE=FOR_RA_BY_LDM'
742 alter table t1 algorithm=inplace,
743 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
744 select node_id, fragment_num from ndbinfo.memory_per_fragment
745 where fq_name = 'test/def/t1' order by node_id, fragment_num;
746 node_id fragment_num
747 1 0
748 1 1
749 1 2
750 1 3
751 1 4
752 1 5
753 1 6
754 1 7
755 2 0
756 2 1
757 2 2
758 2 3
759 2 4
760 2 5
761 2 6
762 2 7
763 select count(*) as cnt from ndbinfo.memory_per_fragment
764 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
765 cnt
766 2
767 2
768 2
769 2
770 2
771 2
772 2
773 2
774 select count(*) into @cnt_t1_7
775 from information_schema.partitions
776 where table_schema = 'test' and table_name = 't1';
777 select @cnt_t1_7;
778 @cnt_t1_7
779 8
780 show create table t1;
781 Table Create Table
782 t1 CREATE TABLE `t1` (
783 `a` bigint NOT NULL,
784 `b` bigint NOT NULL,
785 `c` bigint NOT NULL,
786 `d` bigint NOT NULL,
787 PRIMARY KEY (`a`,`b`,`c`,`d`)
788 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,PARTITION_BALANCE=FOR_RP_BY_LDM'
789 -- t1 --
790 Version: Any
791 Fragment type: HashMapPartition
792 K Value: 6
793 Min load factor: 78
794 Max load factor: 80
795 Temporary table: yes
796 Number of attributes: 4
797 Number of primary keys: 4
798 Length of frm data: XXX
799 Max Rows: 0
800 Row Checksum: 1
801 Row GCI: 1
802 SingleUserMode: 0
803 ForceVarPart: 1
804 PartitionCount: 8
805 FragmentCount: 8
806 PartitionBalance: FOR_RP_BY_LDM
807 ExtraRowGciBits: 0
808 ExtraRowAuthorBits: 0
809 TableStatus: Retrieved
810 Table options: readbackup
811 HashMap: DEFAULT-HASHMAP-3840-8
812 -- Attributes --
813 a Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
814 b Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
815 c Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
816 d Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
817 -- Indexes --
818 PRIMARY KEY(a, b, c, d) - UniqueHashIndex
819 PRIMARY(a, b, c, d) - OrderedIndex
820 drop table t1;
821 create table t1 (a bigint primary key, b bigint storage disk) engine ndb
822 comment="NDB_TABLE=NOLOGGING=1";
823 ERROR HY000: Table storage engine 'ndbcluster' does not support the create option 'NOLOGGING=1 on table with fields using STORAGE DISK'
824 show warnings;
825 Level Code Message
826 Error 1478 Table storage engine 'ndbcluster' does not support the create option 'NOLOGGING=1 on table with fields using STORAGE DISK'
827 create table t2 (a bigint primary key, b bigint unique) engine ndb
828 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
829 select node_id, fragment_num from ndbinfo.memory_per_fragment
830 where fq_name = 'test/def/t2' order by node_id, fragment_num;
831 node_id fragment_num
832 1 0
833 2 0
834 select count(*) as cnt from ndbinfo.memory_per_fragment
835 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
836 cnt
837 1
838 1
839 select node_id, fragment_num from ndbinfo.memory_per_fragment
840 where fq_name like '%unique' order by node_id, fragment_num;
841 node_id fragment_num
842 1 0
843 2 0
844 select count(*) as cnt from ndbinfo.memory_per_fragment
845 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
846 cnt
847 1
848 1
849 alter table t2 algorithm=copy,
850 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_NODE";
851 select node_id, fragment_num from ndbinfo.memory_per_fragment
852 where fq_name = 'test/def/t2' order by node_id, fragment_num;
853 node_id fragment_num
854 1 0
855 1 1
856 2 0
857 2 1
858 select count(*) as cnt from ndbinfo.memory_per_fragment
859 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
860 cnt
861 1
862 1
863 1
864 1
865 select node_id, fragment_num from ndbinfo.memory_per_fragment
866 where fq_name like '%unique' order by node_id, fragment_num;
867 node_id fragment_num
868 1 0
869 1 1
870 2 0
871 2 1
872 select count(*) as cnt from ndbinfo.memory_per_fragment
873 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
874 cnt
875 1
876 1
877 1
878 1
879 drop table t2;
880 create table t2 (a bigint primary key, b bigint unique) engine ndb
881 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
882 alter table t2 algorithm=inplace,
883 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_NODE";
884 select node_id, fragment_num from ndbinfo.memory_per_fragment
885 where fq_name = 'test/def/t2' order by node_id, fragment_num;
886 node_id fragment_num
887 1 0
888 1 1
889 2 0
890 2 1
891 select count(*) as cnt from ndbinfo.memory_per_fragment
892 where fq_name = 'test/def/t2' group by node_id, block_instance order by cnt desc;
893 cnt
894 1
895 1
896 1
897 1
898 select node_id, fragment_num from ndbinfo.memory_per_fragment
899 where fq_name like '%unique' order by node_id, fragment_num;
900 node_id fragment_num
901 1 0
902 1 1
903 2 0
904 2 1
905 select count(*) as cnt from ndbinfo.memory_per_fragment
906 where fq_name like '%unique' group by node_id, block_instance order by cnt desc;
907 cnt
908 1
909 1
910 1
911 1
912 drop table t2;
913 create table t1 (a bigint primary key, b bigint unique) engine ndb
914 comment="NDB_TABLE=READ_BACKUP=1";
915 show create table t1;
916 Table Create Table
917 t1 CREATE TABLE `t1` (
918 `a` bigint NOT NULL,
919 `b` bigint DEFAULT NULL,
920 PRIMARY KEY (`a`),
921 UNIQUE KEY `b` (`b`)
922 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=READ_BACKUP=1'
923 select node_id, fragment_num from ndbinfo.memory_per_fragment
924 where fq_name = 'test/def/t1' order by node_id, fragment_num;
925 node_id fragment_num
926 1 0
927 1 1
928 1 2
929 1 3
930 1 4
931 1 5
932 1 6
933 1 7
934 2 0
935 2 1
936 2 2
937 2 3
938 2 4
939 2 5
940 2 6
941 2 7
942 select count(*) as cnt from ndbinfo.memory_per_fragment
943 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
944 cnt
945 2
946 2
947 2
948 2
949 2
950 2
951 2
952 2
953 select node_id, fragment_num from ndbinfo.memory_per_fragment
954 where fq_name like '%unique' order by node_id, fragment_num;
955 node_id fragment_num
956 1 0
957 1 1
958 1 2
959 1 3
960 1 4
961 1 5
962 1 6
963 1 7
964 2 0
965 2 1
966 2 2
967 2 3
968 2 4
969 2 5
970 2 6
971 2 7
972 select count(*) as cnt from ndbinfo.memory_per_fragment
973 where fq_name like '%unique' group by node_id, block_instance order by cnt desc;
974 cnt
975 2
976 2
977 2
978 2
979 2
980 2
981 2
982 2
983 alter table t1 algorithm=inplace,
984 comment="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
985 show create table t1;
986 Table Create Table
987 t1 CREATE TABLE `t1` (
988 `a` bigint NOT NULL,
989 `b` bigint DEFAULT NULL,
990 PRIMARY KEY (`a`),
991 UNIQUE KEY `b` (`b`)
992 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=READ_BACKUP=1,PARTITION_BALANCE=FOR_RP_BY_LDM'
993 select node_id, fragment_num from ndbinfo.memory_per_fragment
994 where fq_name = 'test/def/t1' order by node_id, fragment_num;
995 node_id fragment_num
996 1 0
997 1 1
998 1 2
999 1 3
1000 1 4
1001 1 5
1002 1 6
1003 1 7
1004 2 0
1005 2 1
1006 2 2
1007 2 3
1008 2 4
1009 2 5
1010 2 6
1011 2 7
1012 select count(*) as cnt from ndbinfo.memory_per_fragment
1013 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
1014 cnt
1015 2
1016 2
1017 2
1018 2
1019 2
1020 2
1021 2
1022 2
1023 select node_id, fragment_num from ndbinfo.memory_per_fragment
1024 where fq_name like '%unique' order by node_id, fragment_num;
1025 node_id fragment_num
1026 1 0
1027 1 1
1028 1 2
1029 1 3
1030 1 4
1031 1 5
1032 1 6
1033 1 7
1034 2 0
1035 2 1
1036 2 2
1037 2 3
1038 2 4
1039 2 5
1040 2 6
1041 2 7
1042 select count(*) as cnt from ndbinfo.memory_per_fragment
1043 where fq_name like '%unique' group by node_id, block_instance order by cnt desc;
1044 cnt
1045 2
1046 2
1047 2
1048 2
1049 2
1050 2
1051 2
1052 2
1053 drop table t1;
1054 create table t1 (a bigint primary key, b bigint unique) engine ndb;
1055 select node_id, fragment_num from ndbinfo.memory_per_fragment
1056 where fq_name = 'test/def/t1' order by node_id, fragment_num;
1057 node_id fragment_num
1058 1 0
1059 1 1
1060 1 2
1061 1 3
1062 1 4
1063 1 5
1064 1 6
1065 1 7
1066 2 0
1067 2 1
1068 2 2
1069 2 3
1070 2 4
1071 2 5
1072 2 6
1073 2 7
1074 select count(*) as cnt from ndbinfo.memory_per_fragment
1075 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
1076 cnt
1077 2
1078 2
1079 2
1080 2
1081 2
1082 2
1083 2
1084 2
1085 alter table t1 algorithm=copy,
1086 COMMENT="NDB_TABLE=READ_BACKUP=1";
1087 select node_id, fragment_num from ndbinfo.memory_per_fragment
1088 where fq_name = 'test/def/t1' order by node_id, fragment_num;
1089 node_id fragment_num
1090 1 0
1091 1 1
1092 1 2
1093 1 3
1094 1 4
1095 1 5
1096 1 6
1097 1 7
1098 2 0
1099 2 1
1100 2 2
1101 2 3
1102 2 4
1103 2 5
1104 2 6
1105 2 7
1106 select count(*) as cnt from ndbinfo.memory_per_fragment
1107 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
1108 cnt
1109 2
1110 2
1111 2
1112 2
1113 2
1114 2
1115 2
1116 2
1117 drop table t1;
1118 create table t1 (a bigint primary key) engine ndb
1119 partition by key();
1120 select node_id, fragment_num from ndbinfo.memory_per_fragment
1121 where fq_name = 'test/def/t1' order by node_id, fragment_num;
1122 node_id fragment_num
1123 1 0
1124 1 1
1125 1 2
1126 1 3
1127 1 4
1128 1 5
1129 1 6
1130 1 7
1131 2 0
1132 2 1
1133 2 2
1134 2 3
1135 2 4
1136 2 5
1137 2 6
1138 2 7
1139 select count(*) as cnt from ndbinfo.memory_per_fragment
1140 where fq_name = 'test/def/t1' group by node_id, block_instance order by cnt desc;
1141 cnt
1142 2
1143 2
1144 2
1145 2
1146 2
1147 2
1148 2
1149 2
1150 drop table t1;
1151 create table t1 (a bigint primary key) engine ndb
1152 comment="NDB_TABLE=READ_BACKUP=0"
1153 partition by key();
1154 alter table t1 algorithm=inplace, comment="NDB_TABLE=READ_BACKUP=1";
1155 alter table t1 algorithm=inplace, comment="NDB_TABLE=READ_BACKUP=1";
1156 alter table t1 algorithm=inplace, comment="NDB_TABLE=READ_BACKUP=0";
1157 alter table t1 algorithm=inplace, comment="NDB_TABLE=READ_BACKUP=0";
1158 drop table t1;
1159 set @@global.ndb_read_backup = @save_ndb_read_backup;