Debian Codegraph @

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

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