1# Creating database MySQL_TEST_DB 2CREATE DATABASE MySQL_Test_DB; 3USE MySQL_Test_DB; 4# 1.0 KEY partitioning mgm 5# Creating KEY partitioned table 6CREATE TABLE TableA (a INT) 7ENGINE = 'Memory' 8PARTITION BY KEY (a) 9(PARTITION parta , 10PARTITION partB , 11PARTITION Partc , 12PARTITION PartD ); 13INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 14INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 15SELECT * FROM TableA; 16a 171 1810 1911 2012 212 223 234 245 256 267 278 289 29# Test of ADD/COALESCE PARTITIONS 30# expecting duplicate partition name 31ALTER TABLE TableA ADD PARTITION 32(PARTITION partA, 33PARTITION Parta, 34PARTITION PartA); 35ERROR HY000: Duplicate partition name partA 36ALTER TABLE TableA ADD PARTITION 37(PARTITION partE, 38PARTITION Partf, 39PARTITION PartG); 40SELECT * FROM TableA; 41a 421 4310 4411 4512 462 473 484 495 506 517 528 539 54SHOW CREATE TABLE TableA; 55Table Create Table 56TableA CREATE TABLE `TableA` ( 57 `a` int(11) DEFAULT NULL 58) ENGINE=MEMORY DEFAULT CHARSET=latin1 59 PARTITION BY KEY (`a`) 60(PARTITION `parta` ENGINE = MEMORY, 61 PARTITION `partB` ENGINE = MEMORY, 62 PARTITION `Partc` ENGINE = MEMORY, 63 PARTITION `PartD` ENGINE = MEMORY, 64 PARTITION `partE` ENGINE = MEMORY, 65 PARTITION `Partf` ENGINE = MEMORY, 66 PARTITION `PartG` ENGINE = MEMORY) 67ALTER TABLE TableA COALESCE PARTITION 4; 68SELECT * FROM TableA; 69a 701 7110 7211 7312 742 753 764 775 786 797 808 819 82SHOW CREATE TABLE TableA; 83Table Create Table 84TableA CREATE TABLE `TableA` ( 85 `a` int(11) DEFAULT NULL 86) ENGINE=MEMORY DEFAULT CHARSET=latin1 87 PARTITION BY KEY (`a`) 88(PARTITION `parta` ENGINE = MEMORY, 89 PARTITION `partB` ENGINE = MEMORY, 90 PARTITION `Partc` ENGINE = MEMORY) 91# Test of EXCHANGE PARTITION WITH TABLE 92SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='MySQL_Test_DB' AND TABLE_NAME = 'TableA'; 93PARTITION_NAME TABLE_ROWS 94parta 4 95partB 4 96Partc 4 97CREATE TABLE TableB LIKE TableA; 98ALTER TABLE TableB REMOVE PARTITIONING; 99ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB; 100SELECT * FROM TableA; 101a 1021 10312 1042 1053 1066 1077 1088 1099 110SHOW CREATE TABLE TableA; 111Table Create Table 112TableA CREATE TABLE `TableA` ( 113 `a` int(11) DEFAULT NULL 114) ENGINE=MEMORY DEFAULT CHARSET=latin1 115 PARTITION BY KEY (`a`) 116(PARTITION `parta` ENGINE = MEMORY, 117 PARTITION `partB` ENGINE = MEMORY, 118 PARTITION `Partc` ENGINE = MEMORY) 119SELECT * FROM TableB; 120a 12110 12211 1234 1245 125SHOW CREATE TABLE TableB; 126Table Create Table 127TableB CREATE TABLE `TableB` ( 128 `a` int(11) DEFAULT NULL 129) ENGINE=MEMORY DEFAULT CHARSET=latin1 130SELECT 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'; 131PARTITION_NAME HAVE_TABLE_ROWS 132parta NO 133partB YES 134Partc YES 135ALTER TABLE TableA EXCHANGE PARTITION parta WITH TABLE TableB; 136INSERT INTO TableB VALUES (11); 137ALTER TABLE TableA EXCHANGE PARTITION Partc WITH TABLE TableB; 138ERROR HY000: Found a row that does not match the partition 139DROP TABLE TableB; 140SELECT * FROM TableA; 141a 1421 14310 14411 14512 1462 1473 1484 1495 1506 1517 1528 1539 154SHOW CREATE TABLE TableA; 155Table Create Table 156TableA CREATE TABLE `TableA` ( 157 `a` int(11) DEFAULT NULL 158) ENGINE=MEMORY DEFAULT CHARSET=latin1 159 PARTITION BY KEY (`a`) 160(PARTITION `parta` ENGINE = MEMORY, 161 PARTITION `partB` ENGINE = MEMORY, 162 PARTITION `Partc` ENGINE = MEMORY) 163# Test of REORGANIZE PARTITIONS 164# Should not work on HASH/KEY 165ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO 166(PARTITION PARTA , 167PARTITION partc ); 168ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers 169ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 170(PARTITION partB , 171PARTITION parta ); 172ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 173ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO 174(PARTITION partB COMMENT="Previusly named parta", 175PARTITION parta COMMENT="Previusly named partB"); 176SELECT * FROM TableA; 177a 1781 17910 18011 18112 1822 1833 1844 1855 1866 1877 1888 1899 190SHOW CREATE TABLE TableA; 191Table Create Table 192TableA CREATE TABLE `TableA` ( 193 `a` int(11) DEFAULT NULL 194) ENGINE=MEMORY DEFAULT CHARSET=latin1 195 PARTITION BY KEY (`a`) 196(PARTITION `partB` COMMENT = 'Previusly named parta' ENGINE = MEMORY, 197 PARTITION `parta` COMMENT = 'Previusly named partB' ENGINE = MEMORY, 198 PARTITION `Partc` ENGINE = MEMORY) 199# Test of RENAME TABLE 200RENAME TABLE TableA to TableB; 201SELECT * FROM TableB; 202a 2031 20410 20511 20612 2072 2083 2094 2105 2116 2127 2138 2149 215RENAME TABLE TableB to TableA; 216SELECT * FROM TableA; 217a 2181 21910 22011 22112 2222 2233 2244 2255 2266 2277 2288 2299 230# Checking name comparision Upper vs Lower case 231# Error if lower_case_table_names != 0 232# lower_case_table_names: 0 233CREATE TABLE tablea (a INT) 234ENGINE = 'Memory' 235PARTITION BY KEY (a) 236(PARTITION parta , 237PARTITION partB , 238PARTITION Partc , 239PARTITION PartD ); 240INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); 241SHOW TABLES; 242Tables_in_MySQL_Test_DB 243TableA 244tablea 245RENAME TABLE TableA to tableA; 246SELECT * FROM tablea; 247a 2481 24910 2502 2517 2528 2539 254SELECT * FROM tableA; 255a 2561 25710 25811 25912 2602 2613 2624 2635 2646 2657 2668 2679 268RENAME TABLE tableA to TableA; 269SHOW CREATE TABLE tablea; 270Table Create Table 271tablea CREATE TABLE `tablea` ( 272 `a` int(11) DEFAULT NULL 273) ENGINE=MEMORY DEFAULT CHARSET=latin1 274 PARTITION BY KEY (`a`) 275(PARTITION `parta` ENGINE = MEMORY, 276 PARTITION `partB` ENGINE = MEMORY, 277 PARTITION `Partc` ENGINE = MEMORY, 278 PARTITION `PartD` ENGINE = MEMORY) 279DROP TABLE tablea; 280# Test of REMOVE PARTITIONING 281ALTER TABLE TableA REMOVE PARTITIONING; 282SELECT * FROM TableA; 283a 2841 28510 28611 28712 2882 2893 2904 2915 2926 2937 2948 2959 296SHOW CREATE TABLE TableA; 297Table Create Table 298TableA CREATE TABLE `TableA` ( 299 `a` int(11) DEFAULT NULL 300) ENGINE=MEMORY DEFAULT CHARSET=latin1 301# Cleaning up after KEY PARTITIONING test 302DROP TABLE TableA; 303# 2.0 HASH partitioning mgm 304# expecting duplicate partition name 305CREATE TABLE TableA (a INT) 306ENGINE = 'Memory' 307PARTITION BY HASH (a) 308(PARTITION parta , 309PARTITION partA , 310PARTITION Parta , 311PARTITION PartA ); 312ERROR HY000: Duplicate partition name partA 313# Creating Hash partitioned table 314CREATE TABLE TableA (a INT) 315ENGINE = 'Memory' 316PARTITION BY HASH (a) 317(PARTITION parta , 318PARTITION partB , 319PARTITION Partc , 320PARTITION PartD ); 321INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 322INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 323SELECT * FROM TableA; 324a 3251 32610 32711 32812 3292 3303 3314 3325 3336 3347 3358 3369 337# Test of ADD/COALESCE PARTITIONS 338# expecting duplicate partition name 339ALTER TABLE TableA ADD PARTITION 340(PARTITION partA, 341PARTITION Parta, 342PARTITION PartA); 343ERROR HY000: Duplicate partition name partA 344ALTER TABLE TableA ADD PARTITION 345(PARTITION partE, 346PARTITION Partf, 347PARTITION PartG); 348SELECT * FROM TableA; 349a 3501 35110 35211 35312 3542 3553 3564 3575 3586 3597 3608 3619 362SHOW CREATE TABLE TableA; 363Table Create Table 364TableA CREATE TABLE `TableA` ( 365 `a` int(11) DEFAULT NULL 366) ENGINE=MEMORY DEFAULT CHARSET=latin1 367 PARTITION BY HASH (`a`) 368(PARTITION `parta` ENGINE = MEMORY, 369 PARTITION `partB` ENGINE = MEMORY, 370 PARTITION `Partc` ENGINE = MEMORY, 371 PARTITION `PartD` ENGINE = MEMORY, 372 PARTITION `partE` ENGINE = MEMORY, 373 PARTITION `Partf` ENGINE = MEMORY, 374 PARTITION `PartG` ENGINE = MEMORY) 375ALTER TABLE TableA COALESCE PARTITION 4; 376SELECT * FROM TableA; 377a 3781 37910 38011 38112 3822 3833 3844 3855 3866 3877 3888 3899 390SHOW CREATE TABLE TableA; 391Table Create Table 392TableA CREATE TABLE `TableA` ( 393 `a` int(11) DEFAULT NULL 394) ENGINE=MEMORY DEFAULT CHARSET=latin1 395 PARTITION BY HASH (`a`) 396(PARTITION `parta` ENGINE = MEMORY, 397 PARTITION `partB` ENGINE = MEMORY, 398 PARTITION `Partc` ENGINE = MEMORY) 399# Test of REORGANIZE PARTITIONS 400# Should not work on HASH/KEY 401ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO 402(PARTITION PARTA , 403PARTITION partc ); 404ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers 405ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 406(PARTITION partB , 407PARTITION parta ); 408ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 409ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO 410(PARTITION partB COMMENT="Previusly named parta", 411PARTITION parta COMMENT="Previusly named partB"); 412SELECT * FROM TableA; 413a 4141 41510 41611 41712 4182 4193 4204 4215 4226 4237 4248 4259 426SHOW CREATE TABLE TableA; 427Table Create Table 428TableA CREATE TABLE `TableA` ( 429 `a` int(11) DEFAULT NULL 430) ENGINE=MEMORY DEFAULT CHARSET=latin1 431 PARTITION BY HASH (`a`) 432(PARTITION `partB` COMMENT = 'Previusly named parta' ENGINE = MEMORY, 433 PARTITION `parta` COMMENT = 'Previusly named partB' ENGINE = MEMORY, 434 PARTITION `Partc` ENGINE = MEMORY) 435# Test of RENAME TABLE 436RENAME TABLE TableA to TableB; 437SELECT * FROM TableB; 438a 4391 44010 44111 44212 4432 4443 4454 4465 4476 4487 4498 4509 451RENAME TABLE TableB to TableA; 452SELECT * FROM TableA; 453a 4541 45510 45611 45712 4582 4593 4604 4615 4626 4637 4648 4659 466# Checking name comparision Upper vs Lower case 467# Error if lower_case_table_names != 0 468# lower_case_table_names: 0 469CREATE TABLE tablea (a INT) 470ENGINE = 'Memory' 471PARTITION BY HASH (a) 472(PARTITION parta , 473PARTITION partB , 474PARTITION Partc , 475PARTITION PartD ); 476INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); 477SHOW TABLES; 478Tables_in_MySQL_Test_DB 479TableA 480tablea 481RENAME TABLE TableA to tableA; 482SELECT * FROM tablea; 483a 4841 48510 4862 4877 4888 4899 490SELECT * FROM tableA; 491a 4921 49310 49411 49512 4962 4973 4984 4995 5006 5017 5028 5039 504RENAME TABLE tableA to TableA; 505SHOW CREATE TABLE tablea; 506Table Create Table 507tablea CREATE TABLE `tablea` ( 508 `a` int(11) DEFAULT NULL 509) ENGINE=MEMORY DEFAULT CHARSET=latin1 510 PARTITION BY HASH (`a`) 511(PARTITION `parta` ENGINE = MEMORY, 512 PARTITION `partB` ENGINE = MEMORY, 513 PARTITION `Partc` ENGINE = MEMORY, 514 PARTITION `PartD` ENGINE = MEMORY) 515DROP TABLE tablea; 516# Test of REMOVE PARTITIONING 517ALTER TABLE TableA REMOVE PARTITIONING; 518SELECT * FROM TableA; 519a 5201 52110 52211 52312 5242 5253 5264 5275 5286 5297 5308 5319 532SHOW CREATE TABLE TableA; 533Table Create Table 534TableA CREATE TABLE `TableA` ( 535 `a` int(11) DEFAULT NULL 536) ENGINE=MEMORY DEFAULT CHARSET=latin1 537# Cleaning up after HASH PARTITIONING test 538DROP TABLE TableA; 539# 3.0 RANGE partitioning mgm 540# Creating RANGE partitioned table 541CREATE TABLE TableA (a INT) 542ENGINE = 'Memory' 543PARTITION BY RANGE (a) 544(PARTITION parta VALUES LESS THAN (4) , 545PARTITION partB VALUES LESS THAN (7) , 546PARTITION Partc VALUES LESS THAN (10) , 547PARTITION PartD VALUES LESS THAN (13) ); 548INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 549INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 550SELECT * FROM TableA; 551a 5521 55310 55411 55512 5562 5573 5584 5595 5606 5617 5628 5639 564# Test of ADD/DROP PARTITIONS 565# expecting duplicate partition name 566ALTER TABLE TableA ADD PARTITION 567(PARTITION partA VALUES LESS THAN (MAXVALUE)); 568ERROR HY000: Duplicate partition name partA 569ALTER TABLE TableA ADD PARTITION 570(PARTITION partE VALUES LESS THAN (16), 571PARTITION Partf VALUES LESS THAN (19), 572PARTITION PartG VALUES LESS THAN (22)); 573SELECT * FROM TableA; 574a 5751 57610 57711 57812 5792 5803 5814 5825 5836 5847 5858 5869 587SHOW CREATE TABLE TableA; 588Table Create Table 589TableA CREATE TABLE `TableA` ( 590 `a` int(11) DEFAULT NULL 591) ENGINE=MEMORY DEFAULT CHARSET=latin1 592 PARTITION BY RANGE (`a`) 593(PARTITION `parta` VALUES LESS THAN (4) ENGINE = MEMORY, 594 PARTITION `partB` VALUES LESS THAN (7) ENGINE = MEMORY, 595 PARTITION `Partc` VALUES LESS THAN (10) ENGINE = MEMORY, 596 PARTITION `PartD` VALUES LESS THAN (13) ENGINE = MEMORY, 597 PARTITION `partE` VALUES LESS THAN (16) ENGINE = MEMORY, 598 PARTITION `Partf` VALUES LESS THAN (19) ENGINE = MEMORY, 599 PARTITION `PartG` VALUES LESS THAN (22) ENGINE = MEMORY) 600ALTER TABLE TableA DROP PARTITION partE, PartG; 601ALTER TABLE TableA DROP PARTITION Partf; 602ALTER TABLE TableA ADD PARTITION 603(PARTITION PartE VALUES LESS THAN (MAXVALUE)); 604SELECT * FROM TableA; 605a 6061 60710 60811 60912 6102 6113 6124 6135 6146 6157 6168 6179 618SHOW CREATE TABLE TableA; 619Table Create Table 620TableA CREATE TABLE `TableA` ( 621 `a` int(11) DEFAULT NULL 622) ENGINE=MEMORY DEFAULT CHARSET=latin1 623 PARTITION BY RANGE (`a`) 624(PARTITION `parta` VALUES LESS THAN (4) ENGINE = MEMORY, 625 PARTITION `partB` VALUES LESS THAN (7) ENGINE = MEMORY, 626 PARTITION `Partc` VALUES LESS THAN (10) ENGINE = MEMORY, 627 PARTITION `PartD` VALUES LESS THAN (13) ENGINE = MEMORY, 628 PARTITION `PartE` VALUES LESS THAN MAXVALUE ENGINE = MEMORY) 629# Test of REORGANIZE PARTITIONS 630# Error since it must reorganize a consecutive range 631ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 632(PARTITION partB VALUES LESS THAN (3) , 633PARTITION parta VALUES LESS THAN (11) ); 634ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 635ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO 636(PARTITION partD VALUES LESS THAN (8) 637COMMENT="Previously partB and partly Partc", 638PARTITION partB VALUES LESS THAN (11) 639COMMENT="Previously partly Partc and partly PartD", 640PARTITION partC VALUES LESS THAN (MAXVALUE) 641COMMENT="Previously partly PartD"); 642SELECT * FROM TableA; 643a 6441 64510 64611 64712 6482 6493 6504 6515 6526 6537 6548 6559 656SHOW CREATE TABLE TableA; 657Table Create Table 658TableA CREATE TABLE `TableA` ( 659 `a` int(11) DEFAULT NULL 660) ENGINE=MEMORY DEFAULT CHARSET=latin1 661 PARTITION BY RANGE (`a`) 662(PARTITION `parta` VALUES LESS THAN (4) ENGINE = MEMORY, 663 PARTITION `partD` VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MEMORY, 664 PARTITION `partB` VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MEMORY, 665 PARTITION `partC` VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MEMORY) 666# Test of RENAME TABLE 667RENAME TABLE TableA to TableB; 668SELECT * FROM TableB; 669a 6701 67110 67211 67312 6742 6753 6764 6775 6786 6797 6808 6819 682RENAME TABLE TableB to TableA; 683SELECT * FROM TableA; 684a 6851 68610 68711 68812 6892 6903 6914 6925 6936 6947 6958 6969 697# Checking name comparision Upper vs Lower case 698# Error if lower_case_table_names != 0 699# lower_case_table_names: 0 700CREATE TABLE tablea (a INT) 701ENGINE = 'Memory' 702PARTITION BY RANGE (a) 703(PARTITION parta VALUES LESS THAN (4) , 704PARTITION partB VALUES LESS THAN (7) , 705PARTITION Partc VALUES LESS THAN (10) , 706PARTITION PartD VALUES LESS THAN (13) ); 707INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); 708SHOW TABLES; 709Tables_in_MySQL_Test_DB 710TableA 711tablea 712RENAME TABLE TableA to tableA; 713SELECT * FROM tablea; 714a 7151 71610 7172 7187 7198 7209 721SELECT * FROM tableA; 722a 7231 72410 72511 72612 7272 7283 7294 7305 7316 7327 7338 7349 735RENAME TABLE tableA to TableA; 736SHOW CREATE TABLE tablea; 737Table Create Table 738tablea CREATE TABLE `tablea` ( 739 `a` int(11) DEFAULT NULL 740) ENGINE=MEMORY DEFAULT CHARSET=latin1 741 PARTITION BY RANGE (`a`) 742(PARTITION `parta` VALUES LESS THAN (4) ENGINE = MEMORY, 743 PARTITION `partB` VALUES LESS THAN (7) ENGINE = MEMORY, 744 PARTITION `Partc` VALUES LESS THAN (10) ENGINE = MEMORY, 745 PARTITION `PartD` VALUES LESS THAN (13) ENGINE = MEMORY) 746DROP TABLE tablea; 747# Test of REMOVE PARTITIONING 748ALTER TABLE TableA REMOVE PARTITIONING; 749SELECT * FROM TableA; 750a 7511 75210 75311 75412 7552 7563 7574 7585 7596 7607 7618 7629 763SHOW CREATE TABLE TableA; 764Table Create Table 765TableA CREATE TABLE `TableA` ( 766 `a` int(11) DEFAULT NULL 767) ENGINE=MEMORY DEFAULT CHARSET=latin1 768# Cleaning up after RANGE PARTITIONING test 769DROP TABLE TableA; 770# 4.0 LIST partitioning mgm 771# Creating LIST partitioned table 772CREATE TABLE TableA (a INT) 773ENGINE = 'Memory' 774PARTITION BY LIST (a) 775(PARTITION parta VALUES IN (1,8,9) , 776PARTITION partB VALUES IN (2,10,11) , 777PARTITION Partc VALUES IN (3,4,7) , 778PARTITION PartD VALUES IN (5,6,12) ); 779INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 780INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 781SELECT * FROM TableA; 782a 7831 78410 78511 78612 7872 7883 7894 7905 7916 7927 7938 7949 795# Test of ADD/DROP PARTITIONS 796# expecting duplicate partition name 797ALTER TABLE TableA ADD PARTITION 798(PARTITION partA VALUES IN (0)); 799ERROR HY000: Duplicate partition name partA 800ALTER TABLE TableA ADD PARTITION 801(PARTITION partE VALUES IN (16), 802PARTITION Partf VALUES IN (19), 803PARTITION PartG VALUES IN (22)); 804SELECT * FROM TableA; 805a 8061 80710 80811 80912 8102 8113 8124 8135 8146 8157 8168 8179 818SHOW CREATE TABLE TableA; 819Table Create Table 820TableA CREATE TABLE `TableA` ( 821 `a` int(11) DEFAULT NULL 822) ENGINE=MEMORY DEFAULT CHARSET=latin1 823 PARTITION BY LIST (`a`) 824(PARTITION `parta` VALUES IN (1,8,9) ENGINE = MEMORY, 825 PARTITION `partB` VALUES IN (2,10,11) ENGINE = MEMORY, 826 PARTITION `Partc` VALUES IN (3,4,7) ENGINE = MEMORY, 827 PARTITION `PartD` VALUES IN (5,6,12) ENGINE = MEMORY, 828 PARTITION `partE` VALUES IN (16) ENGINE = MEMORY, 829 PARTITION `Partf` VALUES IN (19) ENGINE = MEMORY, 830 PARTITION `PartG` VALUES IN (22) ENGINE = MEMORY) 831ALTER TABLE TableA DROP PARTITION partE, PartG; 832ALTER TABLE TableA DROP PARTITION Partf; 833ALTER TABLE TableA ADD PARTITION 834(PARTITION PartE VALUES IN (13)); 835SELECT * FROM TableA; 836a 8371 83810 83911 84012 8412 8423 8434 8445 8456 8467 8478 8489 849SHOW CREATE TABLE TableA; 850Table Create Table 851TableA CREATE TABLE `TableA` ( 852 `a` int(11) DEFAULT NULL 853) ENGINE=MEMORY DEFAULT CHARSET=latin1 854 PARTITION BY LIST (`a`) 855(PARTITION `parta` VALUES IN (1,8,9) ENGINE = MEMORY, 856 PARTITION `partB` VALUES IN (2,10,11) ENGINE = MEMORY, 857 PARTITION `Partc` VALUES IN (3,4,7) ENGINE = MEMORY, 858 PARTITION `PartD` VALUES IN (5,6,12) ENGINE = MEMORY, 859 PARTITION `PartE` VALUES IN (13) ENGINE = MEMORY) 860# Test of REORGANIZE PARTITIONS 861ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 862(PARTITION Partc VALUES IN (1,7) 863COMMENT = "Mix 1 of old parta and Partc", 864PARTITION partF VALUES IN (3,9) 865COMMENT = "Mix 2 of old parta and Partc", 866PARTITION parta VALUES IN (4,8) 867COMMENT = "Mix 3 of old parta and Partc"); 868ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 869ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO 870(PARTITION Partc VALUES IN (1,7) 871COMMENT = "Mix 1 of old parta and Partc", 872PARTITION parta VALUES IN (3,9) 873COMMENT = "Mix 2 of old parta and Partc", 874PARTITION partB VALUES IN (4,8) 875COMMENT = "Mix 3 of old parta and Partc"); 876SELECT * FROM TableA; 877a 8781 87912 8803 8814 8825 8836 8847 8858 8869 887SHOW CREATE TABLE TableA; 888Table Create Table 889TableA CREATE TABLE `TableA` ( 890 `a` int(11) DEFAULT NULL 891) ENGINE=MEMORY DEFAULT CHARSET=latin1 892 PARTITION BY LIST (`a`) 893(PARTITION `Partc` VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = MEMORY, 894 PARTITION `parta` VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MEMORY, 895 PARTITION `partB` VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MEMORY, 896 PARTITION `PartD` VALUES IN (5,6,12) ENGINE = MEMORY, 897 PARTITION `PartE` VALUES IN (13) ENGINE = MEMORY) 898# Test of RENAME TABLE 899RENAME TABLE TableA to TableB; 900SELECT * FROM TableB; 901a 9021 90312 9043 9054 9065 9076 9087 9098 9109 911RENAME TABLE TableB to TableA; 912SELECT * FROM TableA; 913a 9141 91512 9163 9174 9185 9196 9207 9218 9229 923# Checking name comparision Upper vs Lower case 924# Error if lower_case_table_names != 0 925# lower_case_table_names: 0 926CREATE TABLE tablea (a INT) 927ENGINE = 'Memory' 928PARTITION BY LIST (a) 929(PARTITION parta VALUES IN (1,8,9) , 930PARTITION partB VALUES IN (2,10,11) , 931PARTITION Partc VALUES IN (3,4,7) , 932PARTITION PartD VALUES IN (5,6,12) ); 933INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); 934SHOW TABLES; 935Tables_in_MySQL_Test_DB 936TableA 937tablea 938RENAME TABLE TableA to tableA; 939SELECT * FROM tablea; 940a 9411 94210 9432 9447 9458 9469 947SELECT * FROM tableA; 948a 9491 95012 9513 9524 9535 9546 9557 9568 9579 958RENAME TABLE tableA to TableA; 959SHOW CREATE TABLE tablea; 960Table Create Table 961tablea CREATE TABLE `tablea` ( 962 `a` int(11) DEFAULT NULL 963) ENGINE=MEMORY DEFAULT CHARSET=latin1 964 PARTITION BY LIST (`a`) 965(PARTITION `parta` VALUES IN (1,8,9) ENGINE = MEMORY, 966 PARTITION `partB` VALUES IN (2,10,11) ENGINE = MEMORY, 967 PARTITION `Partc` VALUES IN (3,4,7) ENGINE = MEMORY, 968 PARTITION `PartD` VALUES IN (5,6,12) ENGINE = MEMORY) 969DROP TABLE tablea; 970# Test of REMOVE PARTITIONING 971ALTER TABLE TableA REMOVE PARTITIONING; 972SELECT * FROM TableA; 973a 9741 97512 9763 9774 9785 9796 9807 9818 9829 983SHOW CREATE TABLE TableA; 984Table Create Table 985TableA CREATE TABLE `TableA` ( 986 `a` int(11) DEFAULT NULL 987) ENGINE=MEMORY DEFAULT CHARSET=latin1 988# Cleaning up after LIST PARTITIONING test 989DROP TABLE TableA; 990# Testing TRUNCATE PARTITION 991CREATE TABLE t1 992(a BIGINT AUTO_INCREMENT PRIMARY KEY, 993b VARCHAR(255)) 994ENGINE = 'Memory' 995PARTITION BY RANGE (a) 996(PARTITION LT1000 VALUES LESS THAN (1000), 997PARTITION LT2000 VALUES LESS THAN (2000), 998PARTITION MAX VALUES LESS THAN MAXVALUE); 999INSERT 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"); 1000SHOW CREATE TABLE t1; 1001Table Create Table 1002t1 CREATE TABLE `t1` ( 1003 `a` bigint(20) NOT NULL AUTO_INCREMENT, 1004 `b` varchar(255) DEFAULT NULL, 1005 PRIMARY KEY (`a`) 1006) ENGINE=MEMORY AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 1007 PARTITION BY RANGE (`a`) 1008(PARTITION `LT1000` VALUES LESS THAN (1000) ENGINE = MEMORY, 1009 PARTITION `LT2000` VALUES LESS THAN (2000) ENGINE = MEMORY, 1010 PARTITION `MAX` VALUES LESS THAN MAXVALUE ENGINE = MEMORY) 1011SELECT * FROM t1 ORDER BY a; 1012a b 10131 First 10142 Second 1015999 Last in LT1000 10161000 First in LT2000 10171001 Second in LT2000 10181999 Last in LT2000 10192000 First in MAX 10202001 Second in MAX 1021ALTER TABLE t1 ANALYZE PARTITION MAX; 1022Table Op Msg_type Msg_text 1023MySQL_Test_DB.t1 analyze status Engine-independent statistics collected 1024MySQL_Test_DB.t1 analyze note The storage engine for the table doesn't support analyze 1025# Truncate without FLUSH 1026ALTER TABLE t1 TRUNCATE PARTITION MAX; 1027INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); 1028SELECT * FROM t1 WHERE a >= 2000; 1029a b 10302000 First after TRUNCATE MAX (1) 1031# Truncate with FLUSH after 1032ALTER TABLE t1 TRUNCATE PARTITION MAX; 1033FLUSH TABLES; 1034INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); 1035SELECT * FROM t1 WHERE a >= 2000; 1036a b 10372000 First after TRUNCATE MAX (2) 1038# Truncate with FLUSH before 1039FLUSH TABLES; 1040ALTER TABLE t1 TRUNCATE PARTITION MAX; 1041INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); 1042SELECT * FROM t1 WHERE a >= 2000; 1043a b 10442000 First after TRUNCATE MAX (3) 1045# Truncate with FLUSH after INSERT 1046FLUSH TABLES; 1047ALTER TABLE t1 TRUNCATE PARTITION MAX; 1048INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); 1049SELECT * FROM t1 WHERE a >= 2000; 1050a b 10512000 First after TRUNCATE MAX (4) 1052# Truncate without FLUSH 1053ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1054INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); 1055SELECT * FROM t1 ORDER BY a; 1056a b 10571000 First in LT2000 10581001 Second in LT2000 10591999 Last in LT2000 10602000 First after TRUNCATE MAX (4) 10612001 First after TRUNCATE LT1000 (1) 1062# Truncate with FLUSH after 1063ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1064FLUSH TABLES; 1065INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); 1066SELECT * FROM t1 ORDER BY a; 1067a b 10681000 First in LT2000 10691001 Second in LT2000 10701999 Last in LT2000 10712000 First after TRUNCATE MAX (4) 10722001 First after TRUNCATE LT1000 (1) 10732002 First after TRUNCATE LT1000 (2) 1074# Truncate with FLUSH before 1075FLUSH TABLES; 1076ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1077INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); 1078SELECT * FROM t1 ORDER BY a; 1079a b 10801000 First in LT2000 10811001 Second in LT2000 10821999 Last in LT2000 10832000 First after TRUNCATE MAX (4) 10842001 First after TRUNCATE LT1000 (1) 10852002 First after TRUNCATE LT1000 (2) 10862003 First after TRUNCATE LT1000 (3) 1087# Truncate with FLUSH after INSERT 1088FLUSH TABLES; 1089ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1090INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); 1091SELECT * FROM t1 ORDER BY a; 1092a b 10931000 First in LT2000 10941001 Second in LT2000 10951999 Last in LT2000 10962000 First after TRUNCATE MAX (4) 10972001 First after TRUNCATE LT1000 (1) 10982002 First after TRUNCATE LT1000 (2) 10992003 First after TRUNCATE LT1000 (3) 11002004 First after TRUNCATE LT1000 (4) 1101# Truncate without FLUSH 1102ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1103INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); 1104SELECT * FROM t1 ORDER BY a; 1105a b 11062000 First after TRUNCATE MAX (4) 11072001 First after TRUNCATE LT1000 (1) 11082002 First after TRUNCATE LT1000 (2) 11092003 First after TRUNCATE LT1000 (3) 11102004 First after TRUNCATE LT1000 (4) 11112005 First after TRUNCATE LT2000 (1) 1112# Truncate with FLUSH after 1113ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1114FLUSH TABLES; 1115INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); 1116SELECT * FROM t1 ORDER BY a; 1117a b 11182000 First after TRUNCATE MAX (4) 11192001 First after TRUNCATE LT1000 (1) 11202002 First after TRUNCATE LT1000 (2) 11212003 First after TRUNCATE LT1000 (3) 11222004 First after TRUNCATE LT1000 (4) 11232005 First after TRUNCATE LT2000 (1) 11242006 First after TRUNCATE LT2000 (2) 1125# Truncate with FLUSH before 1126FLUSH TABLES; 1127ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1128INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); 1129SELECT * FROM t1 ORDER BY a; 1130a b 11312000 First after TRUNCATE MAX (4) 11322001 First after TRUNCATE LT1000 (1) 11332002 First after TRUNCATE LT1000 (2) 11342003 First after TRUNCATE LT1000 (3) 11352004 First after TRUNCATE LT1000 (4) 11362005 First after TRUNCATE LT2000 (1) 11372006 First after TRUNCATE LT2000 (2) 11382007 First after TRUNCATE LT2000 (3) 1139# Truncate with FLUSH after INSERT 1140FLUSH TABLES; 1141ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1142INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); 1143SELECT * FROM t1 ORDER BY a; 1144a b 11452000 First after TRUNCATE MAX (4) 11462001 First after TRUNCATE LT1000 (1) 11472002 First after TRUNCATE LT1000 (2) 11482003 First after TRUNCATE LT1000 (3) 11492004 First after TRUNCATE LT1000 (4) 11502005 First after TRUNCATE LT2000 (1) 11512006 First after TRUNCATE LT2000 (2) 11522007 First after TRUNCATE LT2000 (3) 11532008 First after TRUNCATE LT2000 (4) 1154DROP TABLE t1; 1155# Cleaning up before exit 1156USE test; 1157DROP DATABASE MySQL_Test_DB; 1158