56
56
TableA CREATE TABLE `tablea` (
57
57
`a` int(11) DEFAULT NULL
58
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION parta ENGINE = ARCHIVE, PARTITION partB ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE, PARTITION PartD ENGINE = ARCHIVE, PARTITION partE ENGINE = ARCHIVE, PARTITION Partf ENGINE = ARCHIVE, PARTITION PartG ENGINE = ARCHIVE) */
58
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
59
/*!50100 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) */
59
67
ALTER TABLE TableA COALESCE PARTITION 4;
60
68
SELECT * FROM TableA;
76
84
TableA CREATE TABLE `tablea` (
77
85
`a` int(11) DEFAULT NULL
78
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION parta ENGINE = ARCHIVE, PARTITION partB ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
86
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
87
/*!50100 PARTITION BY KEY (a)
88
(PARTITION parta ENGINE = ARCHIVE,
89
PARTITION partB ENGINE = ARCHIVE,
90
PARTITION Partc ENGINE = ARCHIVE) */
79
91
# Test of REORGANIZE PARTITIONS
80
92
# Should not work on HASH/KEY
81
93
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
107
119
Table Create Table
108
120
TableA CREATE TABLE `tablea` (
109
121
`a` int(11) DEFAULT NULL
110
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
122
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
123
/*!50100 PARTITION BY KEY (a)
124
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
125
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
126
PARTITION Partc ENGINE = ARCHIVE) */
111
127
# Test of RENAME TABLE
112
128
RENAME TABLE TableA to TableB;
113
129
SELECT * FROM TableB;
175
191
Table Create Table
176
192
tablea CREATE TABLE `tablea` (
177
193
`a` int(11) DEFAULT NULL
178
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
194
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
195
/*!50100 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) */
179
199
# Test of REMOVE PARTITIONING
180
200
ALTER TABLE TableA REMOVE PARTITIONING;
181
201
SELECT * FROM TableA;
262
282
Table Create Table
263
283
TableA CREATE TABLE `tablea` (
264
284
`a` int(11) DEFAULT NULL
265
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION parta ENGINE = ARCHIVE, PARTITION partB ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE, PARTITION PartD ENGINE = ARCHIVE, PARTITION partE ENGINE = ARCHIVE, PARTITION Partf ENGINE = ARCHIVE, PARTITION PartG ENGINE = ARCHIVE) */
285
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
286
/*!50100 PARTITION BY HASH (a)
287
(PARTITION parta ENGINE = ARCHIVE,
288
PARTITION partB ENGINE = ARCHIVE,
289
PARTITION Partc ENGINE = ARCHIVE,
290
PARTITION PartD ENGINE = ARCHIVE,
291
PARTITION partE ENGINE = ARCHIVE,
292
PARTITION Partf ENGINE = ARCHIVE,
293
PARTITION PartG ENGINE = ARCHIVE) */
266
294
ALTER TABLE TableA COALESCE PARTITION 4;
267
295
SELECT * FROM TableA;
282
310
Table Create Table
283
311
TableA CREATE TABLE `tablea` (
284
312
`a` int(11) DEFAULT NULL
285
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION parta ENGINE = ARCHIVE, PARTITION partB ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
313
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
314
/*!50100 PARTITION BY HASH (a)
315
(PARTITION parta ENGINE = ARCHIVE,
316
PARTITION partB ENGINE = ARCHIVE,
317
PARTITION Partc ENGINE = ARCHIVE) */
286
318
# Test of REORGANIZE PARTITIONS
287
319
# Should not work on HASH/KEY
288
320
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
314
346
Table Create Table
315
347
TableA CREATE TABLE `tablea` (
316
348
`a` int(11) DEFAULT NULL
317
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
349
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
350
/*!50100 PARTITION BY HASH (a)
351
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE,
352
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE,
353
PARTITION Partc ENGINE = ARCHIVE) */
318
354
# Test of RENAME TABLE
319
355
RENAME TABLE TableA to TableB;
320
356
SELECT * FROM TableB;
382
418
Table Create Table
383
419
tablea CREATE TABLE `tablea` (
384
420
`a` int(11) DEFAULT NULL
385
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = ARCHIVE, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = ARCHIVE, PARTITION Partc ENGINE = ARCHIVE) */
421
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
422
/*!50100 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) */
386
426
# Test of REMOVE PARTITIONING
387
427
ALTER TABLE TableA REMOVE PARTITIONING;
388
428
SELECT * FROM TableA;
458
498
Table Create Table
459
499
TableA CREATE TABLE `tablea` (
460
500
`a` int(11) DEFAULT NULL
461
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE, PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE, PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE, PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE, PARTITION partE VALUES LESS THAN (16) ENGINE = ARCHIVE, PARTITION Partf VALUES LESS THAN (19) ENGINE = ARCHIVE, PARTITION PartG VALUES LESS THAN (22) ENGINE = ARCHIVE) */
501
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
502
/*!50100 PARTITION BY RANGE (a)
503
(PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
504
PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
505
PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
506
PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
507
PARTITION partE VALUES LESS THAN (16) ENGINE = ARCHIVE,
508
PARTITION Partf VALUES LESS THAN (19) ENGINE = ARCHIVE,
509
PARTITION PartG VALUES LESS THAN (22) ENGINE = ARCHIVE) */
462
510
ALTER TABLE TableA DROP PARTITION partE, PartG;
463
511
ALTER TABLE TableA DROP PARTITION Partf;
464
512
ALTER TABLE TableA ADD PARTITION
481
529
Table Create Table
482
530
TableA CREATE TABLE `tablea` (
483
531
`a` int(11) DEFAULT NULL
484
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE, PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE, PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE, PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE, PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE) */
532
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
533
/*!50100 PARTITION BY RANGE (a)
534
(PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
535
PARTITION partB VALUES LESS THAN (7) ENGINE = ARCHIVE,
536
PARTITION Partc VALUES LESS THAN (10) ENGINE = ARCHIVE,
537
PARTITION PartD VALUES LESS THAN (13) ENGINE = ARCHIVE,
538
PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = ARCHIVE) */
485
539
# Test of REORGANIZE PARTITIONS
486
540
# Error since it must reorganize a consecutive range
487
541
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
513
567
Table Create Table
514
568
TableA CREATE TABLE `tablea` (
515
569
`a` int(11) DEFAULT NULL
516
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE, PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE, PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE, PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE) */
570
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
571
/*!50100 PARTITION BY RANGE (a)
572
(PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
573
PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE,
574
PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE,
575
PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE) */
517
576
# Test of RENAME TABLE
518
577
RENAME TABLE TableA to TableB;
519
578
SELECT * FROM TableB;
581
640
Table Create Table
582
641
tablea CREATE TABLE `tablea` (
583
642
`a` int(11) DEFAULT NULL
584
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE, PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE, PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE, PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE) */
643
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
644
/*!50100 PARTITION BY RANGE (a)
645
(PARTITION parta VALUES LESS THAN (4) ENGINE = ARCHIVE,
646
PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = ARCHIVE,
647
PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = ARCHIVE,
648
PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = ARCHIVE) */
585
649
# Test of REMOVE PARTITIONING
586
650
ALTER TABLE TableA REMOVE PARTITIONING;
587
651
SELECT * FROM TableA;
657
721
Table Create Table
658
722
TableA CREATE TABLE `tablea` (
659
723
`a` int(11) DEFAULT NULL
660
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE, PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE, PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE, PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE, PARTITION partE VALUES IN (16) ENGINE = ARCHIVE, PARTITION Partf VALUES IN (19) ENGINE = ARCHIVE, PARTITION PartG VALUES IN (22) ENGINE = ARCHIVE) */
724
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
725
/*!50100 PARTITION BY LIST (a)
726
(PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
727
PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
728
PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
729
PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
730
PARTITION partE VALUES IN (16) ENGINE = ARCHIVE,
731
PARTITION Partf VALUES IN (19) ENGINE = ARCHIVE,
732
PARTITION PartG VALUES IN (22) ENGINE = ARCHIVE) */
661
733
ALTER TABLE TableA DROP PARTITION partE, PartG;
662
734
ALTER TABLE TableA DROP PARTITION Partf;
663
735
ALTER TABLE TableA ADD PARTITION
680
752
Table Create Table
681
753
TableA CREATE TABLE `tablea` (
682
754
`a` int(11) DEFAULT NULL
683
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE, PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE, PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE, PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE, PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
755
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
756
/*!50100 PARTITION BY LIST (a)
757
(PARTITION parta VALUES IN (1,8,9) ENGINE = ARCHIVE,
758
PARTITION partB VALUES IN (2,10,11) ENGINE = ARCHIVE,
759
PARTITION Partc VALUES IN (3,4,7) ENGINE = ARCHIVE,
760
PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
761
PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
684
762
# Test of REORGANIZE PARTITIONS
685
763
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
686
764
(PARTITION Partc VALUES IN (1,7)
712
790
Table Create Table
713
791
TableA CREATE TABLE `tablea` (
714
792
`a` int(11) DEFAULT NULL
715
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE, PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE, PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE, PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE, PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
793
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
794
/*!50100 PARTITION BY LIST (a)
795
(PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE,
796
PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE,
797
PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE,
798
PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
799
PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
716
800
# Test of RENAME TABLE
717
801
RENAME TABLE TableA to TableB;
718
802
SELECT * FROM TableB;
771
855
Table Create Table
772
856
tablea CREATE TABLE `tablea` (
773
857
`a` int(11) DEFAULT NULL
774
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE, PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE, PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE, PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE, PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
858
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
859
/*!50100 PARTITION BY LIST (a)
860
(PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = ARCHIVE,
861
PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = ARCHIVE,
862
PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = ARCHIVE,
863
PARTITION PartD VALUES IN (5,6,12) ENGINE = ARCHIVE,
864
PARTITION PartE VALUES IN (13) ENGINE = ARCHIVE) */
775
865
# Test of REMOVE PARTITIONING
776
866
ALTER TABLE TableA REMOVE PARTITIONING;
777
867
SELECT * FROM TableA;