Debian Codegraph @

mysql-test/suite/ndb/t/ndb_add_partition.test

raw
1 # This test has not yet been adapted to wl#11968, pending fix to bug#33739957
2
3 --source include/have_multi_ndb.inc
4
5 --disable_warnings
6 DROP TABLE IF EXISTS t1,t2;
7 --enable_warnings
8
9 CREATE LOGFILE GROUP lg1
10 ADD UNDOFILE 'undofile.dat'
11 INITIAL_SIZE 16M
12 UNDO_BUFFER_SIZE = 1M ENGINE=NDB;
13
14 CREATE TABLESPACE ts1
15 ADD DATAFILE 'datafile.dat'
16 USE LOGFILE GROUP lg1
17 INITIAL_SIZE 64M
18 ENGINE NDB;
19
20 CREATE TABLE t1 (a int unsigned not null,
21 b int unsigned not null,
22 c int unsigned not null,
23 primary key(a,c),
24 unique (b))
25 ENGINE = NDB
26 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
27 partition by key(a);
28
29 let $ndb_desc_opts= -b -i -d test t1;
30 source suite/ndb/include/ndb_desc_print.inc;
31
32 CREATE TABLE t2 (a int unsigned not null,
33 b int unsigned not null,
34 c int unsigned not null,
35 primary key(a,b),
36 unique (b))
37 ENGINE = NDB
38 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM"
39 STORAGE DISK
40 TABLESPACE ts1
41 partition by key(a);
42
43 let $ndb_desc_opts= -b -i -d test t2;
44 source suite/ndb/include/ndb_desc_print.inc;
45
46 CREATE TABLE t3 (a int unsigned not null,
47 b int unsigned not null,
48 c int unsigned not null,
49 primary key(a,b),
50 unique (b))
51 MAX_ROWS=50000000
52 ENGINE = NDB;
53
54 let $ndb_desc_opts= -b -i -d test t3;
55 source suite/ndb/include/ndb_desc_print.inc;
56
57 CREATE TABLE t4 (a int unsigned not null,
58 b int unsigned not null,
59 c int unsigned not null,
60 primary key(a,b),
61 unique (b))
62 ENGINE = NDB
63 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
64
65 CREATE TABLE t5 (a int unsigned not null,
66 b int unsigned not null,
67 c int unsigned null,
68 primary key(a,b),
69 unique (b),
70 unique (c))
71 ENGINE = NDB
72 partition by key(a) partitions 3;
73
74 # Fragment counts for unique index must be equal to main table
75 # if main table is hashmap partitioned
76
77 let ndb_database=test;
78 let ndb_table=t1;
79 --source suite/ndb/include/ndb_check_unique_index.inc
80
81 let ndb_database=test;
82 let ndb_table=t2;
83 --source suite/ndb/include/ndb_check_unique_index.inc
84
85 let ndb_database=test;
86 let ndb_table=t3;
87 --source suite/ndb/include/ndb_check_unique_index.inc
88
89 let ndb_database=test;
90 let ndb_table=t4;
91 --source suite/ndb/include/ndb_check_unique_index.inc
92
93 let ndb_database=test;
94 let ndb_table=t5;
95 --source suite/ndb/include/ndb_check_unique_index.inc
96
97 #
98
99 let $t1_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1);
100
101 let $t2_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1);
102
103 let $t3_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1);
104
105 let $t4_part_count_start = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1);
106
107 INSERT INTO t1 VALUES
108 (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
109 (6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
110 (11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
111 (16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
112 (21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
113 (26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
114 (31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
115 (36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
116 (41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
117 (46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
118 (51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
119 (56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
120 (61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
121 (66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
122 (71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75),
123 (76,76,76),(77,77,77),(78,78,78),(79,79,79),(80,80,80),
124 (81,81,81),(82,82,82),(83,83,83),(84,84,84),(85,85,85),
125 (86,86,86),(87,87,87),(88,88,88),(89,89,89),(90,90,90),
126 (91,91,91),(92,92,92),(93,93,93),(94,94,94),(95,95,95),
127 (96,96,96),(97,97,97),(98,98,98),(99,99,99),(100,100,100);
128
129 insert into t2 select * from t1;
130 insert into t3 select * from t1;
131 insert into t4 select * from t1;
132 insert into t5 (a,b,c) select a,b,if(a%2,b,NULL) as c from t1;
133
134 select count(*) from t1;
135 select count(*) from t2;
136 select count(*) from t3;
137 select count(*) from t4;
138 select count(*) from t5;
139 --sorted_result
140 select * from t1 where a < 20;
141 --sorted_result
142 select * from t1 where a = 20;
143 --sorted_result
144 select * from t1 where a = 30;
145 --sorted_result
146 select * from t1 where a = 40;
147 --sorted_result
148 select * from t1 where a = 50;
149 select * from t1 where b = 20;
150 select * from t1 where b = 30;
151 select * from t1 where b = 40;
152 select * from t1 where b = 50;
153
154 --sorted_result
155 select * from t2 where a < 20;
156 --sorted_result
157 select * from t2 where a = 20;
158 --sorted_result
159 select * from t2 where a = 30;
160 --sorted_result
161 select * from t2 where a = 40;
162 --sorted_result
163 select * from t2 where a = 50;
164 select * from t2 where b = 20;
165 select * from t2 where b = 30;
166 select * from t2 where b = 40;
167 select * from t2 where b = 50;
168
169 --sorted_result
170 select * from t3 where a < 20;
171 --sorted_result
172 select * from t3 where a = 20;
173 --sorted_result
174 select * from t3 where a = 30;
175 --sorted_result
176 select * from t3 where a = 40;
177 --sorted_result
178 select * from t3 where a = 50;
179 select * from t3 where b = 20;
180 select * from t3 where b = 30;
181 select * from t3 where b = 40;
182 select * from t3 where b = 50;
183
184 --sorted_result
185 select * from t4 where a < 20;
186 --sorted_result
187 select * from t4 where a = 20;
188 --sorted_result
189 select * from t4 where a = 30;
190 --sorted_result
191 select * from t4 where a = 40;
192 --sorted_result
193 select * from t4 where a = 50;
194 select * from t4 where b = 20;
195 select * from t4 where b = 30;
196 select * from t4 where b = 40;
197 select * from t4 where b = 50;
198
199 alter table t1 algorithm=inplace, reorganize partition;
200
201 let $ndb_desc_opts= -b -i -d test t1;
202 source suite/ndb/include/ndb_desc_print.inc;
203
204 alter table t2 algorithm=inplace, reorganize partition;
205 let $ndb_desc_opts= -b -i -d test t2;
206 source suite/ndb/include/ndb_desc_print.inc;
207
208 --echo Cannot use inplace reorganize partition on t3 as it has explicit MAX_ROWS set
209 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
210 alter table t3 algorithm=inplace, reorganize partition;
211 show warnings;
212
213 alter table t3 algorithm=inplace, max_rows=50000000;
214 let $ndb_desc_opts= -b -i -d test t3;
215 source suite/ndb/include/ndb_desc_print.inc;
216
217 alter table t4 algorithm=inplace, reorganize partition;
218
219 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
220 alter table t5 algorithm=inplace partition by key() partitions 6;
221
222 let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1);
223
224 let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1);
225
226 let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1);
227
228 let $t4_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1);
229
230 --disable_query_log
231 --echo Check partitions added, expect 0 in all cases
232 --echo partitions added to t1
233 eval select $t1_part_count_now - $t1_part_count_start as t1_added;
234 --echo partitions added to t2
235 eval select $t2_part_count_now - $t2_part_count_start as t2_added;
236 --echo partitions added to t3
237 eval select $t3_part_count_now - $t3_part_count_start as t3_added;
238 --echo partitions added to t4
239 eval select $t4_part_count_now - $t4_part_count_start as t4_added;
240 --enable_query_log
241
242 alter table t1 algorithm=inplace, add partition partitions 1;
243
244 let $ndb_desc_opts= -b -i -d test t1;
245 source suite/ndb/include/ndb_desc_print.inc;
246
247 alter table t2 algorithm=inplace, add partition partitions 4;
248 let $ndb_desc_opts= -b -i -d test t2;
249 source suite/ndb/include/ndb_desc_print.inc;
250
251 alter table t3 algorithm=inplace, max_rows=100000000; # Expansion of max rows
252 let $ndb_desc_opts= -b -i -d test t3;
253 source suite/ndb/include/ndb_desc_print.inc;
254
255 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
256 alter table t4 algorithm=inplace, max_rows=100000000; # Attempted introduction of max rows - fails
257
258 let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1);
259
260 let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1);
261
262 let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1);
263
264 let $t4_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't4', Value, 1);
265
266 --disable_query_log
267 --echo partitions added to t1 (expect 1)
268 eval select $t1_part_count_now - $t1_part_count_start as t1_added;
269 --echo partitions added to t2 (expect 4)
270 eval select $t2_part_count_now - $t2_part_count_start as t2_added;
271 --echo partitions added to t3 (expect 4)
272 eval select $t3_part_count_now - $t3_part_count_start as t3_added;
273 --echo partitions added to t4 (expect 0)
274 eval select $t4_part_count_now - $t4_part_count_start as t4_added;
275 --enable_query_log
276
277 # reorganize partition not support if not default partitioning
278 # and after a add partition it's no longer default
279 --error ER_REORG_NO_PARAM_ERROR
280 alter table t1 algorithm=inplace, reorganize partition;
281
282 # Following will fail as t3 has an explicit MAX_ROWS set
283 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
284 alter table t3 algorithm=inplace, reorganize partition;
285 show warnings;
286
287 # t4 reorg will succeed as t4 has no explicit MAX_ROWS
288 alter table t4 algorithm=inplace, reorganize partition;
289
290 select count(*) from t1;
291 select count(*) from t2;
292 --sorted_result
293 select * from t1 where a < 20;
294 --sorted_result
295 select * from t1 where a = 20;
296 --sorted_result
297 select * from t1 where a = 30;
298 --sorted_result
299 select * from t1 where a = 40;
300 --sorted_result
301 select * from t1 where a = 50;
302 select * from t1 where b = 20;
303 select * from t1 where b = 30;
304 select * from t1 where b = 40;
305 select * from t1 where b = 50;
306
307 --sorted_result
308 select * from t2 where a < 20;
309 --sorted_result
310 select * from t2 where a = 20;
311 --sorted_result
312 select * from t2 where a = 30;
313 --sorted_result
314 select * from t2 where a = 40;
315 --sorted_result
316 select * from t2 where a = 50;
317 select * from t2 where b = 20;
318 select * from t2 where b = 30;
319 select * from t2 where b = 40;
320 select * from t2 where b = 50;
321
322 --sorted_result
323 select * from t3 where a < 20;
324 --sorted_result
325 select * from t3 where a = 20;
326 --sorted_result
327 select * from t3 where a = 30;
328 --sorted_result
329 select * from t3 where a = 40;
330 --sorted_result
331 select * from t3 where a = 50;
332 select * from t3 where b = 20;
333 select * from t3 where b = 30;
334 select * from t3 where b = 40;
335 select * from t3 where b = 50;
336
337 --sorted_result
338 select * from t4 where a < 20;
339 --sorted_result
340 select * from t4 where a = 20;
341 --sorted_result
342 select * from t4 where a = 30;
343 --sorted_result
344 select * from t4 where a = 40;
345 --sorted_result
346 select * from t4 where a = 50;
347 select * from t4 where b = 20;
348 select * from t4 where b = 30;
349 select * from t4 where b = 40;
350 select * from t4 where b = 50;
351
352 let ndb_database=test;
353 let ndb_table=t4;
354 --source suite/ndb/include/ndb_check_unique_index.inc
355
356 drop table t4;
357
358 alter table t1 algorithm=inplace, add partition partitions 2;
359 let $ndb_desc_opts= -b -i -d test t1;
360 source suite/ndb/include/ndb_desc_print.inc;
361
362 alter table t2 algorithm=inplace, add partition partitions 1;
363 let $ndb_desc_opts= -b -i -d test t2;
364 source suite/ndb/include/ndb_desc_print.inc;
365
366 alter table t3 algorithm=inplace, max_rows=150000000;
367 let $ndb_desc_opts= -b -i -d test t3;
368 source suite/ndb/include/ndb_desc_print.inc;
369
370
371 let $t1_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't1', Value, 1);
372
373 let $t2_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't2', Value, 1);
374
375 let $t3_part_count_now = query_get_value(select count(*) as Value from information_schema.partitions where table_schema = 'test' and table_name = 't3', Value, 1);
376
377 --disable_query_log
378 --echo partitions added to t1 (expect 3)
379 eval select $t1_part_count_now - $t1_part_count_start as t1_added;
380 --echo partitions added to t2 (expect 5)
381 eval select $t2_part_count_now - $t2_part_count_start as t2_added;
382 --echo partitions added to t3 (expect 4)
383 eval select $t3_part_count_now - $t3_part_count_start as t3_added;
384 --enable_query_log
385
386 select count(*) from t1;
387 select count(*) from t2;
388 --sorted_result
389 select * from t1 where a < 20;
390 --sorted_result
391 select * from t1 where a = 20;
392 --sorted_result
393 select * from t1 where a = 30;
394 --sorted_result
395 select * from t1 where a = 40;
396 --sorted_result
397 select * from t1 where a = 50;
398 select * from t1 where b = 20;
399 select * from t1 where b = 30;
400 select * from t1 where b = 40;
401 select * from t1 where b = 50;
402
403 --sorted_result
404 select * from t2 where a < 20;
405 --sorted_result
406 select * from t2 where a = 20;
407 --sorted_result
408 select * from t2 where a = 30;
409 --sorted_result
410 select * from t2 where a = 40;
411 --sorted_result
412 select * from t2 where a = 50;
413 select * from t2 where b = 20;
414 select * from t2 where b = 30;
415 select * from t2 where b = 40;
416 select * from t2 where b = 50;
417
418 --sorted_result
419 select * from t3 where a < 20;
420 --sorted_result
421 select * from t3 where a = 20;
422 --sorted_result
423 select * from t3 where a = 30;
424 --sorted_result
425 select * from t3 where a = 40;
426 --sorted_result
427 select * from t3 where a = 50;
428 select * from t3 where b = 20;
429 select * from t3 where b = 30;
430 select * from t3 where b = 40;
431 select * from t3 where b = 50;
432
433 # Fragment counts for unique index may now differ from main table
434 # since unique index do not reorg
435
436 let ndb_database=test;
437 let ndb_table=t1;
438 --source suite/ndb/include/ndb_check_unique_index.inc
439
440 let ndb_database=test;
441 let ndb_table=t2;
442 --source suite/ndb/include/ndb_check_unique_index.inc
443
444 let ndb_database=test;
445 let ndb_table=t3;
446 --source suite/ndb/include/ndb_check_unique_index.inc
447
448 let ndb_database=test;
449 let ndb_table=t5;
450 --source suite/ndb/include/ndb_check_unique_index.inc
451
452 alter table t5 algorithm=inplace, add partition partitions 1;
453 let $ndb_desc_opts= -b -i -d test t5;
454 source suite/ndb/include/ndb_desc_print.inc;
455
456 drop table t1,t2,t3,t5;
457
458 --echo Bug 33523991 Ndb online reorg : Scan count verification
459 --echo Scan count issue on complete phase of reorg
460
461 create table t1 (a int primary key, b int, c int, d int, unique(b), unique(c)) engine=ndb;
462
463 --echo Populate some data
464 insert into t1 values (1,1,1,1);
465 insert into t1 select a+1, b+1, c+1, d+1 from t1;
466 insert into t1 select a+2, b+2, c+2, d+2 from t1;
467 insert into t1 select a+4, b+4, c+4, d+4 from t1;
468 insert into t1 select a+8, b+8, c+8, d+8 from t1;
469 insert into t1 select a+16, b+16, c+16, d+16 from t1;
470 insert into t1 select a+32, b+32, c+32, d+32 from t1;
471 insert into t1 select a+64, b+64, c+64, d+64 from t1;
472 insert into t1 select a+128, b+128, c+128, d+128 from t1;
473 insert into t1 select a+256, b+256, c+256, d+256 from t1;
474 insert into t1 select a+512, b+512, c+512, d+512 from t1;
475 insert into t1 select a+1024, b+1024, c+1024, d+1024 from t1;
476 insert into t1 select a+2048, b+2048, c+2048, d+2048 from t1;
477
478 --connection server2
479
480 delimiter %;
481 create procedure scans (max_iterations int)
482 sp: begin
483 DECLARE sum_d int;
484 set @x=0;
485 repeat
486 select sum(d) into sum_d from t1;
487 set @x = @x + 1;
488 if sum_d = 0
489 then
490 leave sp;
491 end if;
492 until @x=max_iterations
493 end repeat;
494 end%
495
496 delimiter ;%
497
498 --send call scans(20000);
499
500 --connection server1
501
502 alter table t1 algorithm=inplace, add partition partitions 1;
503
504 --echo Alter ok, run again
505
506 alter table t1 algorithm=inplace, add partition partitions 1;
507
508 --echo Second alter ok, finish scans
509 select sum(d) into @sum_d from t1;
510 insert into t1 values (20000,20000,20000, 0 - @sum_d);
511
512 --connection server2
513 --reap
514
515 drop procedure scans;
516
517 --connection server1
518 drop table t1;
519
520 alter tablespace ts1 drop datafile 'datafile.dat';
521 drop tablespace ts1;
522 drop logfile group lg1 engine = ndb;