Debian Codegraph @

mysql-test/suite/parts/r/partition_mgm_lc1_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: 1
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 ERROR 42S01: Table 'tablea' already exists
241 SHOW TABLES;
242 Tables_in_mysql_test_db
243 tablea
244 RENAME TABLE TableA to tablea;
245 ERROR 42S01: Table 'tablea' already exists
246 RENAME TABLE tablea to TableA;
247 ERROR 42S01: Table 'tablea' already exists
248 SELECT * FROM tablea;
249 a
250 1
251 10
252 11
253 12
254 2
255 3
256 4
257 5
258 6
259 7
260 8
261 9
262 SHOW CREATE TABLE tablea;
263 Table Create Table
264 tablea CREATE TABLE `tablea` (
265 `a` int(11) DEFAULT NULL
266 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
267 PARTITION BY KEY (a)
268 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
269 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
270 PARTITION Partc ENGINE = ARCHIVE)
271 # Test of REMOVE PARTITIONING
272 ALTER TABLE TableA REMOVE PARTITIONING;
273 SELECT * FROM TableA;
274 a
275 1
276 10
277 11
278 12
279 2
280 3
281 4
282 5
283 6
284 7
285 8
286 9
287 SHOW CREATE TABLE TableA;
288 Table Create Table
289 TableA CREATE TABLE `tablea` (
290 `a` int(11) DEFAULT NULL
291 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
292 # Cleaning up after KEY PARTITIONING test
293 DROP TABLE TableA;
294 # 2.0 HASH partitioning mgm
295 # expecting duplicate partition name
296 CREATE TABLE TableA (a INT)
297 ENGINE = 'Archive'
298 PARTITION BY HASH (a)
299 (PARTITION parta ,
300 PARTITION partA ,
301 PARTITION Parta ,
302 PARTITION PartA );
303 ERROR HY000: Duplicate partition name partA
304 # Creating Hash partitioned table
305 CREATE TABLE TableA (a INT)
306 ENGINE = 'Archive'
307 PARTITION BY HASH (a)
308 (PARTITION parta ,
309 PARTITION partB ,
310 PARTITION Partc ,
311 PARTITION PartD );
312 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
313 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
314 SELECT * FROM TableA;
315 a
316 1
317 10
318 11
319 12
320 2
321 3
322 4
323 5
324 6
325 7
326 8
327 9
328 # Test of ADD/COALESCE PARTITIONS
329 # expecting duplicate partition name
330 ALTER TABLE TableA ADD PARTITION
331 (PARTITION partA,
332 PARTITION Parta,
333 PARTITION PartA);
334 ERROR HY000: Duplicate partition name partA
335 ALTER TABLE TableA ADD PARTITION
336 (PARTITION partE,
337 PARTITION Partf,
338 PARTITION PartG);
339 SELECT * FROM TableA;
340 a
341 1
342 10
343 11
344 12
345 2
346 3
347 4
348 5
349 6
350 7
351 8
352 9
353 SHOW CREATE TABLE TableA;
354 Table Create Table
355 TableA CREATE TABLE `tablea` (
356 `a` int(11) DEFAULT NULL
357 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
358 PARTITION BY HASH (a)
359 (PARTITION parta ENGINE = ARCHIVE,
360 PARTITION partB ENGINE = ARCHIVE,
361 PARTITION Partc ENGINE = ARCHIVE,
362 PARTITION PartD ENGINE = ARCHIVE,
363 PARTITION partE ENGINE = ARCHIVE,
364 PARTITION Partf ENGINE = ARCHIVE,
365 PARTITION PartG ENGINE = ARCHIVE)
366 ALTER TABLE TableA COALESCE PARTITION 4;
367 SELECT * FROM TableA;
368 a
369 1
370 10
371 11
372 12
373 2
374 3
375 4
376 5
377 6
378 7
379 8
380 9
381 SHOW CREATE TABLE TableA;
382 Table Create Table
383 TableA CREATE TABLE `tablea` (
384 `a` int(11) DEFAULT NULL
385 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
386 PARTITION BY HASH (a)
387 (PARTITION parta ENGINE = ARCHIVE,
388 PARTITION partB ENGINE = ARCHIVE,
389 PARTITION Partc ENGINE = ARCHIVE)
390 # Test of REORGANIZE PARTITIONS
391 # Should not work on HASH/KEY
392 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
393 (PARTITION PARTA ,
394 PARTITION partc );
395 ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
396 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
397 (PARTITION partB ,
398 PARTITION parta );
399 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
400 ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
401 (PARTITION partB COMMENT="Previusly named parta",
402 PARTITION parta COMMENT="Previusly named partB");
403 SELECT * FROM TableA;
404 a
405 1
406 10
407 11
408 12
409 2
410 3
411 4
412 5
413 6
414 7
415 8
416 9
417 SHOW CREATE TABLE TableA;
418 Table Create Table
419 TableA CREATE TABLE `tablea` (
420 `a` int(11) DEFAULT NULL
421 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
422 PARTITION BY HASH (a)
423 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
424 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
425 PARTITION Partc ENGINE = ARCHIVE)
426 # Test of RENAME TABLE
427 RENAME TABLE TableA to TableB;
428 SELECT * FROM TableB;
429 a
430 1
431 10
432 11
433 12
434 2
435 3
436 4
437 5
438 6
439 7
440 8
441 9
442 RENAME TABLE TableB to TableA;
443 SELECT * FROM TableA;
444 a
445 1
446 10
447 11
448 12
449 2
450 3
451 4
452 5
453 6
454 7
455 8
456 9
457 # Checking name comparision Upper vs Lower case
458 # Error if lower_case_table_names != 0
459 # lower_case_table_names: 1
460 CREATE TABLE tablea (a INT)
461 ENGINE = 'Archive'
462 PARTITION BY HASH (a)
463 (PARTITION parta ,
464 PARTITION partB ,
465 PARTITION Partc ,
466 PARTITION PartD );
467 ERROR 42S01: Table 'tablea' already exists
468 SHOW TABLES;
469 Tables_in_mysql_test_db
470 tablea
471 RENAME TABLE TableA to tablea;
472 ERROR 42S01: Table 'tablea' already exists
473 RENAME TABLE tablea to TableA;
474 ERROR 42S01: Table 'tablea' already exists
475 SELECT * FROM tablea;
476 a
477 1
478 10
479 11
480 12
481 2
482 3
483 4
484 5
485 6
486 7
487 8
488 9
489 SHOW CREATE TABLE tablea;
490 Table Create Table
491 tablea CREATE TABLE `tablea` (
492 `a` int(11) DEFAULT NULL
493 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
494 PARTITION BY HASH (a)
495 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
496 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
497 PARTITION Partc ENGINE = ARCHIVE)
498 # Test of REMOVE PARTITIONING
499 ALTER TABLE TableA REMOVE PARTITIONING;
500 SELECT * FROM TableA;
501 a
502 1
503 10
504 11
505 12
506 2
507 3
508 4
509 5
510 6
511 7
512 8
513 9
514 SHOW CREATE TABLE TableA;
515 Table Create Table
516 TableA CREATE TABLE `tablea` (
517 `a` int(11) DEFAULT NULL
518 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
519 # Cleaning up after HASH PARTITIONING test
520 DROP TABLE TableA;
521 # 3.0 RANGE partitioning mgm
522 # Creating RANGE partitioned table
523 CREATE TABLE TableA (a INT)
524 ENGINE = 'Archive'
525 PARTITION BY RANGE (a)
526 (PARTITION parta VALUES LESS THAN (4) ,
527 PARTITION partB VALUES LESS THAN (7) ,
528 PARTITION Partc VALUES LESS THAN (10) ,
529 PARTITION PartD VALUES LESS THAN (13) );
530 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
531 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
532 SELECT * FROM TableA;
533 a
534 1
535 10
536 11
537 12
538 2
539 3
540 4
541 5
542 6
543 7
544 8
545 9
546 # Test of ADD/DROP PARTITIONS
547 # expecting duplicate partition name
548 ALTER TABLE TableA ADD PARTITION
549 (PARTITION partA VALUES LESS THAN (MAXVALUE));
550 ERROR HY000: Duplicate partition name partA
551 ALTER TABLE TableA ADD PARTITION
552 (PARTITION partE VALUES LESS THAN (16),
553 PARTITION Partf VALUES LESS THAN (19),
554 PARTITION PartG VALUES LESS THAN (22));
555 SELECT * FROM TableA;
556 a
557 1
558 10
559 11
560 12
561 2
562 3
563 4
564 5
565 6
566 7
567 8
568 9
569 SHOW CREATE TABLE TableA;
570 Table Create Table
571 TableA CREATE TABLE `tablea` (
572 `a` int(11) DEFAULT NULL
573 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
574 PARTITION BY RANGE (a)
575 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
576 PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
577 PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
578 PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
579 PARTITION partE VALUES LESS THAN (16) ENGINE = ARCHIVE,
580 PARTITION Partf VALUES LESS THAN (19) ENGINE = ARCHIVE,
581 PARTITION PartG VALUES LESS THAN (22) ENGINE = ARCHIVE)
582 ALTER TABLE TableA DROP PARTITION partE, PartG;
583 ALTER TABLE TableA DROP PARTITION Partf;
584 ALTER TABLE TableA ADD PARTITION
585 (PARTITION PartE VALUES LESS THAN (MAXVALUE));
586 SELECT * FROM TableA;
587 a
588 1
589 10
590 11
591 12
592 2
593 3
594 4
595 5
596 6
597 7
598 8
599 9
600 SHOW CREATE TABLE TableA;
601 Table Create Table
602 TableA CREATE TABLE `tablea` (
603 `a` int(11) DEFAULT NULL
604 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
605 PARTITION BY RANGE (a)
606 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
607 PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
608 PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
609 PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
610 PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE)
611 # Test of REORGANIZE PARTITIONS
612 # Error since it must reorganize a consecutive range
613 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
614 (PARTITION partB VALUES LESS THAN (3) ,
615 PARTITION parta VALUES LESS THAN (11) );
616 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
617 ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
618 (PARTITION partD VALUES LESS THAN (8)
619 COMMENT="Previously partB and partly Partc",
620 PARTITION partB VALUES LESS THAN (11)
621 COMMENT="Previously partly Partc and partly PartD",
622 PARTITION partC VALUES LESS THAN (MAXVALUE)
623 COMMENT="Previously partly PartD");
624 SELECT * FROM TableA;
625 a
626 1
627 10
628 11
629 12
630 2
631 3
632 4
633 5
634 6
635 7
636 8
637 9
638 SHOW CREATE TABLE TableA;
639 Table Create Table
640 TableA CREATE TABLE `tablea` (
641 `a` int(11) DEFAULT NULL
642 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
643 PARTITION BY RANGE (a)
644 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
645 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE,
646 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE,
647 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE)
648 # Test of RENAME TABLE
649 RENAME TABLE TableA to TableB;
650 SELECT * FROM TableB;
651 a
652 1
653 10
654 11
655 12
656 2
657 3
658 4
659 5
660 6
661 7
662 8
663 9
664 RENAME TABLE TableB to TableA;
665 SELECT * FROM TableA;
666 a
667 1
668 10
669 11
670 12
671 2
672 3
673 4
674 5
675 6
676 7
677 8
678 9
679 # Checking name comparision Upper vs Lower case
680 # Error if lower_case_table_names != 0
681 # lower_case_table_names: 1
682 CREATE TABLE tablea (a INT)
683 ENGINE = 'Archive'
684 PARTITION BY RANGE (a)
685 (PARTITION parta VALUES LESS THAN (4) ,
686 PARTITION partB VALUES LESS THAN (7) ,
687 PARTITION Partc VALUES LESS THAN (10) ,
688 PARTITION PartD VALUES LESS THAN (13) );
689 ERROR 42S01: Table 'tablea' already exists
690 SHOW TABLES;
691 Tables_in_mysql_test_db
692 tablea
693 RENAME TABLE TableA to tablea;
694 ERROR 42S01: Table 'tablea' already exists
695 RENAME TABLE tablea to TableA;
696 ERROR 42S01: Table 'tablea' already exists
697 SELECT * FROM tablea;
698 a
699 1
700 10
701 11
702 12
703 2
704 3
705 4
706 5
707 6
708 7
709 8
710 9
711 SHOW CREATE TABLE tablea;
712 Table Create Table
713 tablea CREATE TABLE `tablea` (
714 `a` int(11) DEFAULT NULL
715 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
716 PARTITION BY RANGE (a)
717 (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
718 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE,
719 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE,
720 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE)
721 # Test of REMOVE PARTITIONING
722 ALTER TABLE TableA REMOVE PARTITIONING;
723 SELECT * FROM TableA;
724 a
725 1
726 10
727 11
728 12
729 2
730 3
731 4
732 5
733 6
734 7
735 8
736 9
737 SHOW CREATE TABLE TableA;
738 Table Create Table
739 TableA CREATE TABLE `tablea` (
740 `a` int(11) DEFAULT NULL
741 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
742 # Cleaning up after RANGE PARTITIONING test
743 DROP TABLE TableA;
744 # 4.0 LIST partitioning mgm
745 # Creating LIST partitioned table
746 CREATE TABLE TableA (a INT)
747 ENGINE = 'Archive'
748 PARTITION BY LIST (a)
749 (PARTITION parta VALUES IN (1,8,9) ,
750 PARTITION partB VALUES IN (2,10,11) ,
751 PARTITION Partc VALUES IN (3,4,7) ,
752 PARTITION PartD VALUES IN (5,6,12) );
753 INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
754 INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
755 SELECT * FROM TableA;
756 a
757 1
758 10
759 11
760 12
761 2
762 3
763 4
764 5
765 6
766 7
767 8
768 9
769 # Test of ADD/DROP PARTITIONS
770 # expecting duplicate partition name
771 ALTER TABLE TableA ADD PARTITION
772 (PARTITION partA VALUES IN (0));
773 ERROR HY000: Duplicate partition name partA
774 ALTER TABLE TableA ADD PARTITION
775 (PARTITION partE VALUES IN (16),
776 PARTITION Partf VALUES IN (19),
777 PARTITION PartG VALUES IN (22));
778 SELECT * FROM TableA;
779 a
780 1
781 10
782 11
783 12
784 2
785 3
786 4
787 5
788 6
789 7
790 8
791 9
792 SHOW CREATE TABLE TableA;
793 Table Create Table
794 TableA CREATE TABLE `tablea` (
795 `a` int(11) DEFAULT NULL
796 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
797 PARTITION BY LIST (a)
798 (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
799 PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
800 PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
801 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
802 PARTITION partE VALUES IN (16) ENGINE = ARCHIVE,
803 PARTITION Partf VALUES IN (19) ENGINE = ARCHIVE,
804 PARTITION PartG VALUES IN (22) ENGINE = ARCHIVE)
805 ALTER TABLE TableA DROP PARTITION partE, PartG;
806 ALTER TABLE TableA DROP PARTITION Partf;
807 ALTER TABLE TableA ADD PARTITION
808 (PARTITION PartE VALUES IN (13));
809 SELECT * FROM TableA;
810 a
811 1
812 10
813 11
814 12
815 2
816 3
817 4
818 5
819 6
820 7
821 8
822 9
823 SHOW CREATE TABLE TableA;
824 Table Create Table
825 TableA CREATE TABLE `tablea` (
826 `a` int(11) DEFAULT NULL
827 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
828 PARTITION BY LIST (a)
829 (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
830 PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
831 PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
832 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
833 PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE)
834 # Test of REORGANIZE PARTITIONS
835 ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
836 (PARTITION Partc VALUES IN (1,7)
837 COMMENT = "Mix 1 of old parta and Partc",
838 PARTITION partF VALUES IN (3,9)
839 COMMENT = "Mix 2 of old parta and Partc",
840 PARTITION parta VALUES IN (4,8)
841 COMMENT = "Mix 3 of old parta and Partc");
842 ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
843 ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
844 (PARTITION Partc VALUES IN (1,7)
845 COMMENT = "Mix 1 of old parta and Partc",
846 PARTITION parta VALUES IN (3,9)
847 COMMENT = "Mix 2 of old parta and Partc",
848 PARTITION partB VALUES IN (4,8)
849 COMMENT = "Mix 3 of old parta and Partc");
850 SELECT * FROM TableA;
851 a
852 1
853 12
854 3
855 4
856 5
857 6
858 7
859 8
860 9
861 SHOW CREATE TABLE TableA;
862 Table Create Table
863 TableA CREATE TABLE `tablea` (
864 `a` int(11) DEFAULT NULL
865 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
866 PARTITION BY LIST (a)
867 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE,
868 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE,
869 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE,
870 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
871 PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE)
872 # Test of RENAME TABLE
873 RENAME TABLE TableA to TableB;
874 SELECT * FROM TableB;
875 a
876 1
877 12
878 3
879 4
880 5
881 6
882 7
883 8
884 9
885 RENAME TABLE TableB to TableA;
886 SELECT * FROM TableA;
887 a
888 1
889 12
890 3
891 4
892 5
893 6
894 7
895 8
896 9
897 # Checking name comparision Upper vs Lower case
898 # Error if lower_case_table_names != 0
899 # lower_case_table_names: 1
900 CREATE TABLE tablea (a INT)
901 ENGINE = 'Archive'
902 PARTITION BY LIST (a)
903 (PARTITION parta VALUES IN (1,8,9) ,
904 PARTITION partB VALUES IN (2,10,11) ,
905 PARTITION Partc VALUES IN (3,4,7) ,
906 PARTITION PartD VALUES IN (5,6,12) );
907 ERROR 42S01: Table 'tablea' already exists
908 SHOW TABLES;
909 Tables_in_mysql_test_db
910 tablea
911 RENAME TABLE TableA to tablea;
912 ERROR 42S01: Table 'tablea' already exists
913 RENAME TABLE tablea to TableA;
914 ERROR 42S01: Table 'tablea' already exists
915 SELECT * FROM tablea;
916 a
917 1
918 12
919 3
920 4
921 5
922 6
923 7
924 8
925 9
926 SHOW CREATE TABLE tablea;
927 Table Create Table
928 tablea CREATE TABLE `tablea` (
929 `a` int(11) DEFAULT NULL
930 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
931 PARTITION BY LIST (a)
932 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE,
933 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE,
934 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE,
935 PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
936 PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE)
937 # Test of REMOVE PARTITIONING
938 ALTER TABLE TableA REMOVE PARTITIONING;
939 SELECT * FROM TableA;
940 a
941 1
942 12
943 3
944 4
945 5
946 6
947 7
948 8
949 9
950 SHOW CREATE TABLE TableA;
951 Table Create Table
952 TableA CREATE TABLE `tablea` (
953 `a` int(11) DEFAULT NULL
954 ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
955 # Cleaning up after LIST PARTITIONING test
956 DROP TABLE TableA;
957 # Verify that TRUNCATE PARTITION gives error
958 CREATE TABLE t1
959 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
960 b VARCHAR(255))
961 ENGINE = 'Archive'
962 PARTITION BY KEY (a)
963 (PARTITION LT1000,
964 PARTITION LT2000,
965 PARTITION MAX);
966 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");
967 ALTER TABLE t1 TRUNCATE PARTITION MAX;
968 Got one of the listed errors
969 SELECT * FROM t1;
970 a b
971 1 First
972 1000 First in LT2000
973 1001 Second in LT2000
974 1999 Last in LT2000
975 2 Second
976 2000 First in MAX
977 2001 Second in MAX
978 999 Last in LT1000
979 # Cleaning up before exit
980 USE test;
981 DROP DATABASE MySQL_Test_DB;