1
# Creating database MySQL_TEST_DB
2
CREATE DATABASE MySQL_Test_DB;
4
# 1.0 KEY partitioning mgm
5
# Creating KEY partitioned table
6
CREATE TABLE TableA (a INT)
13
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
14
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
29
# Test of ADD/COALESCE PARTITIONS
30
# expecting duplicate partition name
31
ALTER TABLE TableA ADD PARTITION
35
ERROR HY000: Duplicate partition name parta
36
ALTER TABLE TableA ADD PARTITION
54
SHOW CREATE TABLE TableA;
56
TableA CREATE TABLE `TableA` (
57
`a` int(11) DEFAULT NULL
58
) ENGINE=MyISAM DEFAULT CHARSET=latin1
59
/*!50100 PARTITION BY KEY (a)
60
(PARTITION parta ENGINE = MyISAM,
61
PARTITION partB ENGINE = MyISAM,
62
PARTITION Partc ENGINE = MyISAM,
63
PARTITION PartD ENGINE = MyISAM,
64
PARTITION partE ENGINE = MyISAM,
65
PARTITION Partf ENGINE = MyISAM,
66
PARTITION PartG ENGINE = MyISAM) */
67
ALTER TABLE TableA COALESCE PARTITION 4;
82
SHOW CREATE TABLE TableA;
84
TableA CREATE TABLE `TableA` (
85
`a` int(11) DEFAULT NULL
86
) ENGINE=MyISAM DEFAULT CHARSET=latin1
87
/*!50100 PARTITION BY KEY (a)
88
(PARTITION parta ENGINE = MyISAM,
89
PARTITION partB ENGINE = MyISAM,
90
PARTITION Partc ENGINE = MyISAM) */
91
# Test of REORGANIZE PARTITIONS
92
# Should not work on HASH/KEY
93
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
96
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
97
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
100
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
101
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
102
(PARTITION partB COMMENT="Previusly named parta",
103
PARTITION parta COMMENT="Previusly named partB");
104
SELECT * FROM TableA;
118
SHOW CREATE TABLE TableA;
120
TableA CREATE TABLE `TableA` (
121
`a` int(11) DEFAULT NULL
122
) ENGINE=MyISAM DEFAULT CHARSET=latin1
123
/*!50100 PARTITION BY KEY (a)
124
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MyISAM,
125
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MyISAM,
126
PARTITION Partc ENGINE = MyISAM) */
127
# Test of RENAME TABLE
128
RENAME TABLE TableA to TableB;
129
SELECT * FROM TableB;
143
RENAME TABLE TableB to TableA;
144
SELECT * FROM TableA;
158
# Checking name comparision Upper vs Lower case
159
# Error if lower_case_table_names != 0
160
# lower_case_table_names: 0
161
CREATE TABLE tablea (a INT)
168
INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
170
Tables_in_MySQL_Test_DB
173
RENAME TABLE TableA to tableA;
174
SELECT * FROM tablea;
182
SELECT * FROM tableA;
196
RENAME TABLE tableA to TableA;
197
SHOW CREATE TABLE tablea;
199
tablea CREATE TABLE `tablea` (
200
`a` int(11) DEFAULT NULL
201
) ENGINE=MyISAM DEFAULT CHARSET=latin1
202
/*!50100 PARTITION BY KEY (a)
203
(PARTITION parta ENGINE = MyISAM,
204
PARTITION partB ENGINE = MyISAM,
205
PARTITION Partc ENGINE = MyISAM,
206
PARTITION PartD ENGINE = MyISAM) */
208
# Test of REMOVE PARTITIONING
209
ALTER TABLE TableA REMOVE PARTITIONING;
210
SELECT * FROM TableA;
224
SHOW CREATE TABLE TableA;
226
TableA CREATE TABLE `TableA` (
227
`a` int(11) DEFAULT NULL
228
) ENGINE=MyISAM DEFAULT CHARSET=latin1
229
# Cleaning up after KEY PARTITIONING test
231
# 2.0 HASH partitioning mgm
232
# expecting duplicate partition name
233
CREATE TABLE TableA (a INT)
235
PARTITION BY HASH (a)
240
ERROR HY000: Duplicate partition name parta
241
# Creating Hash partitioned table
242
CREATE TABLE TableA (a INT)
244
PARTITION BY HASH (a)
249
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
250
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
251
SELECT * FROM TableA;
265
# Test of ADD/COALESCE PARTITIONS
266
# expecting duplicate partition name
267
ALTER TABLE TableA ADD PARTITION
271
ERROR HY000: Duplicate partition name parta
272
ALTER TABLE TableA ADD PARTITION
276
SELECT * FROM TableA;
290
SHOW CREATE TABLE TableA;
292
TableA CREATE TABLE `TableA` (
293
`a` int(11) DEFAULT NULL
294
) ENGINE=MyISAM DEFAULT CHARSET=latin1
295
/*!50100 PARTITION BY HASH (a)
296
(PARTITION parta ENGINE = MyISAM,
297
PARTITION partB ENGINE = MyISAM,
298
PARTITION Partc ENGINE = MyISAM,
299
PARTITION PartD ENGINE = MyISAM,
300
PARTITION partE ENGINE = MyISAM,
301
PARTITION Partf ENGINE = MyISAM,
302
PARTITION PartG ENGINE = MyISAM) */
303
ALTER TABLE TableA COALESCE PARTITION 4;
304
SELECT * FROM TableA;
318
SHOW CREATE TABLE TableA;
320
TableA CREATE TABLE `TableA` (
321
`a` int(11) DEFAULT NULL
322
) ENGINE=MyISAM DEFAULT CHARSET=latin1
323
/*!50100 PARTITION BY HASH (a)
324
(PARTITION parta ENGINE = MyISAM,
325
PARTITION partB ENGINE = MyISAM,
326
PARTITION Partc ENGINE = MyISAM) */
327
# Test of REORGANIZE PARTITIONS
328
# Should not work on HASH/KEY
329
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
332
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
333
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
336
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
337
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
338
(PARTITION partB COMMENT="Previusly named parta",
339
PARTITION parta COMMENT="Previusly named partB");
340
SELECT * FROM TableA;
354
SHOW CREATE TABLE TableA;
356
TableA CREATE TABLE `TableA` (
357
`a` int(11) DEFAULT NULL
358
) ENGINE=MyISAM DEFAULT CHARSET=latin1
359
/*!50100 PARTITION BY HASH (a)
360
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MyISAM,
361
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MyISAM,
362
PARTITION Partc ENGINE = MyISAM) */
363
# Test of RENAME TABLE
364
RENAME TABLE TableA to TableB;
365
SELECT * FROM TableB;
379
RENAME TABLE TableB to TableA;
380
SELECT * FROM TableA;
394
# Checking name comparision Upper vs Lower case
395
# Error if lower_case_table_names != 0
396
# lower_case_table_names: 0
397
CREATE TABLE tablea (a INT)
399
PARTITION BY HASH (a)
404
INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
406
Tables_in_MySQL_Test_DB
409
RENAME TABLE TableA to tableA;
410
SELECT * FROM tablea;
418
SELECT * FROM tableA;
432
RENAME TABLE tableA to TableA;
433
SHOW CREATE TABLE tablea;
435
tablea CREATE TABLE `tablea` (
436
`a` int(11) DEFAULT NULL
437
) ENGINE=MyISAM DEFAULT CHARSET=latin1
438
/*!50100 PARTITION BY HASH (a)
439
(PARTITION parta ENGINE = MyISAM,
440
PARTITION partB ENGINE = MyISAM,
441
PARTITION Partc ENGINE = MyISAM,
442
PARTITION PartD ENGINE = MyISAM) */
444
# Test of REMOVE PARTITIONING
445
ALTER TABLE TableA REMOVE PARTITIONING;
446
SELECT * FROM TableA;
460
SHOW CREATE TABLE TableA;
462
TableA CREATE TABLE `TableA` (
463
`a` int(11) DEFAULT NULL
464
) ENGINE=MyISAM DEFAULT CHARSET=latin1
465
# Cleaning up after HASH PARTITIONING test
467
# 3.0 RANGE partitioning mgm
468
# Creating RANGE partitioned table
469
CREATE TABLE TableA (a INT)
471
PARTITION BY RANGE (a)
472
(PARTITION parta VALUES LESS THAN (4) ,
473
PARTITION partB VALUES LESS THAN (7) ,
474
PARTITION Partc VALUES LESS THAN (10) ,
475
PARTITION PartD VALUES LESS THAN (13) );
476
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
477
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
478
SELECT * FROM TableA;
492
# Test of ADD/DROP PARTITIONS
493
# expecting duplicate partition name
494
ALTER TABLE TableA ADD PARTITION
495
(PARTITION partA VALUES LESS THAN (MAXVALUE));
496
ERROR HY000: Duplicate partition name parta
497
ALTER TABLE TableA ADD PARTITION
498
(PARTITION partE VALUES LESS THAN (16),
499
PARTITION Partf VALUES LESS THAN (19),
500
PARTITION PartG VALUES LESS THAN (22));
501
SELECT * FROM TableA;
515
SHOW CREATE TABLE TableA;
517
TableA CREATE TABLE `TableA` (
518
`a` int(11) DEFAULT NULL
519
) ENGINE=MyISAM DEFAULT CHARSET=latin1
520
/*!50100 PARTITION BY RANGE (a)
521
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
522
PARTITION partB VALUES LESS THAN (7) ENGINE = MyISAM,
523
PARTITION Partc VALUES LESS THAN (10) ENGINE = MyISAM,
524
PARTITION PartD VALUES LESS THAN (13) ENGINE = MyISAM,
525
PARTITION partE VALUES LESS THAN (16) ENGINE = MyISAM,
526
PARTITION Partf VALUES LESS THAN (19) ENGINE = MyISAM,
527
PARTITION PartG VALUES LESS THAN (22) ENGINE = MyISAM) */
528
ALTER TABLE TableA DROP PARTITION partE, PartG;
529
ALTER TABLE TableA DROP PARTITION Partf;
530
ALTER TABLE TableA ADD PARTITION
531
(PARTITION PartE VALUES LESS THAN (MAXVALUE));
532
SELECT * FROM TableA;
546
SHOW CREATE TABLE TableA;
548
TableA CREATE TABLE `TableA` (
549
`a` int(11) DEFAULT NULL
550
) ENGINE=MyISAM DEFAULT CHARSET=latin1
551
/*!50100 PARTITION BY RANGE (a)
552
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
553
PARTITION partB VALUES LESS THAN (7) ENGINE = MyISAM,
554
PARTITION Partc VALUES LESS THAN (10) ENGINE = MyISAM,
555
PARTITION PartD VALUES LESS THAN (13) ENGINE = MyISAM,
556
PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
557
# Test of REORGANIZE PARTITIONS
558
# Error since it must reorganize a consecutive range
559
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
560
(PARTITION partB VALUES LESS THAN (3) ,
561
PARTITION parta VALUES LESS THAN (11) );
562
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
563
ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
564
(PARTITION partD VALUES LESS THAN (8)
565
COMMENT="Previously partB and partly Partc",
566
PARTITION partB VALUES LESS THAN (11)
567
COMMENT="Previously partly Partc and partly PartD",
568
PARTITION partC VALUES LESS THAN (MAXVALUE)
569
COMMENT="Previously partly PartD");
570
SELECT * FROM TableA;
584
SHOW CREATE TABLE TableA;
586
TableA CREATE TABLE `TableA` (
587
`a` int(11) DEFAULT NULL
588
) ENGINE=MyISAM DEFAULT CHARSET=latin1
589
/*!50100 PARTITION BY RANGE (a)
590
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
591
PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MyISAM,
592
PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MyISAM,
593
PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MyISAM) */
594
# Test of RENAME TABLE
595
RENAME TABLE TableA to TableB;
596
SELECT * FROM TableB;
610
RENAME TABLE TableB to TableA;
611
SELECT * FROM TableA;
625
# Checking name comparision Upper vs Lower case
626
# Error if lower_case_table_names != 0
627
# lower_case_table_names: 0
628
CREATE TABLE tablea (a INT)
630
PARTITION BY RANGE (a)
631
(PARTITION parta VALUES LESS THAN (4) ,
632
PARTITION partB VALUES LESS THAN (7) ,
633
PARTITION Partc VALUES LESS THAN (10) ,
634
PARTITION PartD VALUES LESS THAN (13) );
635
INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
637
Tables_in_MySQL_Test_DB
640
RENAME TABLE TableA to tableA;
641
SELECT * FROM tablea;
649
SELECT * FROM tableA;
663
RENAME TABLE tableA to TableA;
664
SHOW CREATE TABLE tablea;
666
tablea CREATE TABLE `tablea` (
667
`a` int(11) DEFAULT NULL
668
) ENGINE=MyISAM DEFAULT CHARSET=latin1
669
/*!50100 PARTITION BY RANGE (a)
670
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
671
PARTITION partB VALUES LESS THAN (7) ENGINE = MyISAM,
672
PARTITION Partc VALUES LESS THAN (10) ENGINE = MyISAM,
673
PARTITION PartD VALUES LESS THAN (13) ENGINE = MyISAM) */
675
# Test of REMOVE PARTITIONING
676
ALTER TABLE TableA REMOVE PARTITIONING;
677
SELECT * FROM TableA;
691
SHOW CREATE TABLE TableA;
693
TableA CREATE TABLE `TableA` (
694
`a` int(11) DEFAULT NULL
695
) ENGINE=MyISAM DEFAULT CHARSET=latin1
696
# Cleaning up after RANGE PARTITIONING test
698
# 4.0 LIST partitioning mgm
699
# Creating LIST partitioned table
700
CREATE TABLE TableA (a INT)
702
PARTITION BY LIST (a)
703
(PARTITION parta VALUES IN (1,8,9) ,
704
PARTITION partB VALUES IN (2,10,11) ,
705
PARTITION Partc VALUES IN (3,4,7) ,
706
PARTITION PartD VALUES IN (5,6,12) );
707
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
708
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
709
SELECT * FROM TableA;
723
# Test of ADD/DROP PARTITIONS
724
# expecting duplicate partition name
725
ALTER TABLE TableA ADD PARTITION
726
(PARTITION partA VALUES IN (0));
727
ERROR HY000: Duplicate partition name parta
728
ALTER TABLE TableA ADD PARTITION
729
(PARTITION partE VALUES IN (16),
730
PARTITION Partf VALUES IN (19),
731
PARTITION PartG VALUES IN (22));
732
SELECT * FROM TableA;
746
SHOW CREATE TABLE TableA;
748
TableA CREATE TABLE `TableA` (
749
`a` int(11) DEFAULT NULL
750
) ENGINE=MyISAM DEFAULT CHARSET=latin1
751
/*!50100 PARTITION BY LIST (a)
752
(PARTITION parta VALUES IN (1,8,9) ENGINE = MyISAM,
753
PARTITION partB VALUES IN (2,10,11) ENGINE = MyISAM,
754
PARTITION Partc VALUES IN (3,4,7) ENGINE = MyISAM,
755
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
756
PARTITION partE VALUES IN (16) ENGINE = MyISAM,
757
PARTITION Partf VALUES IN (19) ENGINE = MyISAM,
758
PARTITION PartG VALUES IN (22) ENGINE = MyISAM) */
759
ALTER TABLE TableA DROP PARTITION partE, PartG;
760
ALTER TABLE TableA DROP PARTITION Partf;
761
ALTER TABLE TableA ADD PARTITION
762
(PARTITION PartE VALUES IN (13));
763
SELECT * FROM TableA;
777
SHOW CREATE TABLE TableA;
779
TableA CREATE TABLE `TableA` (
780
`a` int(11) DEFAULT NULL
781
) ENGINE=MyISAM DEFAULT CHARSET=latin1
782
/*!50100 PARTITION BY LIST (a)
783
(PARTITION parta VALUES IN (1,8,9) ENGINE = MyISAM,
784
PARTITION partB VALUES IN (2,10,11) ENGINE = MyISAM,
785
PARTITION Partc VALUES IN (3,4,7) ENGINE = MyISAM,
786
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
787
PARTITION PartE VALUES IN (13) ENGINE = MyISAM) */
788
# Test of REORGANIZE PARTITIONS
789
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
790
(PARTITION Partc VALUES IN (1,7)
791
COMMENT = "Mix 1 of old parta and Partc",
792
PARTITION partF VALUES IN (3,9)
793
COMMENT = "Mix 2 of old parta and Partc",
794
PARTITION parta VALUES IN (4,8)
795
COMMENT = "Mix 3 of old parta and Partc");
796
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
797
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
798
(PARTITION Partc VALUES IN (1,7)
799
COMMENT = "Mix 1 of old parta and Partc",
800
PARTITION parta VALUES IN (3,9)
801
COMMENT = "Mix 2 of old parta and Partc",
802
PARTITION partB VALUES IN (4,8)
803
COMMENT = "Mix 3 of old parta and Partc");
804
SELECT * FROM TableA;
815
SHOW CREATE TABLE TableA;
817
TableA CREATE TABLE `TableA` (
818
`a` int(11) DEFAULT NULL
819
) ENGINE=MyISAM DEFAULT CHARSET=latin1
820
/*!50100 PARTITION BY LIST (a)
821
(PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = MyISAM,
822
PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MyISAM,
823
PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MyISAM,
824
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
825
PARTITION PartE VALUES IN (13) ENGINE = MyISAM) */
826
# Test of RENAME TABLE
827
RENAME TABLE TableA to TableB;
828
SELECT * FROM TableB;
839
RENAME TABLE TableB to TableA;
840
SELECT * FROM TableA;
851
# Checking name comparision Upper vs Lower case
852
# Error if lower_case_table_names != 0
853
# lower_case_table_names: 0
854
CREATE TABLE tablea (a INT)
856
PARTITION BY LIST (a)
857
(PARTITION parta VALUES IN (1,8,9) ,
858
PARTITION partB VALUES IN (2,10,11) ,
859
PARTITION Partc VALUES IN (3,4,7) ,
860
PARTITION PartD VALUES IN (5,6,12) );
861
INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10);
863
Tables_in_MySQL_Test_DB
866
RENAME TABLE TableA to tableA;
867
SELECT * FROM tablea;
875
SELECT * FROM tableA;
886
RENAME TABLE tableA to TableA;
887
SHOW CREATE TABLE tablea;
889
tablea CREATE TABLE `tablea` (
890
`a` int(11) DEFAULT NULL
891
) ENGINE=MyISAM DEFAULT CHARSET=latin1
892
/*!50100 PARTITION BY LIST (a)
893
(PARTITION parta VALUES IN (1,8,9) ENGINE = MyISAM,
894
PARTITION partB VALUES IN (2,10,11) ENGINE = MyISAM,
895
PARTITION Partc VALUES IN (3,4,7) ENGINE = MyISAM,
896
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM) */
898
# Test of REMOVE PARTITIONING
899
ALTER TABLE TableA REMOVE PARTITIONING;
900
SELECT * FROM TableA;
911
SHOW CREATE TABLE TableA;
913
TableA CREATE TABLE `TableA` (
914
`a` int(11) DEFAULT NULL
915
) ENGINE=MyISAM DEFAULT CHARSET=latin1
916
# Cleaning up after LIST PARTITIONING test
918
# Cleaning up before exit
920
DROP DATABASE MySQL_Test_DB;