Debian Codegraph @

mysql-test/suite/parts/r/partition_mgm_lc1_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: 1
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 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 DEFAULT NULL
266 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
267 /*!50100 PARTITION BY KEY (a)
268 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = InnoDB,
269 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = InnoDB,
270 PARTITION Partc ENGINE = InnoDB) */
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 DEFAULT NULL
291 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 = 'InnoDB'
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 = 'InnoDB'
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 DEFAULT NULL
357 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
358 /*!50100 PARTITION BY HASH (`a`)
359 (PARTITION parta ENGINE = InnoDB,
360 PARTITION partB ENGINE = InnoDB,
361 PARTITION Partc ENGINE = InnoDB,
362 PARTITION PartD ENGINE = InnoDB,
363 PARTITION partE ENGINE = InnoDB,
364 PARTITION Partf ENGINE = InnoDB,
365 PARTITION PartG ENGINE = InnoDB) */
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 DEFAULT NULL
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
386 /*!50100 PARTITION BY HASH (`a`)
387 (PARTITION parta ENGINE = InnoDB,
388 PARTITION partB ENGINE = InnoDB,
389 PARTITION Partc ENGINE = InnoDB) */
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 DEFAULT NULL
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
422 /*!50100 PARTITION BY HASH (`a`)
423 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = InnoDB,
424 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = InnoDB,
425 PARTITION Partc ENGINE = InnoDB) */
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 = 'InnoDB'
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 DEFAULT NULL
493 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
494 /*!50100 PARTITION BY HASH (`a`)
495 (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = InnoDB,
496 PARTITION parta COMMENT = 'Previusly named partB' ENGINE = InnoDB,
497 PARTITION Partc ENGINE = InnoDB) */
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 DEFAULT NULL
518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 = 'InnoDB'
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 DEFAULT NULL
573 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
574 /*!50100 PARTITION BY RANGE (`a`)
575 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
576 PARTITION partB VALUES LESS THAN (7) ENGINE = InnoDB,
577 PARTITION Partc VALUES LESS THAN (10) ENGINE = InnoDB,
578 PARTITION PartD VALUES LESS THAN (13) ENGINE = InnoDB,
579 PARTITION partE VALUES LESS THAN (16) ENGINE = InnoDB,
580 PARTITION Partf VALUES LESS THAN (19) ENGINE = InnoDB,
581 PARTITION PartG VALUES LESS THAN (22) ENGINE = InnoDB) */
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 DEFAULT NULL
604 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
605 /*!50100 PARTITION BY RANGE (`a`)
606 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
607 PARTITION partB VALUES LESS THAN (7) ENGINE = InnoDB,
608 PARTITION Partc VALUES LESS THAN (10) ENGINE = InnoDB,
609 PARTITION PartD VALUES LESS THAN (13) ENGINE = InnoDB,
610 PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
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 DEFAULT NULL
642 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
643 /*!50100 PARTITION BY RANGE (`a`)
644 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
645 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = InnoDB,
646 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = InnoDB,
647 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = InnoDB) */
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 = 'InnoDB'
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 DEFAULT NULL
715 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
716 /*!50100 PARTITION BY RANGE (`a`)
717 (PARTITION parta VALUES LESS THAN (4) ENGINE = InnoDB,
718 PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = InnoDB,
719 PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = InnoDB,
720 PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = InnoDB) */
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 DEFAULT NULL
741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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 = 'InnoDB'
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 DEFAULT NULL
796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
797 /*!50100 PARTITION BY LIST (`a`)
798 (PARTITION parta VALUES IN (1,8,9) ENGINE = InnoDB,
799 PARTITION partB VALUES IN (2,10,11) ENGINE = InnoDB,
800 PARTITION Partc VALUES IN (3,4,7) ENGINE = InnoDB,
801 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
802 PARTITION partE VALUES IN (16) ENGINE = InnoDB,
803 PARTITION Partf VALUES IN (19) ENGINE = InnoDB,
804 PARTITION PartG VALUES IN (22) ENGINE = InnoDB) */
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 DEFAULT NULL
827 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
828 /*!50100 PARTITION BY LIST (`a`)
829 (PARTITION parta VALUES IN (1,8,9) ENGINE = InnoDB,
830 PARTITION partB VALUES IN (2,10,11) ENGINE = InnoDB,
831 PARTITION Partc VALUES IN (3,4,7) ENGINE = InnoDB,
832 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
833 PARTITION PartE VALUES IN (13) ENGINE = InnoDB) */
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 DEFAULT NULL
865 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
866 /*!50100 PARTITION BY LIST (`a`)
867 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = InnoDB,
868 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = InnoDB,
869 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = InnoDB,
870 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
871 PARTITION PartE VALUES IN (13) ENGINE = InnoDB) */
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 = 'InnoDB'
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 DEFAULT NULL
930 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
931 /*!50100 PARTITION BY LIST (`a`)
932 (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = InnoDB,
933 PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = InnoDB,
934 PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = InnoDB,
935 PARTITION PartD VALUES IN (5,6,12) ENGINE = InnoDB,
936 PARTITION PartE VALUES IN (13) ENGINE = InnoDB) */
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 DEFAULT NULL
954 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
955 # Cleaning up after LIST PARTITIONING test
956 DROP TABLE TableA;
957 # Testing TRUNCATE PARTITION
958 CREATE TABLE t1
959 (a BIGINT AUTO_INCREMENT PRIMARY KEY,
960 b VARCHAR(255))
961 ENGINE = 'InnoDB'
962 PARTITION BY RANGE (a)
963 (PARTITION LT1000 VALUES LESS THAN (1000),
964 PARTITION LT2000 VALUES LESS THAN (2000),
965 PARTITION MAX VALUES LESS THAN MAXVALUE);
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 SHOW CREATE TABLE t1;
968 Table Create Table
969 t1 CREATE TABLE `t1` (
970 `a` bigint NOT NULL AUTO_INCREMENT,
971 `b` varchar(255) DEFAULT NULL,
972 PRIMARY KEY (`a`)
973 ) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
974 /*!50100 PARTITION BY RANGE (`a`)
975 (PARTITION LT1000 VALUES LESS THAN (1000) ENGINE = InnoDB,
976 PARTITION LT2000 VALUES LESS THAN (2000) ENGINE = InnoDB,
977 PARTITION MAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
978 SELECT * FROM t1 ORDER BY a;
979 a b
980 1 First
981 2 Second
982 999 Last in LT1000
983 1000 First in LT2000
984 1001 Second in LT2000
985 1999 Last in LT2000
986 2000 First in MAX
987 2001 Second in MAX
988 ALTER TABLE t1 ANALYZE PARTITION MAX;
989 Table Op Msg_type Msg_text
990 mysql_test_db.t1 analyze status OK
991 # Truncate without FLUSH
992 ALTER TABLE t1 TRUNCATE PARTITION MAX;
993 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)");
994 SELECT * FROM t1 WHERE a >= 2000;
995 a b
996 2002 First after TRUNCATE MAX (1)
997 # Truncate with FLUSH after
998 ALTER TABLE t1 TRUNCATE PARTITION MAX;
999 FLUSH TABLES;
1000 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)");
1001 SELECT * FROM t1 WHERE a >= 2000;
1002 a b
1003 2003 First after TRUNCATE MAX (2)
1004 # Truncate with FLUSH before
1005 FLUSH TABLES;
1006 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1007 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)");
1008 SELECT * FROM t1 WHERE a >= 2000;
1009 a b
1010 2004 First after TRUNCATE MAX (3)
1011 # Truncate with FLUSH after INSERT
1012 FLUSH TABLES;
1013 ALTER TABLE t1 TRUNCATE PARTITION MAX;
1014 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)");
1015 SELECT * FROM t1 WHERE a >= 2000;
1016 a b
1017 2005 First after TRUNCATE MAX (4)
1018 # Truncate without FLUSH
1019 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1020 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)");
1021 SELECT * FROM t1 ORDER BY a;
1022 a b
1023 1000 First in LT2000
1024 1001 Second in LT2000
1025 1999 Last in LT2000
1026 2005 First after TRUNCATE MAX (4)
1027 2006 First after TRUNCATE LT1000 (1)
1028 # Truncate with FLUSH after
1029 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1030 FLUSH TABLES;
1031 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)");
1032 SELECT * FROM t1 ORDER BY a;
1033 a b
1034 1000 First in LT2000
1035 1001 Second in LT2000
1036 1999 Last in LT2000
1037 2005 First after TRUNCATE MAX (4)
1038 2006 First after TRUNCATE LT1000 (1)
1039 2007 First after TRUNCATE LT1000 (2)
1040 # Truncate with FLUSH before
1041 FLUSH TABLES;
1042 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1043 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)");
1044 SELECT * FROM t1 ORDER BY a;
1045 a b
1046 1000 First in LT2000
1047 1001 Second in LT2000
1048 1999 Last in LT2000
1049 2005 First after TRUNCATE MAX (4)
1050 2006 First after TRUNCATE LT1000 (1)
1051 2007 First after TRUNCATE LT1000 (2)
1052 2008 First after TRUNCATE LT1000 (3)
1053 # Truncate with FLUSH after INSERT
1054 FLUSH TABLES;
1055 ALTER TABLE t1 TRUNCATE PARTITION LT1000;
1056 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)");
1057 SELECT * FROM t1 ORDER BY a;
1058 a b
1059 1000 First in LT2000
1060 1001 Second in LT2000
1061 1999 Last in LT2000
1062 2005 First after TRUNCATE MAX (4)
1063 2006 First after TRUNCATE LT1000 (1)
1064 2007 First after TRUNCATE LT1000 (2)
1065 2008 First after TRUNCATE LT1000 (3)
1066 2009 First after TRUNCATE LT1000 (4)
1067 # Truncate without FLUSH
1068 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1069 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)");
1070 SELECT * FROM t1 ORDER BY a;
1071 a b
1072 2005 First after TRUNCATE MAX (4)
1073 2006 First after TRUNCATE LT1000 (1)
1074 2007 First after TRUNCATE LT1000 (2)
1075 2008 First after TRUNCATE LT1000 (3)
1076 2009 First after TRUNCATE LT1000 (4)
1077 2010 First after TRUNCATE LT2000 (1)
1078 # Truncate with FLUSH after
1079 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1080 FLUSH TABLES;
1081 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)");
1082 SELECT * FROM t1 ORDER BY a;
1083 a b
1084 2005 First after TRUNCATE MAX (4)
1085 2006 First after TRUNCATE LT1000 (1)
1086 2007 First after TRUNCATE LT1000 (2)
1087 2008 First after TRUNCATE LT1000 (3)
1088 2009 First after TRUNCATE LT1000 (4)
1089 2010 First after TRUNCATE LT2000 (1)
1090 2011 First after TRUNCATE LT2000 (2)
1091 # Truncate with FLUSH before
1092 FLUSH TABLES;
1093 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1094 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)");
1095 SELECT * FROM t1 ORDER BY a;
1096 a b
1097 2005 First after TRUNCATE MAX (4)
1098 2006 First after TRUNCATE LT1000 (1)
1099 2007 First after TRUNCATE LT1000 (2)
1100 2008 First after TRUNCATE LT1000 (3)
1101 2009 First after TRUNCATE LT1000 (4)
1102 2010 First after TRUNCATE LT2000 (1)
1103 2011 First after TRUNCATE LT2000 (2)
1104 2012 First after TRUNCATE LT2000 (3)
1105 # Truncate with FLUSH after INSERT
1106 FLUSH TABLES;
1107 ALTER TABLE t1 TRUNCATE PARTITION LT2000;
1108 INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)");
1109 SELECT * FROM t1 ORDER BY a;
1110 a b
1111 2005 First after TRUNCATE MAX (4)
1112 2006 First after TRUNCATE LT1000 (1)
1113 2007 First after TRUNCATE LT1000 (2)
1114 2008 First after TRUNCATE LT1000 (3)
1115 2009 First after TRUNCATE LT1000 (4)
1116 2010 First after TRUNCATE LT2000 (1)
1117 2011 First after TRUNCATE LT2000 (2)
1118 2012 First after TRUNCATE LT2000 (3)
1119 2013 First after TRUNCATE LT2000 (4)
1120 DROP TABLE t1;
1121 # Cleaning up before exit
1122 USE test;
1123 DROP DATABASE MySQL_Test_DB;