Debian Codegraph @

mysql-test/suite/parts/r/partition_alter3_innodb.result

raw
1 SET @max_row = 20;
2 SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
3 SET GLOBAL innodb_stats_persistent=0;
4 SET @@session.default_storage_engine = 'InnoDB';
5
6 #------------------------------------------------------------------------
7 # 0. Setting of auxiliary variables + Creation of an auxiliary tables
8 # needed in many testcases
9 #------------------------------------------------------------------------
10 SELECT @max_row DIV 2 INTO @max_row_div2;
11 SELECT @max_row DIV 3 INTO @max_row_div3;
12 SELECT @max_row DIV 4 INTO @max_row_div4;
13 SET @max_int_4 = 2147483647;
14 DROP TABLE IF EXISTS t0_template;
15 CREATE TABLE t0_template (
16 f_int1 INTEGER DEFAULT 0,
17 f_int2 INTEGER DEFAULT 0,
18 f_char1 CHAR(20),
19 f_char2 CHAR(20),
20 f_charbig VARCHAR(1000) ,
21 PRIMARY KEY(f_int1))
22 ENGINE = MEMORY;
23 # Logging of <max_row> INSERTs into t0_template suppressed
24 DROP TABLE IF EXISTS t0_definition;
25 CREATE TABLE t0_definition (
26 state CHAR(3),
27 create_command VARBINARY(5000),
28 file_list VARBINARY(10000),
29 PRIMARY KEY (state)
30 ) ENGINE = MEMORY;
31 DROP TABLE IF EXISTS t0_aux;
32 CREATE TABLE t0_aux ( f_int1 INTEGER DEFAULT 0,
33 f_int2 INTEGER DEFAULT 0,
34 f_char1 CHAR(20),
35 f_char2 CHAR(20),
36 f_charbig VARCHAR(1000) )
37 ENGINE = MEMORY;
38 SET AUTOCOMMIT= 1;
39 SET @@session.sql_mode= '';
40 # End of basic preparations needed for all tests
41 #-----------------------------------------------
42
43 #========================================================================
44 # 1. Partition management commands on HASH partitioned table
45 # column in partitioning function is of type DATE
46 #========================================================================
47 DROP TABLE IF EXISTS t1;
48 CREATE TABLE t1 (f_date DATE, f_varchar VARCHAR(30));
49 INSERT INTO t1 (f_date, f_varchar)
50 SELECT CONCAT(CAST((f_int1 + 999) AS CHAR),'-02-10'), CAST(f_char1 AS CHAR)
51 FROM t0_template
52 WHERE f_int1 + 999 BETWEEN 1000 AND 9999;
53 SELECT IF(9999 - 1000 + 1 > @max_row, @max_row , 9999 - 1000 + 1)
54 INTO @exp_row_count;
55 SHOW CREATE TABLE t1;
56 Table Create Table
57 t1 CREATE TABLE `t1` (
58 `f_date` date DEFAULT NULL,
59 `f_varchar` varchar(30) DEFAULT NULL
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
61 t1.frm
62 t1.ibd
63 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
64 id select_type table partitions type possible_keys key key_len ref rows Extra
65 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
66 # check read single success: 1
67 # check read all success: 1
68 # check read row by row success: 1
69 #------------------------------------------------------------------------
70 # 1.1 Increase number of PARTITIONS
71 #------------------------------------------------------------------------
72 # 1.1.1 ADD PARTITION to not partitioned table --> must fail
73 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
74 ERROR HY000: Partition management on a not partitioned table is not possible
75 # 1.1.2 Assign HASH partitioning
76 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
77 SHOW CREATE TABLE t1;
78 Table Create Table
79 t1 CREATE TABLE `t1` (
80 `f_date` date DEFAULT NULL,
81 `f_varchar` varchar(30) DEFAULT NULL
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
83 PARTITION BY HASH (year(`f_date`))
84 t1#P#p0.ibd
85 t1.frm
86 t1.par
87 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
88 id select_type table partitions type possible_keys key key_len ref rows Extra
89 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
90 # check read single success: 1
91 # check read all success: 1
92 # check read row by row success: 1
93 # 1.1.3 Assign other HASH partitioning to already partitioned table
94 # + test and switch back + test
95 ALTER TABLE t1 PARTITION BY HASH(DAYOFYEAR(f_date));
96 SHOW CREATE TABLE t1;
97 Table Create Table
98 t1 CREATE TABLE `t1` (
99 `f_date` date DEFAULT NULL,
100 `f_varchar` varchar(30) DEFAULT NULL
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
102 PARTITION BY HASH (dayofyear(`f_date`))
103 t1#P#p0.ibd
104 t1.frm
105 t1.par
106 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
107 id select_type table partitions type possible_keys key key_len ref rows Extra
108 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
109 # check read single success: 1
110 # check read all success: 1
111 # check read row by row success: 1
112 ALTER TABLE t1 PARTITION BY HASH(YEAR(f_date));
113 SHOW CREATE TABLE t1;
114 Table Create Table
115 t1 CREATE TABLE `t1` (
116 `f_date` date DEFAULT NULL,
117 `f_varchar` varchar(30) DEFAULT NULL
118 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
119 PARTITION BY HASH (year(`f_date`))
120 t1#P#p0.ibd
121 t1.frm
122 t1.par
123 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
124 id select_type table partitions type possible_keys key key_len ref rows Extra
125 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
126 # check read single success: 1
127 # check read all success: 1
128 # check read row by row success: 1
129 # 1.1.4 Add PARTITIONS not fitting to HASH --> must fail
130 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
131 ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
132 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
133 ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
134 # 1.1.5 Add two named partitions + test
135 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
136 SHOW CREATE TABLE t1;
137 Table Create Table
138 t1 CREATE TABLE `t1` (
139 `f_date` date DEFAULT NULL,
140 `f_varchar` varchar(30) DEFAULT NULL
141 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
142 PARTITION BY HASH (year(`f_date`))
143 (PARTITION `p0` ENGINE = InnoDB,
144 PARTITION `part1` ENGINE = InnoDB,
145 PARTITION `part7` ENGINE = InnoDB)
146 t1#P#p0.ibd
147 t1#P#part1.ibd
148 t1#P#part7.ibd
149 t1.frm
150 t1.par
151 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
152 id select_type table partitions type possible_keys key key_len ref rows Extra
153 1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
154 # check read single success: 1
155 # check read all success: 1
156 # check read row by row success: 1
157 # 1.1.6 Add two named partitions, name clash --> must fail
158 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
159 ERROR HY000: Duplicate partition name part1
160 # 1.1.7 Add one named partition + test
161 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
162 SHOW CREATE TABLE t1;
163 Table Create Table
164 t1 CREATE TABLE `t1` (
165 `f_date` date DEFAULT NULL,
166 `f_varchar` varchar(30) DEFAULT NULL
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
168 PARTITION BY HASH (year(`f_date`))
169 (PARTITION `p0` ENGINE = InnoDB,
170 PARTITION `part1` ENGINE = InnoDB,
171 PARTITION `part7` ENGINE = InnoDB,
172 PARTITION `part2` ENGINE = InnoDB)
173 t1#P#p0.ibd
174 t1#P#part1.ibd
175 t1#P#part2.ibd
176 t1#P#part7.ibd
177 t1.frm
178 t1.par
179 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
180 id select_type table partitions type possible_keys key key_len ref rows Extra
181 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
182 # check read single success: 1
183 # check read all success: 1
184 # check read row by row success: 1
185 # 1.1.8 Add four not named partitions + test
186 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
187 SHOW CREATE TABLE t1;
188 Table Create Table
189 t1 CREATE TABLE `t1` (
190 `f_date` date DEFAULT NULL,
191 `f_varchar` varchar(30) DEFAULT NULL
192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
193 PARTITION BY HASH (year(`f_date`))
194 (PARTITION `p0` ENGINE = InnoDB,
195 PARTITION `part1` ENGINE = InnoDB,
196 PARTITION `part7` ENGINE = InnoDB,
197 PARTITION `part2` ENGINE = InnoDB,
198 PARTITION `p4` ENGINE = InnoDB,
199 PARTITION `p5` ENGINE = InnoDB,
200 PARTITION `p6` ENGINE = InnoDB,
201 PARTITION `p7` ENGINE = InnoDB)
202 t1#P#p0.ibd
203 t1#P#p4.ibd
204 t1#P#p5.ibd
205 t1#P#p6.ibd
206 t1#P#p7.ibd
207 t1#P#part1.ibd
208 t1#P#part2.ibd
209 t1#P#part7.ibd
210 t1.frm
211 t1.par
212 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
213 id select_type table partitions type possible_keys key key_len ref rows Extra
214 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
215 # check read single success: 1
216 # check read all success: 1
217 # check read row by row success: 1
218 #------------------------------------------------------------------------
219 # 1.2 Decrease number of PARTITIONS
220 #------------------------------------------------------------------------
221 # 1.2.1 DROP PARTITION is not supported for HASH --> must fail
222 ALTER TABLE t1 DROP PARTITION part1;
223 ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
224 # 1.2.2 COALESCE PARTITION partitionname is not supported
225 ALTER TABLE t1 COALESCE PARTITION part1;
226 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'part1' at line 1
227 # 1.2.3 Decrease by 0 is non sense --> must fail
228 ALTER TABLE t1 COALESCE PARTITION 0;
229 ERROR HY000: At least one partition must be coalesced
230 # 1.2.4 COALESCE one partition + test loop
231 ALTER TABLE t1 COALESCE PARTITION 1;
232 SHOW CREATE TABLE t1;
233 Table Create Table
234 t1 CREATE TABLE `t1` (
235 `f_date` date DEFAULT NULL,
236 `f_varchar` varchar(30) DEFAULT NULL
237 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
238 PARTITION BY HASH (year(`f_date`))
239 (PARTITION `p0` ENGINE = InnoDB,
240 PARTITION `part1` ENGINE = InnoDB,
241 PARTITION `part7` ENGINE = InnoDB,
242 PARTITION `part2` ENGINE = InnoDB,
243 PARTITION `p4` ENGINE = InnoDB,
244 PARTITION `p5` ENGINE = InnoDB,
245 PARTITION `p6` ENGINE = InnoDB)
246 t1#P#p0.ibd
247 t1#P#p4.ibd
248 t1#P#p5.ibd
249 t1#P#p6.ibd
250 t1#P#part1.ibd
251 t1#P#part2.ibd
252 t1#P#part7.ibd
253 t1.frm
254 t1.par
255 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
256 id select_type table partitions type possible_keys key key_len ref rows Extra
257 1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
258 # check read single success: 1
259 # check read all success: 1
260 # check read row by row success: 1
261 ALTER TABLE t1 COALESCE PARTITION 1;
262 SHOW CREATE TABLE t1;
263 Table Create Table
264 t1 CREATE TABLE `t1` (
265 `f_date` date DEFAULT NULL,
266 `f_varchar` varchar(30) DEFAULT NULL
267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
268 PARTITION BY HASH (year(`f_date`))
269 (PARTITION `p0` ENGINE = InnoDB,
270 PARTITION `part1` ENGINE = InnoDB,
271 PARTITION `part7` ENGINE = InnoDB,
272 PARTITION `part2` ENGINE = InnoDB,
273 PARTITION `p4` ENGINE = InnoDB,
274 PARTITION `p5` ENGINE = InnoDB)
275 t1#P#p0.ibd
276 t1#P#p4.ibd
277 t1#P#p5.ibd
278 t1#P#part1.ibd
279 t1#P#part2.ibd
280 t1#P#part7.ibd
281 t1.frm
282 t1.par
283 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
284 id select_type table partitions type possible_keys key key_len ref rows Extra
285 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
286 # check read single success: 1
287 # check read all success: 1
288 # check read row by row success: 1
289 ALTER TABLE t1 COALESCE PARTITION 1;
290 SHOW CREATE TABLE t1;
291 Table Create Table
292 t1 CREATE TABLE `t1` (
293 `f_date` date DEFAULT NULL,
294 `f_varchar` varchar(30) DEFAULT NULL
295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
296 PARTITION BY HASH (year(`f_date`))
297 (PARTITION `p0` ENGINE = InnoDB,
298 PARTITION `part1` ENGINE = InnoDB,
299 PARTITION `part7` ENGINE = InnoDB,
300 PARTITION `part2` ENGINE = InnoDB,
301 PARTITION `p4` ENGINE = InnoDB)
302 t1#P#p0.ibd
303 t1#P#p4.ibd
304 t1#P#part1.ibd
305 t1#P#part2.ibd
306 t1#P#part7.ibd
307 t1.frm
308 t1.par
309 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
310 id select_type table partitions type possible_keys key key_len ref rows Extra
311 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
312 # check read single success: 1
313 # check read all success: 1
314 # check read row by row success: 1
315 ALTER TABLE t1 COALESCE PARTITION 1;
316 SHOW CREATE TABLE t1;
317 Table Create Table
318 t1 CREATE TABLE `t1` (
319 `f_date` date DEFAULT NULL,
320 `f_varchar` varchar(30) DEFAULT NULL
321 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
322 PARTITION BY HASH (year(`f_date`))
323 (PARTITION `p0` ENGINE = InnoDB,
324 PARTITION `part1` ENGINE = InnoDB,
325 PARTITION `part7` ENGINE = InnoDB,
326 PARTITION `part2` ENGINE = InnoDB)
327 t1#P#p0.ibd
328 t1#P#part1.ibd
329 t1#P#part2.ibd
330 t1#P#part7.ibd
331 t1.frm
332 t1.par
333 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
334 id select_type table partitions type possible_keys key key_len ref rows Extra
335 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
336 # check read single success: 1
337 # check read all success: 1
338 # check read row by row success: 1
339 ALTER TABLE t1 COALESCE PARTITION 1;
340 SHOW CREATE TABLE t1;
341 Table Create Table
342 t1 CREATE TABLE `t1` (
343 `f_date` date DEFAULT NULL,
344 `f_varchar` varchar(30) DEFAULT NULL
345 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
346 PARTITION BY HASH (year(`f_date`))
347 (PARTITION `p0` ENGINE = InnoDB,
348 PARTITION `part1` ENGINE = InnoDB,
349 PARTITION `part7` ENGINE = InnoDB)
350 t1#P#p0.ibd
351 t1#P#part1.ibd
352 t1#P#part7.ibd
353 t1.frm
354 t1.par
355 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
356 id select_type table partitions type possible_keys key key_len ref rows Extra
357 1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
358 # check read single success: 1
359 # check read all success: 1
360 # check read row by row success: 1
361 ALTER TABLE t1 COALESCE PARTITION 1;
362 SHOW CREATE TABLE t1;
363 Table Create Table
364 t1 CREATE TABLE `t1` (
365 `f_date` date DEFAULT NULL,
366 `f_varchar` varchar(30) DEFAULT NULL
367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
368 PARTITION BY HASH (year(`f_date`))
369 (PARTITION `p0` ENGINE = InnoDB,
370 PARTITION `part1` ENGINE = InnoDB)
371 t1#P#p0.ibd
372 t1#P#part1.ibd
373 t1.frm
374 t1.par
375 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
376 id select_type table partitions type possible_keys key key_len ref rows Extra
377 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
378 # check read single success: 1
379 # check read all success: 1
380 # check read row by row success: 1
381 ALTER TABLE t1 COALESCE PARTITION 1;
382 SHOW CREATE TABLE t1;
383 Table Create Table
384 t1 CREATE TABLE `t1` (
385 `f_date` date DEFAULT NULL,
386 `f_varchar` varchar(30) DEFAULT NULL
387 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
388 PARTITION BY HASH (year(`f_date`))
389 (PARTITION `p0` ENGINE = InnoDB)
390 t1#P#p0.ibd
391 t1.frm
392 t1.par
393 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
394 id select_type table partitions type possible_keys key key_len ref rows Extra
395 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
396 # check read single success: 1
397 # check read all success: 1
398 # check read row by row success: 1
399 # 1.2.5 COALESCE of last partition --> must fail
400 ALTER TABLE t1 COALESCE PARTITION 1;
401 ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
402 # 1.2.6 Remove partitioning
403 ALTER TABLE t1 REMOVE PARTITIONING;
404 SHOW CREATE TABLE t1;
405 Table Create Table
406 t1 CREATE TABLE `t1` (
407 `f_date` date DEFAULT NULL,
408 `f_varchar` varchar(30) DEFAULT NULL
409 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
410 t1.frm
411 t1.ibd
412 EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
413 id select_type table partitions type possible_keys key key_len ref rows Extra
414 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
415 # check read single success: 1
416 # check read all success: 1
417 # check read row by row success: 1
418 # 1.2.7 Remove partitioning from not partitioned table --> ????
419 ALTER TABLE t1 REMOVE PARTITIONING;
420 ERROR HY000: Partition management on a not partitioned table is not possible
421 DROP TABLE t1;
422 # Attention: There are unused files.
423 # Either the DROP TABLE or a preceding ALTER TABLE
424 # <alter partitioning> worked incomplete.
425 # We found:
426 unified filelist
427 --- not determined ---
428
429 #========================================================================
430 # 2. Partition management commands on KEY partitioned table
431 #========================================================================
432 DROP TABLE IF EXISTS t1;
433 CREATE TABLE t1 (
434 f_int1 INTEGER DEFAULT 0,
435 f_int2 INTEGER DEFAULT 0,
436 f_char1 CHAR(20),
437 f_char2 CHAR(20),
438 f_charbig VARCHAR(1000)
439 );
440 INSERT INTO t1(f_int1,f_int2,f_char1,f_char2,f_charbig)
441 SELECT f_int1,f_int2,f_char1,f_char2,f_charbig FROM t0_template;
442 SHOW CREATE TABLE t1;
443 Table Create Table
444 t1 CREATE TABLE `t1` (
445 `f_int1` int(11) DEFAULT 0,
446 `f_int2` int(11) DEFAULT 0,
447 `f_char1` char(20) DEFAULT NULL,
448 `f_char2` char(20) DEFAULT NULL,
449 `f_charbig` varchar(1000) DEFAULT NULL
450 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
451 t1.frm
452 t1.ibd
453 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
454 id select_type table partitions type possible_keys key key_len ref rows Extra
455 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
456 # check read single success: 1
457 # check read all success: 1
458 # check read row by row success: 1
459 #------------------------------------------------------------------------
460 # 2.1 Increase number of PARTITIONS
461 # Some negative testcases are omitted (already checked with HASH).
462 #------------------------------------------------------------------------
463 # 2.1.1 Assign KEY partitioning
464 ALTER TABLE t1 PARTITION BY KEY(f_int1);
465 SHOW CREATE TABLE t1;
466 Table Create Table
467 t1 CREATE TABLE `t1` (
468 `f_int1` int(11) DEFAULT 0,
469 `f_int2` int(11) DEFAULT 0,
470 `f_char1` char(20) DEFAULT NULL,
471 `f_char2` char(20) DEFAULT NULL,
472 `f_charbig` varchar(1000) DEFAULT NULL
473 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
474 PARTITION BY KEY (`f_int1`)
475 t1#P#p0.ibd
476 t1.frm
477 t1.par
478 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
479 id select_type table partitions type possible_keys key key_len ref rows Extra
480 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
481 # check read single success: 1
482 # check read all success: 1
483 # check read row by row success: 1
484 # 2.1.2 Add PARTITIONS not fitting to KEY --> must fail
485 ALTER TABLE t1 ADD PARTITION (PARTITION part1 VALUES IN (0));
486 ERROR HY000: Only LIST PARTITIONING can use VALUES IN in partition definition
487 ALTER TABLE t1 ADD PARTITION (PARTITION part2 VALUES LESS THAN (0));
488 ERROR HY000: Only RANGE PARTITIONING can use VALUES LESS THAN in partition definition
489 # 2.1.3 Add two named partitions + test
490 ALTER TABLE t1 ADD PARTITION (PARTITION part1, PARTITION part7);
491 SHOW CREATE TABLE t1;
492 Table Create Table
493 t1 CREATE TABLE `t1` (
494 `f_int1` int(11) DEFAULT 0,
495 `f_int2` int(11) DEFAULT 0,
496 `f_char1` char(20) DEFAULT NULL,
497 `f_char2` char(20) DEFAULT NULL,
498 `f_charbig` varchar(1000) DEFAULT NULL
499 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
500 PARTITION BY KEY (`f_int1`)
501 (PARTITION `p0` ENGINE = InnoDB,
502 PARTITION `part1` ENGINE = InnoDB,
503 PARTITION `part7` ENGINE = InnoDB)
504 t1#P#p0.ibd
505 t1#P#part1.ibd
506 t1#P#part7.ibd
507 t1.frm
508 t1.par
509 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
510 id select_type table partitions type possible_keys key key_len ref rows Extra
511 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
512 # check read single success: 1
513 # check read all success: 1
514 # check read row by row success: 1
515 # 2.1.4 Add one named partition + test
516 ALTER TABLE t1 ADD PARTITION (PARTITION part2);
517 SHOW CREATE TABLE t1;
518 Table Create Table
519 t1 CREATE TABLE `t1` (
520 `f_int1` int(11) DEFAULT 0,
521 `f_int2` int(11) DEFAULT 0,
522 `f_char1` char(20) DEFAULT NULL,
523 `f_char2` char(20) DEFAULT NULL,
524 `f_charbig` varchar(1000) DEFAULT NULL
525 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
526 PARTITION BY KEY (`f_int1`)
527 (PARTITION `p0` ENGINE = InnoDB,
528 PARTITION `part1` ENGINE = InnoDB,
529 PARTITION `part7` ENGINE = InnoDB,
530 PARTITION `part2` ENGINE = InnoDB)
531 t1#P#p0.ibd
532 t1#P#part1.ibd
533 t1#P#part2.ibd
534 t1#P#part7.ibd
535 t1.frm
536 t1.par
537 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
538 id select_type table partitions type possible_keys key key_len ref rows Extra
539 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
540 # check read single success: 1
541 # check read all success: 1
542 # check read row by row success: 1
543 # 2.1.5 Add four not named partitions + test
544 ALTER TABLE t1 ADD PARTITION PARTITIONS 4;
545 SHOW CREATE TABLE t1;
546 Table Create Table
547 t1 CREATE TABLE `t1` (
548 `f_int1` int(11) DEFAULT 0,
549 `f_int2` int(11) DEFAULT 0,
550 `f_char1` char(20) DEFAULT NULL,
551 `f_char2` char(20) DEFAULT NULL,
552 `f_charbig` varchar(1000) DEFAULT NULL
553 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
554 PARTITION BY KEY (`f_int1`)
555 (PARTITION `p0` ENGINE = InnoDB,
556 PARTITION `part1` ENGINE = InnoDB,
557 PARTITION `part7` ENGINE = InnoDB,
558 PARTITION `part2` ENGINE = InnoDB,
559 PARTITION `p4` ENGINE = InnoDB,
560 PARTITION `p5` ENGINE = InnoDB,
561 PARTITION `p6` ENGINE = InnoDB,
562 PARTITION `p7` ENGINE = InnoDB)
563 t1#P#p0.ibd
564 t1#P#p4.ibd
565 t1#P#p5.ibd
566 t1#P#p6.ibd
567 t1#P#p7.ibd
568 t1#P#part1.ibd
569 t1#P#part2.ibd
570 t1#P#part7.ibd
571 t1.frm
572 t1.par
573 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
574 id select_type table partitions type possible_keys key key_len ref rows Extra
575 1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
576 # check read single success: 1
577 # check read all success: 1
578 # check read row by row success: 1
579 #------------------------------------------------------------------------
580 # 2.2 Decrease number of PARTITIONS
581 # Some negative testcases are omitted (already checked with HASH).
582 #------------------------------------------------------------------------
583 # 2.2.1 DROP PARTITION is not supported for KEY --> must fail
584 ALTER TABLE t1 DROP PARTITION part1;
585 ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions
586 # 2.2.4 COALESCE one partition + test loop
587 ALTER TABLE t1 COALESCE PARTITION 1;
588 SHOW CREATE TABLE t1;
589 Table Create Table
590 t1 CREATE TABLE `t1` (
591 `f_int1` int(11) DEFAULT 0,
592 `f_int2` int(11) DEFAULT 0,
593 `f_char1` char(20) DEFAULT NULL,
594 `f_char2` char(20) DEFAULT NULL,
595 `f_charbig` varchar(1000) DEFAULT NULL
596 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
597 PARTITION BY KEY (`f_int1`)
598 (PARTITION `p0` ENGINE = InnoDB,
599 PARTITION `part1` ENGINE = InnoDB,
600 PARTITION `part7` ENGINE = InnoDB,
601 PARTITION `part2` ENGINE = InnoDB,
602 PARTITION `p4` ENGINE = InnoDB,
603 PARTITION `p5` ENGINE = InnoDB,
604 PARTITION `p6` ENGINE = InnoDB)
605 t1#P#p0.ibd
606 t1#P#p4.ibd
607 t1#P#p5.ibd
608 t1#P#p6.ibd
609 t1#P#part1.ibd
610 t1#P#part2.ibd
611 t1#P#part7.ibd
612 t1.frm
613 t1.par
614 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
615 id select_type table partitions type possible_keys key key_len ref rows Extra
616 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
617 # check read single success: 1
618 # check read all success: 1
619 # check read row by row success: 1
620 ALTER TABLE t1 COALESCE PARTITION 1;
621 SHOW CREATE TABLE t1;
622 Table Create Table
623 t1 CREATE TABLE `t1` (
624 `f_int1` int(11) DEFAULT 0,
625 `f_int2` int(11) DEFAULT 0,
626 `f_char1` char(20) DEFAULT NULL,
627 `f_char2` char(20) DEFAULT NULL,
628 `f_charbig` varchar(1000) DEFAULT NULL
629 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
630 PARTITION BY KEY (`f_int1`)
631 (PARTITION `p0` ENGINE = InnoDB,
632 PARTITION `part1` ENGINE = InnoDB,
633 PARTITION `part7` ENGINE = InnoDB,
634 PARTITION `part2` ENGINE = InnoDB,
635 PARTITION `p4` ENGINE = InnoDB,
636 PARTITION `p5` ENGINE = InnoDB)
637 t1#P#p0.ibd
638 t1#P#p4.ibd
639 t1#P#p5.ibd
640 t1#P#part1.ibd
641 t1#P#part2.ibd
642 t1#P#part7.ibd
643 t1.frm
644 t1.par
645 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
646 id select_type table partitions type possible_keys key key_len ref rows Extra
647 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
648 # check read single success: 1
649 # check read all success: 1
650 # check read row by row success: 1
651 ALTER TABLE t1 COALESCE PARTITION 1;
652 SHOW CREATE TABLE t1;
653 Table Create Table
654 t1 CREATE TABLE `t1` (
655 `f_int1` int(11) DEFAULT 0,
656 `f_int2` int(11) DEFAULT 0,
657 `f_char1` char(20) DEFAULT NULL,
658 `f_char2` char(20) DEFAULT NULL,
659 `f_charbig` varchar(1000) DEFAULT NULL
660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
661 PARTITION BY KEY (`f_int1`)
662 (PARTITION `p0` ENGINE = InnoDB,
663 PARTITION `part1` ENGINE = InnoDB,
664 PARTITION `part7` ENGINE = InnoDB,
665 PARTITION `part2` ENGINE = InnoDB,
666 PARTITION `p4` ENGINE = InnoDB)
667 t1#P#p0.ibd
668 t1#P#p4.ibd
669 t1#P#part1.ibd
670 t1#P#part2.ibd
671 t1#P#part7.ibd
672 t1.frm
673 t1.par
674 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
675 id select_type table partitions type possible_keys key key_len ref rows Extra
676 1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
677 # check read single success: 1
678 # check read all success: 1
679 # check read row by row success: 1
680 ALTER TABLE t1 COALESCE PARTITION 1;
681 SHOW CREATE TABLE t1;
682 Table Create Table
683 t1 CREATE TABLE `t1` (
684 `f_int1` int(11) DEFAULT 0,
685 `f_int2` int(11) DEFAULT 0,
686 `f_char1` char(20) DEFAULT NULL,
687 `f_char2` char(20) DEFAULT NULL,
688 `f_charbig` varchar(1000) DEFAULT NULL
689 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
690 PARTITION BY KEY (`f_int1`)
691 (PARTITION `p0` ENGINE = InnoDB,
692 PARTITION `part1` ENGINE = InnoDB,
693 PARTITION `part7` ENGINE = InnoDB,
694 PARTITION `part2` ENGINE = InnoDB)
695 t1#P#p0.ibd
696 t1#P#part1.ibd
697 t1#P#part2.ibd
698 t1#P#part7.ibd
699 t1.frm
700 t1.par
701 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
702 id select_type table partitions type possible_keys key key_len ref rows Extra
703 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
704 # check read single success: 1
705 # check read all success: 1
706 # check read row by row success: 1
707 ALTER TABLE t1 COALESCE PARTITION 1;
708 SHOW CREATE TABLE t1;
709 Table Create Table
710 t1 CREATE TABLE `t1` (
711 `f_int1` int(11) DEFAULT 0,
712 `f_int2` int(11) DEFAULT 0,
713 `f_char1` char(20) DEFAULT NULL,
714 `f_char2` char(20) DEFAULT NULL,
715 `f_charbig` varchar(1000) DEFAULT NULL
716 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
717 PARTITION BY KEY (`f_int1`)
718 (PARTITION `p0` ENGINE = InnoDB,
719 PARTITION `part1` ENGINE = InnoDB,
720 PARTITION `part7` ENGINE = InnoDB)
721 t1#P#p0.ibd
722 t1#P#part1.ibd
723 t1#P#part7.ibd
724 t1.frm
725 t1.par
726 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
727 id select_type table partitions type possible_keys key key_len ref rows Extra
728 1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
729 # check read single success: 1
730 # check read all success: 1
731 # check read row by row success: 1
732 ALTER TABLE t1 COALESCE PARTITION 1;
733 SHOW CREATE TABLE t1;
734 Table Create Table
735 t1 CREATE TABLE `t1` (
736 `f_int1` int(11) DEFAULT 0,
737 `f_int2` int(11) DEFAULT 0,
738 `f_char1` char(20) DEFAULT NULL,
739 `f_char2` char(20) DEFAULT NULL,
740 `f_charbig` varchar(1000) DEFAULT NULL
741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
742 PARTITION BY KEY (`f_int1`)
743 (PARTITION `p0` ENGINE = InnoDB,
744 PARTITION `part1` ENGINE = InnoDB)
745 t1#P#p0.ibd
746 t1#P#part1.ibd
747 t1.frm
748 t1.par
749 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
750 id select_type table partitions type possible_keys key key_len ref rows Extra
751 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
752 # check read single success: 1
753 # check read all success: 1
754 # check read row by row success: 1
755 ALTER TABLE t1 COALESCE PARTITION 1;
756 SHOW CREATE TABLE t1;
757 Table Create Table
758 t1 CREATE TABLE `t1` (
759 `f_int1` int(11) DEFAULT 0,
760 `f_int2` int(11) DEFAULT 0,
761 `f_char1` char(20) DEFAULT NULL,
762 `f_char2` char(20) DEFAULT NULL,
763 `f_charbig` varchar(1000) DEFAULT NULL
764 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
765 PARTITION BY KEY (`f_int1`)
766 (PARTITION `p0` ENGINE = InnoDB)
767 t1#P#p0.ibd
768 t1.frm
769 t1.par
770 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
771 id select_type table partitions type possible_keys key key_len ref rows Extra
772 1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
773 # check read single success: 1
774 # check read all success: 1
775 # check read row by row success: 1
776 # 2.2.5 COALESCE of last partition --> must fail
777 ALTER TABLE t1 COALESCE PARTITION 1;
778 ERROR HY000: Cannot remove all partitions, use DROP TABLE instead
779 # 2.2.6 Remove partitioning
780 ALTER TABLE t1 REMOVE PARTITIONING;
781 SHOW CREATE TABLE t1;
782 Table Create Table
783 t1 CREATE TABLE `t1` (
784 `f_int1` int(11) DEFAULT 0,
785 `f_int2` int(11) DEFAULT 0,
786 `f_char1` char(20) DEFAULT NULL,
787 `f_char2` char(20) DEFAULT NULL,
788 `f_charbig` varchar(1000) DEFAULT NULL
789 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
790 t1.frm
791 t1.ibd
792 EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
793 id select_type table partitions type possible_keys key key_len ref rows Extra
794 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 20 Using where
795 # check read single success: 1
796 # check read all success: 1
797 # check read row by row success: 1
798 # 2.2.7 Remove partitioning from not partitioned table --> ????
799 ALTER TABLE t1 REMOVE PARTITIONING;
800 ERROR HY000: Partition management on a not partitioned table is not possible
801 DROP TABLE t1;
802 # Attention: There are unused files.
803 # Either the DROP TABLE or a preceding ALTER TABLE
804 # <alter partitioning> worked incomplete.
805 # We found:
806 unified filelist
807 --- not determined ---
808 DROP VIEW IF EXISTS v1;
809 DROP TABLE IF EXISTS t1;
810 DROP TABLE IF EXISTS t0_aux;
811 DROP TABLE IF EXISTS t0_definition;
812 DROP TABLE IF EXISTS t0_template;
813 SET GLOBAL innodb_stats_persistent=@save_stats_persistent;