Debian Codegraph @

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

raw
1 # Creating database MySQL_TEST_DB
2 CREATE DATABASE MySQL_Test_DB;
3 USE MySQL_Test_DB;
4 # 1.0 KEY partitioning mgm
5 # Creating KEY partitioned table
6 CREATE TABLE TableA (a INT)
7 ENGINE = 'InnoDB'
8 PARTITION BY KEY (a)
9 (PARTITION parta ,
10 PARTITION partB ,
11 PARTITION Partc ,
12 PARTITION PartD );
13 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
14 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
15 SELECT * FROM TableA;
16 a
17 1
18 10
19 11
20 12
21 2
22 3
23 4
24 5
25 6
26 7
27 8
28 9
29 # Test of ADD/COALESCE PARTITIONS
30 # expecting duplicate partition name
31 ALTER TABLE TableA ADD PARTITION
32 (PARTITION partA,
33 PARTITION Parta,
34 PARTITION PartA);
35 ERROR HY000: Duplicate partition name partA
36 ALTER TABLE TableA ADD PARTITION
37 (PARTITION partE,
38 PARTITION Partf,
39 PARTITION PartG);
40 SELECT * FROM TableA;
41 a
42 1
43 10
44 11
45 12
46 2
47 3
48 4
49 5
50 6
51 7
52 8
53 9
54 SHOW CREATE TABLE TableA;
55 Table Create Table
56 TableA CREATE TABLE `TableA` (
57 `a` int DEFAULT NULL
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
59 /*!50100 PARTITION BY KEY (a)
60 (PARTITION parta ENGINE = InnoDB,
61 PARTITION partB ENGINE = InnoDB,
62 PARTITION Partc ENGINE = InnoDB,
63 PARTITION PartD ENGINE = InnoDB,
64 PARTITION partE ENGINE = InnoDB,
65 PARTITION Partf ENGINE = InnoDB,
66 PARTITION PartG ENGINE = InnoDB) */
67 ALTER TABLE TableA COALESCE PARTITION 4;
68 SELECT * FROM TableA;
69 a
70 1
71 10
72 11
73 12
74 2
75 3
76 4
77 5
78 6
79 7
80 8
81 9
82 SHOW CREATE TABLE TableA;
83 Table Create Table
84 TableA CREATE TABLE `TableA` (
85 `a` int DEFAULT NULL
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
87 /*!50100 PARTITION BY KEY (a)
88 (PARTITION parta ENGINE = InnoDB,
89 PARTITION partB ENGINE = InnoDB,
90 PARTITION Partc ENGINE = InnoDB) */
91 # Test of EXCHANGE PARTITION WITH TABLE
92 SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='MySQL_Test_DB' AND TABLE_NAME = 'TableA';
93 PARTITION_NAME TABLE_ROWS
94 Partc 4
95 partB 4
96 parta 4
97 CREATE TABLE TableB LIKE TableA;
98 ALTER TABLE TableB REMOVE PARTITIONING;
99 ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB;
100 SELECT * FROM TableA;
101 a
102 1
103 12
104 2
105 3
106 6
107 7
108 8
109 9
110 SHOW CREATE TABLE TableA;
111 Table Create Table
112 TableA CREATE TABLE `TableA` (
113 `a` int DEFAULT NULL
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
115 /*!50100 PARTITION BY KEY (a)
116 (PARTITION parta ENGINE = InnoDB,
117 PARTITION partB ENGINE = InnoDB,
118 PARTITION Partc ENGINE = InnoDB) */
119 SELECT * FROM TableB;
120 a
121 10
122 11
123 4
124 5
125 SHOW CREATE TABLE TableB;
126 Table Create Table
127 TableB CREATE TABLE `TableB` (
128 `a` int DEFAULT NULL
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
130 SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='MySQL_Test_DB' AND TABLE_NAME = 'TableA';
131 PARTITION_NAME HAVE_TABLE_ROWS
132 Partc YES
133 partB YES
134 parta NO
135 ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB;
136 INSERT INTO TableB VALUES (11);
137 ALTER TABLE TableA EXCHANGE PARTITION Partc WITH TABLE TableB;
138 ERROR HY000: Found a row that does not match the partition
139 DROP TABLE TableB;
140 SELECT * FROM TableA;
141 a
142 1
143 10
144 11
145 12
146 2
147 3
148 4
149 5
150 6
151 7
152 8
153 9
154 SHOW CREATE TABLE TableA;
155 Table Create Table
156 TableA CREATE TABLE `TableA` (
157 `a` int DEFAULT NULL
158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
159 /*!50100 PARTITION BY KEY (a)
160 (PARTITION parta ENGINE = InnoDB,
161 PARTITION partB ENGINE = InnoDB,
162 PARTITION Partc ENGINE = InnoDB) */
163 # Test of REORGANIZE PARTITIONS
164 # Should not work on HASH/KEY
165 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
166 (PARTITION PARTA ,
167 PARTITION partc );
168 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
169 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
170 (PARTITION partB ,
171 PARTITION parta );
172 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
173 ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
174 (PARTITION partB COMMENT="Previusly named parta",
175 PARTITION parta COMMENT="Previusly named partB");
176 SELECT * FROM TableA;
177 a
178 1
179 10
180 11
181 12
182 2
183 3
184 4
185 5
186 6
187 7
188 8
189 9
190 SHOW CREATE TABLE TableA;
191 Table Create Table
192 TableA CREATE TABLE `TableA` (
193 `a` int DEFAULT NULL
194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
195 /*!50100 PARTITION BY KEY (a)
196 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = InnoDB,
197 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = InnoDB,
198 PARTITION Partc ENGINE = InnoDB) */
199 # Test of RENAME TABLE
200 RENAME TABLE TableA to TableB;
201 SELECT * FROM TableB;
202 a
203 1
204 10
205 11
206 12
207 2
208 3
209 4
210 5
211 6
212 7
213 8
214 9
215 RENAME TABLE TableB to TableA;
216 SELECT * FROM TableA;
217 a
218 1
219 10
220 11
221 12
222 2
223 3
224 4
225 5
226 6
227 7
228 8
229 9
230 # Checking name comparision Upper vs Lower case
231 # Error if lower_case_table_names != 0
232 # lower_case_table_names: 0
233 CREATE TABLE tablea (a INT)
234 ENGINE = 'InnoDB'
235 PARTITION BY KEY (a)
236 (PARTITION parta ,
237 PARTITION partB ,
238 PARTITION Partc ,
239 PARTITION PartD );
240 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
241 SHOW TABLES;
242 Tables_in_MySQL_Test_DB
243 TableA
244 tablea
245 RENAME TABLE TableA to tableA;
246 SELECT * FROM tablea;
247 a
248 1
249 10
250 2
251 7
252 8
253 9
254 SELECT * FROM tableA;
255 a
256 1
257 10
258 11
259 12
260 2
261 3
262 4
263 5
264 6
265 7
266 8
267 9
268 RENAME TABLE tableA to TableA;
269 SHOW CREATE TABLE tablea;
270 Table Create Table
271 tablea CREATE TABLE `tablea` (
272 `a` int DEFAULT NULL
273 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
274 /*!50100 PARTITION BY KEY (a)
275 (PARTITION parta ENGINE = InnoDB,
276 PARTITION partB ENGINE = InnoDB,
277 PARTITION Partc ENGINE = InnoDB,
278 PARTITION PartD ENGINE = InnoDB) */
279 DROP TABLE tablea;
280 # Test of REMOVE PARTITIONING
281 ALTER TABLE TableA REMOVE PARTITIONING;
282 SELECT * FROM TableA;
283 a
284 1
285 10
286 11
287 12
288 2
289 3
290 4
291 5
292 6
293 7
294 8
295 9
296 SHOW CREATE TABLE TableA;
297 Table Create Table
298 TableA CREATE TABLE `TableA` (
299 `a` int DEFAULT NULL
300 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
301 # Cleaning up after KEY PARTITIONING test
302 DROP TABLE TableA;
303 # 2.0 HASH partitioning mgm
304 # expecting duplicate partition name
305 CREATE TABLE TableA (a INT)
306 ENGINE = 'InnoDB'
307 PARTITION BY HASH (a)
308 (PARTITION parta ,
309 PARTITION partA ,
310 PARTITION Parta ,
311 PARTITION PartA );
312 ERROR HY000: Duplicate partition name partA
313 # Creating Hash partitioned table
314 CREATE TABLE TableA (a INT)
315 ENGINE = 'InnoDB'
316 PARTITION BY HASH (a)
317 (PARTITION parta ,
318 PARTITION partB ,
319 PARTITION Partc ,
320 PARTITION PartD );
321 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
322 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
323 SELECT * FROM TableA;
324 a
325 1
326 10
327 11
328 12
329 2
330 3
331 4
332 5
333 6
334 7
335 8
336 9
337 # Test of ADD/COALESCE PARTITIONS
338 # expecting duplicate partition name
339 ALTER TABLE TableA ADD PARTITION
340 (PARTITION partA,
341 PARTITION Parta,
342 PARTITION PartA);
343 ERROR HY000: Duplicate partition name partA
344 ALTER TABLE TableA ADD PARTITION
345 (PARTITION partE,
346 PARTITION Partf,
347 PARTITION PartG);
348 SELECT * FROM TableA;
349 a
350 1
351 10
352 11
353 12
354 2
355 3
356 4
357 5
358 6
359 7
360 8
361 9
362 SHOW CREATE TABLE TableA;
363 Table Create Table
364 TableA CREATE TABLE `TableA` (
365 `a` int DEFAULT NULL
366 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
367 /*!50100 PARTITION BY HASH (`a`)
368 (PARTITION parta ENGINE = InnoDB,
369 PARTITION partB ENGINE = InnoDB,
370 PARTITION Partc ENGINE = InnoDB,
371 PARTITION PartD ENGINE = InnoDB,
372 PARTITION partE ENGINE = InnoDB,
373 PARTITION Partf ENGINE = InnoDB,
374 PARTITION PartG ENGINE = InnoDB) */
375 ALTER TABLE TableA COALESCE PARTITION 4;
376 SELECT * FROM TableA;
377 a
378 1
379 10
380 11
381 12
382 2
383 3
384 4
385 5
386 6
387 7
388 8
389 9
390 SHOW CREATE TABLE TableA;
391 Table Create Table
392 TableA CREATE TABLE `TableA` (
393 `a` int DEFAULT NULL
394 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
395 /*!50100 PARTITION BY HASH (`a`)
396 (PARTITION parta ENGINE = InnoDB,
397 PARTITION partB ENGINE = InnoDB,
398 PARTITION Partc ENGINE = InnoDB) */
399 # Test of REORGANIZE PARTITIONS
400 # Should not work on HASH/KEY
401 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
402 (PARTITION PARTA ,
403 PARTITION partc );
404 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
405 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
406 (PARTITION partB ,
407 PARTITION parta );
408 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
409 ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
410 (PARTITION partB COMMENT="Previusly named parta",
411 PARTITION parta COMMENT="Previusly named partB");
412 SELECT * FROM TableA;
413 a
414 1
415 10
416 11
417 12
418 2
419 3
420 4
421 5
422 6
423 7
424 8
425 9
426 SHOW CREATE TABLE TableA;
427 Table Create Table
428 TableA CREATE TABLE `TableA` (
429 `a` int DEFAULT NULL
430 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
431 /*!50100 PARTITION BY HASH (`a`)
432 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = InnoDB,
433 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = InnoDB,
434 PARTITION Partc ENGINE = InnoDB) */
435 # Test of RENAME TABLE
436 RENAME TABLE TableA to TableB;
437 SELECT * FROM TableB;
438 a
439 1
440 10
441 11
442 12
443 2
444 3
445 4
446 5
447 6
448 7
449 8
450 9
451 RENAME TABLE TableB to TableA;
452 SELECT * FROM TableA;
453 a
454 1
455 10
456 11
457 12
458 2
459 3
460 4
461 5
462 6
463 7
464 8
465 9
466 # Checking name comparision Upper vs Lower case
467 # Error if lower_case_table_names != 0
468 # lower_case_table_names: 0
469 CREATE TABLE tablea (a INT)
470 ENGINE = 'InnoDB'
471 PARTITION BY HASH (a)
472 (PARTITION parta ,
473 PARTITION partB ,
474 PARTITION Partc ,
475 PARTITION PartD );
476 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
477 SHOW TABLES;
478 Tables_in_MySQL_Test_DB
479 TableA
480 tablea
481 RENAME TABLE TableA to tableA;
482 SELECT * FROM tablea;
483 a
484 1
485 10
486 2
487 7
488 8
489 9
490 SELECT * FROM tableA;
491 a
492 1
493 10
494 11
495 12
496 2
497 3
498 4
499 5
500 6
501 7
502 8
503 9
504 RENAME TABLE tableA to TableA;
505 SHOW CREATE TABLE tablea;
506 Table Create Table
507 tablea CREATE TABLE `tablea` (
508 `a` int DEFAULT NULL
509 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
510 /*!50100 PARTITION BY HASH (`a`)
511 (PARTITION parta ENGINE = InnoDB,
512 PARTITION partB ENGINE = InnoDB,
513 PARTITION Partc ENGINE = InnoDB,
514 PARTITION PartD ENGINE = InnoDB) */
515 DROP TABLE tablea;
516 # Test of REMOVE PARTITIONING
517 ALTER TABLE TableA REMOVE PARTITIONING;
518 SELECT * FROM TableA;
519 a
520 1
521 10
522 11
523 12
524 2
525 3
526 4
527 5
528 6
529 7
530 8
531 9
532 SHOW CREATE TABLE TableA;
533 Table Create Table
534 TableA CREATE TABLE `TableA` (
535 `a` int DEFAULT NULL
536 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
537 # Cleaning up after HASH PARTITIONING test
538 DROP TABLE TableA;
539 # 3.0 RANGE partitioning mgm
540 # Creating RANGE partitioned table
541 CREATE TABLE TableA (a INT)
542 ENGINE = 'InnoDB'
543 PARTITION BY RANGE (a)
544 (PARTITION parta VALUES LESS THAN (4) ,
545 PARTITION partB VALUES LESS THAN (7) ,
546 PARTITION Partc VALUES LESS THAN (10) ,
547 PARTITION PartD VALUES LESS THAN (13) );
548 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
549 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
550 SELECT * FROM TableA;
551 a
552 1
553 10
554 11
555 12
556 2
557 3
558 4
559 5
560 6
561 7
562 8
563 9
564 # Test of ADD/DROP PARTITIONS
565 # expecting duplicate partition name
566 ALTER TABLE TableA ADD PARTITION
567 (PARTITION partA VALUES LESS THAN (MAXVALUE));
568 ERROR HY000: Duplicate partition name partA
569 ALTER TABLE TableA ADD PARTITION
570 (PARTITION partE VALUES LESS THAN (16),
571 PARTITION Partf VALUES LESS THAN (19),
572 PARTITION PartG VALUES LESS THAN (22));
573 SELECT * FROM TableA;
574 a
575 1
576 10
577 11
578 12
579 2
580 3
581 4
582 5
583 6
584 7
585 8
586 9
587 SHOW CREATE TABLE TableA;
588 Table Create Table
589 TableA CREATE TABLE `TableA` (
590 `a` int DEFAULT NULL
591 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
592 /*!50100 PARTITION BY RANGE (`a`)
593 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
594 PARTITION partB VALUES LESS THAN (7) ENGINE = InnoDB,
595 PARTITION Partc VALUES LESS THAN (10) ENGINE = InnoDB,
596 PARTITION PartD VALUES LESS THAN (13) ENGINE = InnoDB,
597 PARTITION partE VALUES LESS THAN (16) ENGINE = InnoDB,
598 PARTITION Partf VALUES LESS THAN (19) ENGINE = InnoDB,
599 PARTITION PartG VALUES LESS THAN (22) ENGINE = InnoDB) */
600 ALTER TABLE TableA DROP PARTITION partE, PartG;
601 ALTER TABLE TableA DROP PARTITION Partf;
602 ALTER TABLE TableA ADD PARTITION
603 (PARTITION PartE VALUES LESS THAN (MAXVALUE));
604 SELECT * FROM TableA;
605 a
606 1
607 10
608 11
609 12
610 2
611 3
612 4
613 5
614 6
615 7
616 8
617 9
618 SHOW CREATE TABLE TableA;
619 Table Create Table
620 TableA CREATE TABLE `TableA` (
621 `a` int DEFAULT NULL
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
623 /*!50100 PARTITION BY RANGE (`a`)
624 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
625 PARTITION partB VALUES LESS THAN (7) ENGINE = InnoDB,
626 PARTITION Partc VALUES LESS THAN (10) ENGINE = InnoDB,
627 PARTITION PartD VALUES LESS THAN (13) ENGINE = InnoDB,
628 PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
629 # Test of REORGANIZE PARTITIONS
630 # Error since it must reorganize a consecutive range
631 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
632 (PARTITION partB VALUES LESS THAN (3) ,
633 PARTITION parta VALUES LESS THAN (11) );
634 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
635 ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
636 (PARTITION partD VALUES LESS THAN (8)
637 COMMENT="Previously partB and partly Partc",
638 PARTITION partB VALUES LESS THAN (11)
639 COMMENT="Previously partly Partc and partly PartD",
640 PARTITION partC VALUES LESS THAN (MAXVALUE)
641 COMMENT="Previously partly PartD");
642 SELECT * FROM TableA;
643 a
644 1
645 10
646 11
647 12
648 2
649 3
650 4
651 5
652 6
653 7
654 8
655 9
656 SHOW CREATE TABLE TableA;
657 Table Create Table
658 TableA CREATE TABLE `TableA` (
659 `a` int DEFAULT NULL
660 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
661 /*!50100 PARTITION BY RANGE (`a`)
662 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
663 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = InnoDB,
664 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = InnoDB,
665 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = InnoDB) */
666 # Test of RENAME TABLE
667 RENAME TABLE TableA to TableB;
668 SELECT * FROM TableB;
669 a
670 1
671 10
672 11
673 12
674 2
675 3
676 4
677 5
678 6
679 7
680 8
681 9
682 RENAME TABLE TableB to TableA;
683 SELECT * FROM TableA;
684 a
685 1
686 10
687 11
688 12
689 2
690 3
691 4
692 5
693 6
694 7
695 8
696 9
697 # Checking name comparision Upper vs Lower case
698 # Error if lower_case_table_names != 0
699 # lower_case_table_names: 0
700 CREATE TABLE tablea (a INT)
701 ENGINE = 'InnoDB'
702 PARTITION BY RANGE (a)
703 (PARTITION parta VALUES LESS THAN (4) ,
704 PARTITION partB VALUES LESS THAN (7) ,
705 PARTITION Partc VALUES LESS THAN (10) ,
706 PARTITION PartD VALUES LESS THAN (13) );
707 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
708 SHOW TABLES;
709 Tables_in_MySQL_Test_DB
710 TableA
711 tablea
712 RENAME TABLE TableA to tableA;
713 SELECT * FROM tablea;
714 a
715 1
716 10
717 2
718 7
719 8
720 9
721 SELECT * FROM tableA;
722 a
723 1
724 10
725 11
726 12
727 2
728 3
729 4
730 5
731 6
732 7
733 8
734 9
735 RENAME TABLE tableA to TableA;
736 SHOW CREATE TABLE tablea;
737 Table Create Table
738 tablea CREATE TABLE `tablea` (
739 `a` int DEFAULT NULL
740 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
741 /*!50100 PARTITION BY RANGE (`a`)
742 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
743 PARTITION partB VALUES LESS THAN (7) ENGINE = InnoDB,
744 PARTITION Partc VALUES LESS THAN (10) ENGINE = InnoDB,
745 PARTITION PartD VALUES LESS THAN (13) ENGINE = InnoDB) */
746 DROP TABLE tablea;
747 # Test of REMOVE PARTITIONING
748 ALTER TABLE TableA REMOVE PARTITIONING;
749 SELECT * FROM TableA;
750 a
751 1
752 10
753 11
754 12
755 2
756 3
757 4
758 5
759 6
760 7
761 8
762 9
763 SHOW CREATE TABLE TableA;
764 Table Create Table
765 TableA CREATE TABLE `TableA` (
766 `a` int DEFAULT NULL
767 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
768 # Cleaning up after RANGE PARTITIONING test
769 DROP TABLE TableA;
770 # 4.0 LIST partitioning mgm
771 # Creating LIST partitioned table
772 CREATE TABLE TableA (a INT)
773 ENGINE = 'InnoDB'
774 PARTITION BY LIST (a)
775 (PARTITION parta VALUES IN (1,8,9) ,
776 PARTITION partB VALUES IN (2,10,11) ,
777 PARTITION Partc VALUES IN (3,4,7) ,
778 PARTITION PartD VALUES IN (5,6,12) );
779 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
780 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
781 SELECT * FROM TableA;
782 a
783 1
784 10
785 11
786 12
787 2
788 3
789 4
790 5
791 6
792 7
793 8
794 9
795 # Test of ADD/DROP PARTITIONS
796 # expecting duplicate partition name
797 ALTER TABLE TableA ADD PARTITION
798 (PARTITION partA VALUES IN (0));
799 ERROR HY000: Duplicate partition name partA
800 ALTER TABLE TableA ADD PARTITION
801 (PARTITION partE VALUES IN (16),
802 PARTITION Partf VALUES IN (19),
803 PARTITION PartG VALUES IN (22));
804 SELECT * FROM TableA;
805 a
806 1
807 10
808 11
809 12
810 2
811 3
812 4
813 5
814 6
815 7
816 8
817 9
818 SHOW CREATE TABLE TableA;
819 Table Create Table
820 TableA CREATE TABLE `TableA` (
821 `a` int DEFAULT NULL
822 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
823 /*!50100 PARTITION BY LIST (`a`)
824 (PARTITION parta VALUES IN (1,8,9) ENGINE = InnoDB,
825 PARTITION partB VALUES IN (2,10,11) ENGINE = InnoDB,
826 PARTITION Partc VALUES IN (3,4,7) ENGINE = InnoDB,
827 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
828 PARTITION partE VALUES IN (16) ENGINE = InnoDB,
829 PARTITION Partf VALUES IN (19) ENGINE = InnoDB,
830 PARTITION PartG VALUES IN (22) ENGINE = InnoDB) */
831 ALTER TABLE TableA DROP PARTITION partE, PartG;
832 ALTER TABLE TableA DROP PARTITION Partf;
833 ALTER TABLE TableA ADD PARTITION
834 (PARTITION PartE VALUES IN (13));
835 SELECT * FROM TableA;
836 a
837 1
838 10
839 11
840 12
841 2
842 3
843 4
844 5
845 6
846 7
847 8
848 9
849 SHOW CREATE TABLE TableA;
850 Table Create Table
851 TableA CREATE TABLE `TableA` (
852 `a` int DEFAULT NULL
853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
854 /*!50100 PARTITION BY LIST (`a`)
855 (PARTITION parta VALUES IN (1,8,9) ENGINE = InnoDB,
856 PARTITION partB VALUES IN (2,10,11) ENGINE = InnoDB,
857 PARTITION Partc VALUES IN (3,4,7) ENGINE = InnoDB,
858 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
859 PARTITION PartE VALUES IN (13) ENGINE = InnoDB) */
860 # Test of REORGANIZE PARTITIONS
861 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
862 (PARTITION Partc VALUES IN (1,7)
863 COMMENT = "Mix 1 of old parta and Partc",
864 PARTITION partF VALUES IN (3,9)
865 COMMENT = "Mix 2 of old parta and Partc",
866 PARTITION parta VALUES IN (4,8)
867 COMMENT = "Mix 3 of old parta and Partc");
868 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
869 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
870 (PARTITION Partc VALUES IN (1,7)
871 COMMENT = "Mix 1 of old parta and Partc",
872 PARTITION parta VALUES IN (3,9)
873 COMMENT = "Mix 2 of old parta and Partc",
874 PARTITION partB VALUES IN (4,8)
875 COMMENT = "Mix 3 of old parta and Partc");
876 SELECT * FROM TableA;
877 a
878 1
879 12
880 3
881 4
882 5
883 6
884 7
885 8
886 9
887 SHOW CREATE TABLE TableA;
888 Table Create Table
889 TableA CREATE TABLE `TableA` (
890 `a` int DEFAULT NULL
891 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
892 /*!50100 PARTITION BY LIST (`a`)
893 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = InnoDB,
894 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = InnoDB,
895 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = InnoDB,
896 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
897 PARTITION PartE VALUES IN (13) ENGINE = InnoDB) */
898 # Test of RENAME TABLE
899 RENAME TABLE TableA to TableB;
900 SELECT * FROM TableB;
901 a
902 1
903 12
904 3
905 4
906 5
907 6
908 7
909 8
910 9
911 RENAME TABLE TableB to TableA;
912 SELECT * FROM TableA;
913 a
914 1
915 12
916 3
917 4
918 5
919 6
920 7
921 8
922 9
923 # Checking name comparision Upper vs Lower case
924 # Error if lower_case_table_names != 0
925 # lower_case_table_names: 0
926 CREATE TABLE tablea (a INT)
927 ENGINE = 'InnoDB'
928 PARTITION BY LIST (a)
929 (PARTITION parta VALUES IN (1,8,9) ,
930 PARTITION partB VALUES IN (2,10,11) ,
931 PARTITION Partc VALUES IN (3,4,7) ,
932 PARTITION PartD VALUES IN (5,6,12) );
933 INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
934 SHOW TABLES;
935 Tables_in_MySQL_Test_DB
936 TableA
937 tablea
938 RENAME TABLE TableA to tableA;
939 SELECT * FROM tablea;
940 a
941 1
942 10
943 2
944 7
945 8
946 9
947 SELECT * FROM tableA;
948 a
949 1
950 12
951 3
952 4
953 5
954 6
955 7
956 8
957 9
958 RENAME TABLE tableA to TableA;
959 SHOW CREATE TABLE tablea;
960 Table Create Table
961 tablea CREATE TABLE `tablea` (
962 `a` int DEFAULT NULL
963 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
964 /*!50100 PARTITION BY LIST (`a`)
965 (PARTITION parta VALUES IN (1,8,9) ENGINE = InnoDB,
966 PARTITION partB VALUES IN (2,10,11) ENGINE = InnoDB,
967 PARTITION Partc VALUES IN (3,4,7) ENGINE = InnoDB,
968 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB) */
969 DROP TABLE tablea;
970 # Test of REMOVE PARTITIONING
971 ALTER TABLE TableA REMOVE PARTITIONING;
972 SELECT * FROM TableA;
973 a
974 1
975 12
976 3
977 4
978 5
979 6
980 7
981 8
982 9
983 SHOW CREATE TABLE TableA;
984 Table Create Table
985 TableA CREATE TABLE `TableA` (
986 `a` int DEFAULT NULL
987 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
988 # Cleaning up after LIST PARTITIONING test
989 DROP TABLE TableA;
990 # Testing TRUNCATE PARTITION
991 CREATE TABLE t1
992 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
993 b VARCHAR(255))
994 ENGINE = 'InnoDB'
995 PARTITION BY RANGE (a)
996 (PARTITION LT1000 VALUES LESS THAN (1000),
997 PARTITION LT2000 VALUES LESS THAN (2000),
998 PARTITION MAX VALUES LESS THAN MAXVALUE);
999 INSERT INTO t1 VALUES (NULL, "First"), (NULL, "Second"), (999, "Last in LT1000"), (NULL, "First in LT2000"), (NULL, "Second in LT2000"), (1999, "Last in LT2000"), (NULL, "First in MAX"), (NULL, "Second in MAX");
1000 SHOW CREATE TABLE t1;
1001 Table Create Table
1002 t1 CREATE TABLE `t1` (
1003 `a` bigint NOT NULL AUTO_INCREMENT,
1004 `b` varchar(255) DEFAULT NULL,
1005 PRIMARY KEY (`a`)
1006 ) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1007 /*!50100 PARTITION BY RANGE (`a`)
1008 (PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
1009 PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
1010 PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1011 SELECT * FROM t1 ORDER BY a;
1012 a b
1013 1 First
1014 2 Second
1015 999 Last in LT1000
1016 1000 First in LT2000
1017 1001 Second in LT2000
1018 1999 Last in LT2000
1019 2000 First in MAX
1020 2001 Second in MAX
1021 ALTER TABLE t1 ANALYZE PARTITION MAX;
1022 Table Op Msg_type Msg_text
1023 MySQL_Test_DB.t1 analyze status OK
1024 # Truncate without FLUSH
1025 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1026 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
1027 SELECT * FROM t1 WHERE a >= 2000;
1028 a b
1029 2002 First after TRUNCATE MAX (1)
1030 # Truncate with FLUSH after
1031 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1032 FLUSH TABLES;
1033 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
1034 SELECT * FROM t1 WHERE a >= 2000;
1035 a b
1036 2003 First after TRUNCATE MAX (2)
1037 # Truncate with FLUSH before
1038 FLUSH TABLES;
1039 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1040 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
1041 SELECT * FROM t1 WHERE a >= 2000;
1042 a b
1043 2004 First after TRUNCATE MAX (3)
1044 # Truncate with FLUSH after INSERT
1045 FLUSH TABLES;
1046 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1047 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
1048 SELECT * FROM t1 WHERE a >= 2000;
1049 a b
1050 2005 First after TRUNCATE MAX (4)
1051 # Truncate without FLUSH
1052 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1053 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
1054 SELECT * FROM t1 ORDER BY a;
1055 a b
1056 1000 First in LT2000
1057 1001 Second in LT2000
1058 1999 Last in LT2000
1059 2005 First after TRUNCATE MAX (4)
1060 2006 First after TRUNCATE LT1000 (1)
1061 # Truncate with FLUSH after
1062 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1063 FLUSH TABLES;
1064 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
1065 SELECT * FROM t1 ORDER BY a;
1066 a b
1067 1000 First in LT2000
1068 1001 Second in LT2000
1069 1999 Last in LT2000
1070 2005 First after TRUNCATE MAX (4)
1071 2006 First after TRUNCATE LT1000 (1)
1072 2007 First after TRUNCATE LT1000 (2)
1073 # Truncate with FLUSH before
1074 FLUSH TABLES;
1075 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1076 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
1077 SELECT * FROM t1 ORDER BY a;
1078 a b
1079 1000 First in LT2000
1080 1001 Second in LT2000
1081 1999 Last in LT2000
1082 2005 First after TRUNCATE MAX (4)
1083 2006 First after TRUNCATE LT1000 (1)
1084 2007 First after TRUNCATE LT1000 (2)
1085 2008 First after TRUNCATE LT1000 (3)
1086 # Truncate with FLUSH after INSERT
1087 FLUSH TABLES;
1088 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1089 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
1090 SELECT * FROM t1 ORDER BY a;
1091 a b
1092 1000 First in LT2000
1093 1001 Second in LT2000
1094 1999 Last in LT2000
1095 2005 First after TRUNCATE MAX (4)
1096 2006 First after TRUNCATE LT1000 (1)
1097 2007 First after TRUNCATE LT1000 (2)
1098 2008 First after TRUNCATE LT1000 (3)
1099 2009 First after TRUNCATE LT1000 (4)
1100 # Truncate without FLUSH
1101 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1102 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
1103 SELECT * FROM t1 ORDER BY a;
1104 a b
1105 2005 First after TRUNCATE MAX (4)
1106 2006 First after TRUNCATE LT1000 (1)
1107 2007 First after TRUNCATE LT1000 (2)
1108 2008 First after TRUNCATE LT1000 (3)
1109 2009 First after TRUNCATE LT1000 (4)
1110 2010 First after TRUNCATE LT2000 (1)
1111 # Truncate with FLUSH after
1112 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1113 FLUSH TABLES;
1114 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
1115 SELECT * FROM t1 ORDER BY a;
1116 a b
1117 2005 First after TRUNCATE MAX (4)
1118 2006 First after TRUNCATE LT1000 (1)
1119 2007 First after TRUNCATE LT1000 (2)
1120 2008 First after TRUNCATE LT1000 (3)
1121 2009 First after TRUNCATE LT1000 (4)
1122 2010 First after TRUNCATE LT2000 (1)
1123 2011 First after TRUNCATE LT2000 (2)
1124 # Truncate with FLUSH before
1125 FLUSH TABLES;
1126 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1127 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
1128 SELECT * FROM t1 ORDER BY a;
1129 a b
1130 2005 First after TRUNCATE MAX (4)
1131 2006 First after TRUNCATE LT1000 (1)
1132 2007 First after TRUNCATE LT1000 (2)
1133 2008 First after TRUNCATE LT1000 (3)
1134 2009 First after TRUNCATE LT1000 (4)
1135 2010 First after TRUNCATE LT2000 (1)
1136 2011 First after TRUNCATE LT2000 (2)
1137 2012 First after TRUNCATE LT2000 (3)
1138 # Truncate with FLUSH after INSERT
1139 FLUSH TABLES;
1140 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1141 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
1142 SELECT * FROM t1 ORDER BY a;
1143 a b
1144 2005 First after TRUNCATE MAX (4)
1145 2006 First after TRUNCATE LT1000 (1)
1146 2007 First after TRUNCATE LT1000 (2)
1147 2008 First after TRUNCATE LT1000 (3)
1148 2009 First after TRUNCATE LT1000 (4)
1149 2010 First after TRUNCATE LT2000 (1)
1150 2011 First after TRUNCATE LT2000 (2)
1151 2012 First after TRUNCATE LT2000 (3)
1152 2013 First after TRUNCATE LT2000 (4)
1153 DROP TABLE t1;
1154 # Cleaning up before exit
1155 USE test;
1156 DROP DATABASE MySQL_Test_DB;