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: 2
161
CREATE TABLE tablea (a INT)
168
ERROR 42S01: Table 'tablea' already exists
170
Tables_in_mysql_test_db
172
RENAME TABLE TableA to tablea;
173
ERROR 42S01: Table 'tablea' already exists
174
RENAME TABLE tablea to TableA;
175
ERROR 42S01: Table 'TableA' already exists
176
SELECT * FROM tablea;
190
SHOW CREATE TABLE tablea;
192
tablea CREATE TABLE `tablea` (
193
`a` int(11) DEFAULT NULL
194
) ENGINE=MyISAM DEFAULT CHARSET=latin1
195
/*!50100 PARTITION BY KEY (a)
196
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MyISAM,
197
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MyISAM,
198
PARTITION Partc ENGINE = MyISAM) */
199
# Test of REMOVE PARTITIONING
200
ALTER TABLE TableA REMOVE PARTITIONING;
201
SELECT * FROM TableA;
215
SHOW CREATE TABLE TableA;
217
TableA CREATE TABLE `TableA` (
218
`a` int(11) DEFAULT NULL
219
) ENGINE=MyISAM DEFAULT CHARSET=latin1
220
# Cleaning up after KEY PARTITIONING test
222
# 2.0 HASH partitioning mgm
223
# expecting duplicate partition name
224
CREATE TABLE TableA (a INT)
226
PARTITION BY HASH (a)
231
ERROR HY000: Duplicate partition name parta
232
# Creating Hash partitioned table
233
CREATE TABLE TableA (a INT)
235
PARTITION BY HASH (a)
240
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
241
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
242
SELECT * FROM TableA;
256
# Test of ADD/COALESCE PARTITIONS
257
# expecting duplicate partition name
258
ALTER TABLE TableA ADD PARTITION
262
ERROR HY000: Duplicate partition name parta
263
ALTER TABLE TableA ADD PARTITION
267
SELECT * FROM TableA;
281
SHOW CREATE TABLE TableA;
283
TableA CREATE TABLE `TableA` (
284
`a` int(11) DEFAULT NULL
285
) ENGINE=MyISAM DEFAULT CHARSET=latin1
286
/*!50100 PARTITION BY HASH (a)
287
(PARTITION parta ENGINE = MyISAM,
288
PARTITION partB ENGINE = MyISAM,
289
PARTITION Partc ENGINE = MyISAM,
290
PARTITION PartD ENGINE = MyISAM,
291
PARTITION partE ENGINE = MyISAM,
292
PARTITION Partf ENGINE = MyISAM,
293
PARTITION PartG ENGINE = MyISAM) */
294
ALTER TABLE TableA COALESCE PARTITION 4;
295
SELECT * FROM TableA;
309
SHOW CREATE TABLE TableA;
311
TableA CREATE TABLE `TableA` (
312
`a` int(11) DEFAULT NULL
313
) ENGINE=MyISAM DEFAULT CHARSET=latin1
314
/*!50100 PARTITION BY HASH (a)
315
(PARTITION parta ENGINE = MyISAM,
316
PARTITION partB ENGINE = MyISAM,
317
PARTITION Partc ENGINE = MyISAM) */
318
# Test of REORGANIZE PARTITIONS
319
# Should not work on HASH/KEY
320
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
323
ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers
324
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
327
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
328
ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO
329
(PARTITION partB COMMENT="Previusly named parta",
330
PARTITION parta COMMENT="Previusly named partB");
331
SELECT * FROM TableA;
345
SHOW CREATE TABLE TableA;
347
TableA CREATE TABLE `TableA` (
348
`a` int(11) DEFAULT NULL
349
) ENGINE=MyISAM DEFAULT CHARSET=latin1
350
/*!50100 PARTITION BY HASH (a)
351
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MyISAM,
352
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MyISAM,
353
PARTITION Partc ENGINE = MyISAM) */
354
# Test of RENAME TABLE
355
RENAME TABLE TableA to TableB;
356
SELECT * FROM TableB;
370
RENAME TABLE TableB to TableA;
371
SELECT * FROM TableA;
385
# Checking name comparision Upper vs Lower case
386
# Error if lower_case_table_names != 0
387
# lower_case_table_names: 2
388
CREATE TABLE tablea (a INT)
390
PARTITION BY HASH (a)
395
ERROR 42S01: Table 'tablea' already exists
397
Tables_in_mysql_test_db
399
RENAME TABLE TableA to tablea;
400
ERROR 42S01: Table 'tablea' already exists
401
RENAME TABLE tablea to TableA;
402
ERROR 42S01: Table 'TableA' already exists
403
SELECT * FROM tablea;
417
SHOW CREATE TABLE tablea;
419
tablea CREATE TABLE `tablea` (
420
`a` int(11) DEFAULT NULL
421
) ENGINE=MyISAM DEFAULT CHARSET=latin1
422
/*!50100 PARTITION BY HASH (a)
423
(PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MyISAM,
424
PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MyISAM,
425
PARTITION Partc ENGINE = MyISAM) */
426
# Test of REMOVE PARTITIONING
427
ALTER TABLE TableA REMOVE PARTITIONING;
428
SELECT * FROM TableA;
442
SHOW CREATE TABLE TableA;
444
TableA CREATE TABLE `TableA` (
445
`a` int(11) DEFAULT NULL
446
) ENGINE=MyISAM DEFAULT CHARSET=latin1
447
# Cleaning up after HASH PARTITIONING test
449
# 3.0 RANGE partitioning mgm
450
# Creating RANGE partitioned table
451
CREATE TABLE TableA (a INT)
453
PARTITION BY RANGE (a)
454
(PARTITION parta VALUES LESS THAN (4) ,
455
PARTITION partB VALUES LESS THAN (7) ,
456
PARTITION Partc VALUES LESS THAN (10) ,
457
PARTITION PartD VALUES LESS THAN (13) );
458
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
459
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
460
SELECT * FROM TableA;
474
# Test of ADD/DROP PARTITIONS
475
# expecting duplicate partition name
476
ALTER TABLE TableA ADD PARTITION
477
(PARTITION partA VALUES LESS THAN (MAXVALUE));
478
ERROR HY000: Duplicate partition name parta
479
ALTER TABLE TableA ADD PARTITION
480
(PARTITION partE VALUES LESS THAN (16),
481
PARTITION Partf VALUES LESS THAN (19),
482
PARTITION PartG VALUES LESS THAN (22));
483
SELECT * FROM TableA;
497
SHOW CREATE TABLE TableA;
499
TableA CREATE TABLE `TableA` (
500
`a` int(11) DEFAULT NULL
501
) ENGINE=MyISAM DEFAULT CHARSET=latin1
502
/*!50100 PARTITION BY RANGE (a)
503
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
504
PARTITION partB VALUES LESS THAN (7) ENGINE = MyISAM,
505
PARTITION Partc VALUES LESS THAN (10) ENGINE = MyISAM,
506
PARTITION PartD VALUES LESS THAN (13) ENGINE = MyISAM,
507
PARTITION partE VALUES LESS THAN (16) ENGINE = MyISAM,
508
PARTITION Partf VALUES LESS THAN (19) ENGINE = MyISAM,
509
PARTITION PartG VALUES LESS THAN (22) ENGINE = MyISAM) */
510
ALTER TABLE TableA DROP PARTITION partE, PartG;
511
ALTER TABLE TableA DROP PARTITION Partf;
512
ALTER TABLE TableA ADD PARTITION
513
(PARTITION PartE VALUES LESS THAN (MAXVALUE));
514
SELECT * FROM TableA;
528
SHOW CREATE TABLE TableA;
530
TableA CREATE TABLE `TableA` (
531
`a` int(11) DEFAULT NULL
532
) ENGINE=MyISAM DEFAULT CHARSET=latin1
533
/*!50100 PARTITION BY RANGE (a)
534
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
535
PARTITION partB VALUES LESS THAN (7) ENGINE = MyISAM,
536
PARTITION Partc VALUES LESS THAN (10) ENGINE = MyISAM,
537
PARTITION PartD VALUES LESS THAN (13) ENGINE = MyISAM,
538
PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
539
# Test of REORGANIZE PARTITIONS
540
# Error since it must reorganize a consecutive range
541
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
542
(PARTITION partB VALUES LESS THAN (3) ,
543
PARTITION parta VALUES LESS THAN (11) );
544
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
545
ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO
546
(PARTITION partD VALUES LESS THAN (8)
547
COMMENT="Previously partB and partly Partc",
548
PARTITION partB VALUES LESS THAN (11)
549
COMMENT="Previously partly Partc and partly PartD",
550
PARTITION partC VALUES LESS THAN (MAXVALUE)
551
COMMENT="Previously partly PartD");
552
SELECT * FROM TableA;
566
SHOW CREATE TABLE TableA;
568
TableA CREATE TABLE `TableA` (
569
`a` int(11) DEFAULT NULL
570
) ENGINE=MyISAM DEFAULT CHARSET=latin1
571
/*!50100 PARTITION BY RANGE (a)
572
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
573
PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MyISAM,
574
PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MyISAM,
575
PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MyISAM) */
576
# Test of RENAME TABLE
577
RENAME TABLE TableA to TableB;
578
SELECT * FROM TableB;
592
RENAME TABLE TableB to TableA;
593
SELECT * FROM TableA;
607
# Checking name comparision Upper vs Lower case
608
# Error if lower_case_table_names != 0
609
# lower_case_table_names: 2
610
CREATE TABLE tablea (a INT)
612
PARTITION BY RANGE (a)
613
(PARTITION parta VALUES LESS THAN (4) ,
614
PARTITION partB VALUES LESS THAN (7) ,
615
PARTITION Partc VALUES LESS THAN (10) ,
616
PARTITION PartD VALUES LESS THAN (13) );
617
ERROR 42S01: Table 'tablea' already exists
619
Tables_in_mysql_test_db
621
RENAME TABLE TableA to tablea;
622
ERROR 42S01: Table 'tablea' already exists
623
RENAME TABLE tablea to TableA;
624
ERROR 42S01: Table 'TableA' already exists
625
SELECT * FROM tablea;
639
SHOW CREATE TABLE tablea;
641
tablea CREATE TABLE `tablea` (
642
`a` int(11) DEFAULT NULL
643
) ENGINE=MyISAM DEFAULT CHARSET=latin1
644
/*!50100 PARTITION BY RANGE (a)
645
(PARTITION parta VALUES LESS THAN (4) ENGINE = MyISAM,
646
PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MyISAM,
647
PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MyISAM,
648
PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MyISAM) */
649
# Test of REMOVE PARTITIONING
650
ALTER TABLE TableA REMOVE PARTITIONING;
651
SELECT * FROM TableA;
665
SHOW CREATE TABLE TableA;
667
TableA CREATE TABLE `TableA` (
668
`a` int(11) DEFAULT NULL
669
) ENGINE=MyISAM DEFAULT CHARSET=latin1
670
# Cleaning up after RANGE PARTITIONING test
672
# 4.0 LIST partitioning mgm
673
# Creating LIST partitioned table
674
CREATE TABLE TableA (a INT)
676
PARTITION BY LIST (a)
677
(PARTITION parta VALUES IN (1,8,9) ,
678
PARTITION partB VALUES IN (2,10,11) ,
679
PARTITION Partc VALUES IN (3,4,7) ,
680
PARTITION PartD VALUES IN (5,6,12) );
681
INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10);
682
INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12);
683
SELECT * FROM TableA;
697
# Test of ADD/DROP PARTITIONS
698
# expecting duplicate partition name
699
ALTER TABLE TableA ADD PARTITION
700
(PARTITION partA VALUES IN (0));
701
ERROR HY000: Duplicate partition name parta
702
ALTER TABLE TableA ADD PARTITION
703
(PARTITION partE VALUES IN (16),
704
PARTITION Partf VALUES IN (19),
705
PARTITION PartG VALUES IN (22));
706
SELECT * FROM TableA;
720
SHOW CREATE TABLE TableA;
722
TableA CREATE TABLE `TableA` (
723
`a` int(11) DEFAULT NULL
724
) ENGINE=MyISAM DEFAULT CHARSET=latin1
725
/*!50100 PARTITION BY LIST (a)
726
(PARTITION parta VALUES IN (1,8,9) ENGINE = MyISAM,
727
PARTITION partB VALUES IN (2,10,11) ENGINE = MyISAM,
728
PARTITION Partc VALUES IN (3,4,7) ENGINE = MyISAM,
729
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
730
PARTITION partE VALUES IN (16) ENGINE = MyISAM,
731
PARTITION Partf VALUES IN (19) ENGINE = MyISAM,
732
PARTITION PartG VALUES IN (22) ENGINE = MyISAM) */
733
ALTER TABLE TableA DROP PARTITION partE, PartG;
734
ALTER TABLE TableA DROP PARTITION Partf;
735
ALTER TABLE TableA ADD PARTITION
736
(PARTITION PartE VALUES IN (13));
737
SELECT * FROM TableA;
751
SHOW CREATE TABLE TableA;
753
TableA CREATE TABLE `TableA` (
754
`a` int(11) DEFAULT NULL
755
) ENGINE=MyISAM DEFAULT CHARSET=latin1
756
/*!50100 PARTITION BY LIST (a)
757
(PARTITION parta VALUES IN (1,8,9) ENGINE = MyISAM,
758
PARTITION partB VALUES IN (2,10,11) ENGINE = MyISAM,
759
PARTITION Partc VALUES IN (3,4,7) ENGINE = MyISAM,
760
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
761
PARTITION PartE VALUES IN (13) ENGINE = MyISAM) */
762
# Test of REORGANIZE PARTITIONS
763
ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO
764
(PARTITION Partc VALUES IN (1,7)
765
COMMENT = "Mix 1 of old parta and Partc",
766
PARTITION partF VALUES IN (3,9)
767
COMMENT = "Mix 2 of old parta and Partc",
768
PARTITION parta VALUES IN (4,8)
769
COMMENT = "Mix 3 of old parta and Partc");
770
ERROR HY000: When reorganizing a set of partitions they must be in consecutive order
771
ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO
772
(PARTITION Partc VALUES IN (1,7)
773
COMMENT = "Mix 1 of old parta and Partc",
774
PARTITION parta VALUES IN (3,9)
775
COMMENT = "Mix 2 of old parta and Partc",
776
PARTITION partB VALUES IN (4,8)
777
COMMENT = "Mix 3 of old parta and Partc");
778
SELECT * FROM TableA;
789
SHOW CREATE TABLE TableA;
791
TableA CREATE TABLE `TableA` (
792
`a` int(11) DEFAULT NULL
793
) ENGINE=MyISAM 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 = MyISAM,
796
PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MyISAM,
797
PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MyISAM,
798
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
799
PARTITION PartE VALUES IN (13) ENGINE = MyISAM) */
800
# Test of RENAME TABLE
801
RENAME TABLE TableA to TableB;
802
SELECT * FROM TableB;
813
RENAME TABLE TableB to TableA;
814
SELECT * FROM TableA;
825
# Checking name comparision Upper vs Lower case
826
# Error if lower_case_table_names != 0
827
# lower_case_table_names: 2
828
CREATE TABLE tablea (a INT)
830
PARTITION BY LIST (a)
831
(PARTITION parta VALUES IN (1,8,9) ,
832
PARTITION partB VALUES IN (2,10,11) ,
833
PARTITION Partc VALUES IN (3,4,7) ,
834
PARTITION PartD VALUES IN (5,6,12) );
835
ERROR 42S01: Table 'tablea' already exists
837
Tables_in_mysql_test_db
839
RENAME TABLE TableA to tablea;
840
ERROR 42S01: Table 'tablea' already exists
841
RENAME TABLE tablea to TableA;
842
ERROR 42S01: Table 'TableA' already exists
843
SELECT * FROM tablea;
854
SHOW CREATE TABLE tablea;
856
tablea CREATE TABLE `tablea` (
857
`a` int(11) DEFAULT NULL
858
) ENGINE=MyISAM 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 = MyISAM,
861
PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MyISAM,
862
PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MyISAM,
863
PARTITION PartD VALUES IN (5,6,12) ENGINE = MyISAM,
864
PARTITION PartE VALUES IN (13) ENGINE = MyISAM) */
865
# Test of REMOVE PARTITIONING
866
ALTER TABLE TableA REMOVE PARTITIONING;
867
SELECT * FROM TableA;
878
SHOW CREATE TABLE TableA;
880
TableA CREATE TABLE `TableA` (
881
`a` int(11) DEFAULT NULL
882
) ENGINE=MyISAM DEFAULT CHARSET=latin1
883
# Cleaning up after LIST PARTITIONING test
885
# Cleaning up before exit
887
DROP DATABASE MySQL_Test_DB;