Debian Codegraph @

mysql-test/suite/parts/r/partition_mgm_lc0_archive.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 = 'Archive'
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(11) DEFAULT NULL
58 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
59 PARTITION BY KEY (a)
60 (PARTITION parta ENGINE = ARCHIVE,
61 PARTITION partB ENGINE = ARCHIVE,
62 PARTITION Partc ENGINE = ARCHIVE,
63 PARTITION PartD ENGINE = ARCHIVE,
64 PARTITION partE ENGINE = ARCHIVE,
65 PARTITION Partf ENGINE = ARCHIVE,
66 PARTITION PartG ENGINE = ARCHIVE)
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(11) DEFAULT NULL
86 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
87 PARTITION BY KEY (a)
88 (PARTITION parta ENGINE = ARCHIVE,
89 PARTITION partB ENGINE = ARCHIVE,
90 PARTITION Partc ENGINE = ARCHIVE)
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 parta 4
95 partB 4
96 Partc 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(11) DEFAULT NULL
114 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
115 PARTITION BY KEY (a)
116 (PARTITION parta ENGINE = ARCHIVE,
117 PARTITION partB ENGINE = ARCHIVE,
118 PARTITION Partc ENGINE = ARCHIVE)
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(11) DEFAULT NULL
129 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
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 parta NO
133 partB YES
134 Partc YES
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(11) DEFAULT NULL
158 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
159 PARTITION BY KEY (a)
160 (PARTITION parta ENGINE = ARCHIVE,
161 PARTITION partB ENGINE = ARCHIVE,
162 PARTITION Partc ENGINE = ARCHIVE)
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(11) DEFAULT NULL
194 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
195 PARTITION BY KEY (a)
196 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
197 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
198 PARTITION Partc ENGINE = ARCHIVE)
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 = 'Archive'
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(11) DEFAULT NULL
273 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
274 PARTITION BY KEY (a)
275 (PARTITION parta ENGINE = ARCHIVE,
276 PARTITION partB ENGINE = ARCHIVE,
277 PARTITION Partc ENGINE = ARCHIVE,
278 PARTITION PartD ENGINE = ARCHIVE)
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(11) DEFAULT NULL
300 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
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 = 'Archive'
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 = 'Archive'
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(11) DEFAULT NULL
366 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
367 PARTITION BY HASH (a)
368 (PARTITION parta ENGINE = ARCHIVE,
369 PARTITION partB ENGINE = ARCHIVE,
370 PARTITION Partc ENGINE = ARCHIVE,
371 PARTITION PartD ENGINE = ARCHIVE,
372 PARTITION partE ENGINE = ARCHIVE,
373 PARTITION Partf ENGINE = ARCHIVE,
374 PARTITION PartG ENGINE = ARCHIVE)
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(11) DEFAULT NULL
394 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
395 PARTITION BY HASH (a)
396 (PARTITION parta ENGINE = ARCHIVE,
397 PARTITION partB ENGINE = ARCHIVE,
398 PARTITION Partc ENGINE = ARCHIVE)
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(11) DEFAULT NULL
430 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
431 PARTITION BY HASH (a)
432 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
433 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
434 PARTITION Partc ENGINE = ARCHIVE)
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 = 'Archive'
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(11) DEFAULT NULL
509 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
510 PARTITION BY HASH (a)
511 (PARTITION parta ENGINE = ARCHIVE,
512 PARTITION partB ENGINE = ARCHIVE,
513 PARTITION Partc ENGINE = ARCHIVE,
514 PARTITION PartD ENGINE = ARCHIVE)
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(11) DEFAULT NULL
536 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
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 = 'Archive'
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(11) DEFAULT NULL
591 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
592 PARTITION BY RANGE (a)
593 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
594 PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
595 PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
596 PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
597 PARTITION partE VALUES LESS THAN (16) ENGINE = ARCHIVE,
598 PARTITION Partf VALUES LESS THAN (19) ENGINE = ARCHIVE,
599 PARTITION PartG VALUES LESS THAN (22) ENGINE = ARCHIVE)
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(11) DEFAULT NULL
622 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
623 PARTITION BY RANGE (a)
624 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
625 PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
626 PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
627 PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
628 PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
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(11) DEFAULT NULL
660 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
661 PARTITION BY RANGE (a)
662 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
663 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE,
664 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE,
665 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE)
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 = 'Archive'
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(11) DEFAULT NULL
740 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
741 PARTITION BY RANGE (a)
742 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
743 PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
744 PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
745 PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE)
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(11) DEFAULT NULL
767 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
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 = 'Archive'
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(11) DEFAULT NULL
822 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
823 PARTITION BY LIST (a)
824 (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
825 PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
826 PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
827 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
828 PARTITION partE VALUES IN (16) ENGINE = ARCHIVE,
829 PARTITION Partf VALUES IN (19) ENGINE = ARCHIVE,
830 PARTITION PartG VALUES IN (22) ENGINE = ARCHIVE)
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(11) DEFAULT NULL
853 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
854 PARTITION BY LIST (a)
855 (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
856 PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
857 PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
858 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
859 PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE)
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(11) DEFAULT NULL
891 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
892 PARTITION BY LIST (a)
893 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE,
894 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE,
895 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE,
896 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
897 PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE)
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 = 'Archive'
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(11) DEFAULT NULL
963 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
964 PARTITION BY LIST (a)
965 (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
966 PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
967 PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
968 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE)
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(11) DEFAULT NULL
987 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
988 # Cleaning up after LIST PARTITIONING test
989 DROP TABLE TableA;
990 # Verify that TRUNCATE PARTITION gives error
991 CREATE TABLE t1
992 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
993 b VARCHAR(255))
994 ENGINE = 'Archive'
995 PARTITION BY KEY (a)
996 (PARTITION LT1000,
997 PARTITION LT2000,
998 PARTITION MAX);
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 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1001 Got one of the listed errors
1002 SELECT * FROM t1;
1003 a b
1004 1 First
1005 1000 First in LT2000
1006 1001 Second in LT2000
1007 1999 Last in LT2000
1008 2 Second
1009 2000 First in MAX
1010 2001 Second in MAX
1011 999 Last in LT1000
1012 # Cleaning up before exit
1013 USE test;
1014 DROP DATABASE MySQL_Test_DB;