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 = 'InnoDB' 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=InnoDB DEFAULT CHARSET=latin1 59 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) 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=InnoDB DEFAULT CHARSET=latin1 87 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 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=InnoDB DEFAULT CHARSET=latin1 115 PARTITION BY KEY (`a`) 116(PARTITION `parta` ENGINE = InnoDB, 117 PARTITION `partB` ENGINE = InnoDB, 118 PARTITION `Partc` ENGINE = InnoDB) 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=InnoDB 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=InnoDB DEFAULT CHARSET=latin1 159 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 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=InnoDB DEFAULT CHARSET=latin1 195 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 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: 2 233CREATE TABLE tablea (a INT) 234ENGINE = 'InnoDB' 235PARTITION BY KEY (a) 236(PARTITION parta , 237PARTITION partB , 238PARTITION Partc , 239PARTITION PartD ); 240ERROR 42S01: Table 'tablea' already exists 241SHOW TABLES; 242Tables_in_MySQL_Test_DB 243TableA 244RENAME TABLE TableA to tablea; 245ERROR 42S01: Table 'tablea' already exists 246RENAME TABLE tablea to TableA; 247ERROR 42S01: Table 'TableA' already exists 248SELECT * FROM tablea; 249a 2501 25110 25211 25312 2542 2553 2564 2575 2586 2597 2608 2619 262SHOW CREATE TABLE tablea; 263Table Create Table 264tablea CREATE TABLE `tablea` ( 265 `a` int(11) DEFAULT NULL 266) ENGINE=InnoDB DEFAULT CHARSET=latin1 267 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 272ALTER TABLE TableA REMOVE PARTITIONING; 273SELECT * FROM TableA; 274a 2751 27610 27711 27812 2792 2803 2814 2825 2836 2847 2858 2869 287SHOW CREATE TABLE TableA; 288Table Create Table 289TableA CREATE TABLE `TableA` ( 290 `a` int(11) DEFAULT NULL 291) ENGINE=InnoDB DEFAULT CHARSET=latin1 292# Cleaning up after KEY PARTITIONING test 293DROP TABLE TableA; 294# 2.0 HASH partitioning mgm 295# expecting duplicate partition name 296CREATE TABLE TableA (a INT) 297ENGINE = 'InnoDB' 298PARTITION BY HASH (a) 299(PARTITION parta , 300PARTITION partA , 301PARTITION Parta , 302PARTITION PartA ); 303ERROR HY000: Duplicate partition name partA 304# Creating Hash partitioned table 305CREATE TABLE TableA (a INT) 306ENGINE = 'InnoDB' 307PARTITION BY HASH (a) 308(PARTITION parta , 309PARTITION partB , 310PARTITION Partc , 311PARTITION PartD ); 312INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 313INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 314SELECT * FROM TableA; 315a 3161 31710 31811 31912 3202 3213 3224 3235 3246 3257 3268 3279 328# Test of ADD/COALESCE PARTITIONS 329# expecting duplicate partition name 330ALTER TABLE TableA ADD PARTITION 331(PARTITION partA, 332PARTITION Parta, 333PARTITION PartA); 334ERROR HY000: Duplicate partition name partA 335ALTER TABLE TableA ADD PARTITION 336(PARTITION partE, 337PARTITION Partf, 338PARTITION PartG); 339SELECT * FROM TableA; 340a 3411 34210 34311 34412 3452 3463 3474 3485 3496 3507 3518 3529 353SHOW CREATE TABLE TableA; 354Table Create Table 355TableA CREATE TABLE `TableA` ( 356 `a` int(11) DEFAULT NULL 357) ENGINE=InnoDB DEFAULT CHARSET=latin1 358 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) 366ALTER TABLE TableA COALESCE PARTITION 4; 367SELECT * FROM TableA; 368a 3691 37010 37111 37212 3732 3743 3754 3765 3776 3787 3798 3809 381SHOW CREATE TABLE TableA; 382Table Create Table 383TableA CREATE TABLE `TableA` ( 384 `a` int(11) DEFAULT NULL 385) ENGINE=InnoDB DEFAULT CHARSET=latin1 386 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 392ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO 393(PARTITION PARTA , 394PARTITION partc ); 395ERROR HY000: REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers 396ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 397(PARTITION partB , 398PARTITION parta ); 399ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 400ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO 401(PARTITION partB COMMENT="Previusly named parta", 402PARTITION parta COMMENT="Previusly named partB"); 403SELECT * FROM TableA; 404a 4051 40610 40711 40812 4092 4103 4114 4125 4136 4147 4158 4169 417SHOW CREATE TABLE TableA; 418Table Create Table 419TableA CREATE TABLE `TableA` ( 420 `a` int(11) DEFAULT NULL 421) ENGINE=InnoDB DEFAULT CHARSET=latin1 422 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 427RENAME TABLE TableA to TableB; 428SELECT * FROM TableB; 429a 4301 43110 43211 43312 4342 4353 4364 4375 4386 4397 4408 4419 442RENAME TABLE TableB to TableA; 443SELECT * FROM TableA; 444a 4451 44610 44711 44812 4492 4503 4514 4525 4536 4547 4558 4569 457# Checking name comparision Upper vs Lower case 458# Error if lower_case_table_names != 0 459# lower_case_table_names: 2 460CREATE TABLE tablea (a INT) 461ENGINE = 'InnoDB' 462PARTITION BY HASH (a) 463(PARTITION parta , 464PARTITION partB , 465PARTITION Partc , 466PARTITION PartD ); 467ERROR 42S01: Table 'tablea' already exists 468SHOW TABLES; 469Tables_in_MySQL_Test_DB 470TableA 471RENAME TABLE TableA to tablea; 472ERROR 42S01: Table 'tablea' already exists 473RENAME TABLE tablea to TableA; 474ERROR 42S01: Table 'TableA' already exists 475SELECT * FROM tablea; 476a 4771 47810 47911 48012 4812 4823 4834 4845 4856 4867 4878 4889 489SHOW CREATE TABLE tablea; 490Table Create Table 491tablea CREATE TABLE `tablea` ( 492 `a` int(11) DEFAULT NULL 493) ENGINE=InnoDB DEFAULT CHARSET=latin1 494 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 499ALTER TABLE TableA REMOVE PARTITIONING; 500SELECT * FROM TableA; 501a 5021 50310 50411 50512 5062 5073 5084 5095 5106 5117 5128 5139 514SHOW CREATE TABLE TableA; 515Table Create Table 516TableA CREATE TABLE `TableA` ( 517 `a` int(11) DEFAULT NULL 518) ENGINE=InnoDB DEFAULT CHARSET=latin1 519# Cleaning up after HASH PARTITIONING test 520DROP TABLE TableA; 521# 3.0 RANGE partitioning mgm 522# Creating RANGE partitioned table 523CREATE TABLE TableA (a INT) 524ENGINE = 'InnoDB' 525PARTITION BY RANGE (a) 526(PARTITION parta VALUES LESS THAN (4) , 527PARTITION partB VALUES LESS THAN (7) , 528PARTITION Partc VALUES LESS THAN (10) , 529PARTITION PartD VALUES LESS THAN (13) ); 530INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 531INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 532SELECT * FROM TableA; 533a 5341 53510 53611 53712 5382 5393 5404 5415 5426 5437 5448 5459 546# Test of ADD/DROP PARTITIONS 547# expecting duplicate partition name 548ALTER TABLE TableA ADD PARTITION 549(PARTITION partA VALUES LESS THAN (MAXVALUE)); 550ERROR HY000: Duplicate partition name partA 551ALTER TABLE TableA ADD PARTITION 552(PARTITION partE VALUES LESS THAN (16), 553PARTITION Partf VALUES LESS THAN (19), 554PARTITION PartG VALUES LESS THAN (22)); 555SELECT * FROM TableA; 556a 5571 55810 55911 56012 5612 5623 5634 5645 5656 5667 5678 5689 569SHOW CREATE TABLE TableA; 570Table Create Table 571TableA CREATE TABLE `TableA` ( 572 `a` int(11) DEFAULT NULL 573) ENGINE=InnoDB DEFAULT CHARSET=latin1 574 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) 582ALTER TABLE TableA DROP PARTITION partE, PartG; 583ALTER TABLE TableA DROP PARTITION Partf; 584ALTER TABLE TableA ADD PARTITION 585(PARTITION PartE VALUES LESS THAN (MAXVALUE)); 586SELECT * FROM TableA; 587a 5881 58910 59011 59112 5922 5933 5944 5955 5966 5977 5988 5999 600SHOW CREATE TABLE TableA; 601Table Create Table 602TableA CREATE TABLE `TableA` ( 603 `a` int(11) DEFAULT NULL 604) ENGINE=InnoDB DEFAULT CHARSET=latin1 605 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 613ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 614(PARTITION partB VALUES LESS THAN (3) , 615PARTITION parta VALUES LESS THAN (11) ); 616ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 617ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO 618(PARTITION partD VALUES LESS THAN (8) 619COMMENT="Previously partB and partly Partc", 620PARTITION partB VALUES LESS THAN (11) 621COMMENT="Previously partly Partc and partly PartD", 622PARTITION partC VALUES LESS THAN (MAXVALUE) 623COMMENT="Previously partly PartD"); 624SELECT * FROM TableA; 625a 6261 62710 62811 62912 6302 6313 6324 6335 6346 6357 6368 6379 638SHOW CREATE TABLE TableA; 639Table Create Table 640TableA CREATE TABLE `TableA` ( 641 `a` int(11) DEFAULT NULL 642) ENGINE=InnoDB DEFAULT CHARSET=latin1 643 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 649RENAME TABLE TableA to TableB; 650SELECT * FROM TableB; 651a 6521 65310 65411 65512 6562 6573 6584 6595 6606 6617 6628 6639 664RENAME TABLE TableB to TableA; 665SELECT * FROM TableA; 666a 6671 66810 66911 67012 6712 6723 6734 6745 6756 6767 6778 6789 679# Checking name comparision Upper vs Lower case 680# Error if lower_case_table_names != 0 681# lower_case_table_names: 2 682CREATE TABLE tablea (a INT) 683ENGINE = 'InnoDB' 684PARTITION BY RANGE (a) 685(PARTITION parta VALUES LESS THAN (4) , 686PARTITION partB VALUES LESS THAN (7) , 687PARTITION Partc VALUES LESS THAN (10) , 688PARTITION PartD VALUES LESS THAN (13) ); 689ERROR 42S01: Table 'tablea' already exists 690SHOW TABLES; 691Tables_in_MySQL_Test_DB 692TableA 693RENAME TABLE TableA to tablea; 694ERROR 42S01: Table 'tablea' already exists 695RENAME TABLE tablea to TableA; 696ERROR 42S01: Table 'TableA' already exists 697SELECT * FROM tablea; 698a 6991 70010 70111 70212 7032 7043 7054 7065 7076 7087 7098 7109 711SHOW CREATE TABLE tablea; 712Table Create Table 713tablea CREATE TABLE `tablea` ( 714 `a` int(11) DEFAULT NULL 715) ENGINE=InnoDB DEFAULT CHARSET=latin1 716 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 722ALTER TABLE TableA REMOVE PARTITIONING; 723SELECT * FROM TableA; 724a 7251 72610 72711 72812 7292 7303 7314 7325 7336 7347 7358 7369 737SHOW CREATE TABLE TableA; 738Table Create Table 739TableA CREATE TABLE `TableA` ( 740 `a` int(11) DEFAULT NULL 741) ENGINE=InnoDB DEFAULT CHARSET=latin1 742# Cleaning up after RANGE PARTITIONING test 743DROP TABLE TableA; 744# 4.0 LIST partitioning mgm 745# Creating LIST partitioned table 746CREATE TABLE TableA (a INT) 747ENGINE = 'InnoDB' 748PARTITION BY LIST (a) 749(PARTITION parta VALUES IN (1,8,9) , 750PARTITION partB VALUES IN (2,10,11) , 751PARTITION Partc VALUES IN (3,4,7) , 752PARTITION PartD VALUES IN (5,6,12) ); 753INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); 754INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); 755SELECT * FROM TableA; 756a 7571 75810 75911 76012 7612 7623 7634 7645 7656 7667 7678 7689 769# Test of ADD/DROP PARTITIONS 770# expecting duplicate partition name 771ALTER TABLE TableA ADD PARTITION 772(PARTITION partA VALUES IN (0)); 773ERROR HY000: Duplicate partition name partA 774ALTER TABLE TableA ADD PARTITION 775(PARTITION partE VALUES IN (16), 776PARTITION Partf VALUES IN (19), 777PARTITION PartG VALUES IN (22)); 778SELECT * FROM TableA; 779a 7801 78110 78211 78312 7842 7853 7864 7875 7886 7897 7908 7919 792SHOW CREATE TABLE TableA; 793Table Create Table 794TableA CREATE TABLE `TableA` ( 795 `a` int(11) DEFAULT NULL 796) ENGINE=InnoDB DEFAULT CHARSET=latin1 797 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) 805ALTER TABLE TableA DROP PARTITION partE, PartG; 806ALTER TABLE TableA DROP PARTITION Partf; 807ALTER TABLE TableA ADD PARTITION 808(PARTITION PartE VALUES IN (13)); 809SELECT * FROM TableA; 810a 8111 81210 81311 81412 8152 8163 8174 8185 8196 8207 8218 8229 823SHOW CREATE TABLE TableA; 824Table Create Table 825TableA CREATE TABLE `TableA` ( 826 `a` int(11) DEFAULT NULL 827) ENGINE=InnoDB DEFAULT CHARSET=latin1 828 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 835ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO 836(PARTITION Partc VALUES IN (1,7) 837COMMENT = "Mix 1 of old parta and Partc", 838PARTITION partF VALUES IN (3,9) 839COMMENT = "Mix 2 of old parta and Partc", 840PARTITION parta VALUES IN (4,8) 841COMMENT = "Mix 3 of old parta and Partc"); 842ERROR HY000: When reorganizing a set of partitions they must be in consecutive order 843ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO 844(PARTITION Partc VALUES IN (1,7) 845COMMENT = "Mix 1 of old parta and Partc", 846PARTITION parta VALUES IN (3,9) 847COMMENT = "Mix 2 of old parta and Partc", 848PARTITION partB VALUES IN (4,8) 849COMMENT = "Mix 3 of old parta and Partc"); 850SELECT * FROM TableA; 851a 8521 85312 8543 8554 8565 8576 8587 8598 8609 861SHOW CREATE TABLE TableA; 862Table Create Table 863TableA CREATE TABLE `TableA` ( 864 `a` int(11) DEFAULT NULL 865) ENGINE=InnoDB DEFAULT CHARSET=latin1 866 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 873RENAME TABLE TableA to TableB; 874SELECT * FROM TableB; 875a 8761 87712 8783 8794 8805 8816 8827 8838 8849 885RENAME TABLE TableB to TableA; 886SELECT * FROM TableA; 887a 8881 88912 8903 8914 8925 8936 8947 8958 8969 897# Checking name comparision Upper vs Lower case 898# Error if lower_case_table_names != 0 899# lower_case_table_names: 2 900CREATE TABLE tablea (a INT) 901ENGINE = 'InnoDB' 902PARTITION BY LIST (a) 903(PARTITION parta VALUES IN (1,8,9) , 904PARTITION partB VALUES IN (2,10,11) , 905PARTITION Partc VALUES IN (3,4,7) , 906PARTITION PartD VALUES IN (5,6,12) ); 907ERROR 42S01: Table 'tablea' already exists 908SHOW TABLES; 909Tables_in_MySQL_Test_DB 910TableA 911RENAME TABLE TableA to tablea; 912ERROR 42S01: Table 'tablea' already exists 913RENAME TABLE tablea to TableA; 914ERROR 42S01: Table 'TableA' already exists 915SELECT * FROM tablea; 916a 9171 91812 9193 9204 9215 9226 9237 9248 9259 926SHOW CREATE TABLE tablea; 927Table Create Table 928tablea CREATE TABLE `tablea` ( 929 `a` int(11) DEFAULT NULL 930) ENGINE=InnoDB DEFAULT CHARSET=latin1 931 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 938ALTER TABLE TableA REMOVE PARTITIONING; 939SELECT * FROM TableA; 940a 9411 94212 9433 9444 9455 9466 9477 9488 9499 950SHOW CREATE TABLE TableA; 951Table Create Table 952TableA CREATE TABLE `TableA` ( 953 `a` int(11) DEFAULT NULL 954) ENGINE=InnoDB DEFAULT CHARSET=latin1 955# Cleaning up after LIST PARTITIONING test 956DROP TABLE TableA; 957# Testing TRUNCATE PARTITION 958CREATE TABLE t1 959(a BIGINT AUTO_INCREMENT PRIMARY KEY, 960b VARCHAR(255)) 961ENGINE = 'InnoDB' 962PARTITION BY RANGE (a) 963(PARTITION LT1000 VALUES LESS THAN (1000), 964PARTITION LT2000 VALUES LESS THAN (2000), 965PARTITION MAX VALUES LESS THAN MAXVALUE); 966INSERT 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"); 967SHOW CREATE TABLE t1; 968Table Create Table 969t1 CREATE TABLE `t1` ( 970 `a` bigint(20) NOT NULL AUTO_INCREMENT, 971 `b` varchar(255) DEFAULT NULL, 972 PRIMARY KEY (`a`) 973) ENGINE=InnoDB AUTO_INCREMENT=2002 DEFAULT CHARSET=latin1 974 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) 978SELECT * FROM t1 ORDER BY a; 979a b 9801 First 9812 Second 982999 Last in LT1000 9831000 First in LT2000 9841001 Second in LT2000 9851999 Last in LT2000 9862000 First in MAX 9872001 Second in MAX 988ALTER TABLE t1 ANALYZE PARTITION MAX; 989Table Op Msg_type Msg_text 990mysql_test_db.t1 analyze status OK 991# Truncate without FLUSH 992ALTER TABLE t1 TRUNCATE PARTITION MAX; 993INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (1)"); 994SELECT * FROM t1 WHERE a >= 2000; 995a b 9962000 First after TRUNCATE MAX (1) 997# Truncate with FLUSH after 998ALTER TABLE t1 TRUNCATE PARTITION MAX; 999FLUSH TABLES; 1000INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (2)"); 1001SELECT * FROM t1 WHERE a >= 2000; 1002a b 10032000 First after TRUNCATE MAX (2) 1004# Truncate with FLUSH before 1005FLUSH TABLES; 1006ALTER TABLE t1 TRUNCATE PARTITION MAX; 1007INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (3)"); 1008SELECT * FROM t1 WHERE a >= 2000; 1009a b 10102000 First after TRUNCATE MAX (3) 1011# Truncate with FLUSH after INSERT 1012FLUSH TABLES; 1013ALTER TABLE t1 TRUNCATE PARTITION MAX; 1014INSERT INTO t1 VALUES (NULL, "First after TRUNCATE MAX (4)"); 1015SELECT * FROM t1 WHERE a >= 2000; 1016a b 10172000 First after TRUNCATE MAX (4) 1018# Truncate without FLUSH 1019ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1020INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (1)"); 1021SELECT * FROM t1 ORDER BY a; 1022a b 10231000 First in LT2000 10241001 Second in LT2000 10251999 Last in LT2000 10262000 First after TRUNCATE MAX (4) 10272001 First after TRUNCATE LT1000 (1) 1028# Truncate with FLUSH after 1029ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1030FLUSH TABLES; 1031INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (2)"); 1032SELECT * FROM t1 ORDER BY a; 1033a b 10341000 First in LT2000 10351001 Second in LT2000 10361999 Last in LT2000 10372000 First after TRUNCATE MAX (4) 10382001 First after TRUNCATE LT1000 (1) 10392002 First after TRUNCATE LT1000 (2) 1040# Truncate with FLUSH before 1041FLUSH TABLES; 1042ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1043INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (3)"); 1044SELECT * FROM t1 ORDER BY a; 1045a b 10461000 First in LT2000 10471001 Second in LT2000 10481999 Last in LT2000 10492000 First after TRUNCATE MAX (4) 10502001 First after TRUNCATE LT1000 (1) 10512002 First after TRUNCATE LT1000 (2) 10522003 First after TRUNCATE LT1000 (3) 1053# Truncate with FLUSH after INSERT 1054FLUSH TABLES; 1055ALTER TABLE t1 TRUNCATE PARTITION LT1000; 1056INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT1000 (4)"); 1057SELECT * FROM t1 ORDER BY a; 1058a b 10591000 First in LT2000 10601001 Second in LT2000 10611999 Last in LT2000 10622000 First after TRUNCATE MAX (4) 10632001 First after TRUNCATE LT1000 (1) 10642002 First after TRUNCATE LT1000 (2) 10652003 First after TRUNCATE LT1000 (3) 10662004 First after TRUNCATE LT1000 (4) 1067# Truncate without FLUSH 1068ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1069INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (1)"); 1070SELECT * FROM t1 ORDER BY a; 1071a b 10722000 First after TRUNCATE MAX (4) 10732001 First after TRUNCATE LT1000 (1) 10742002 First after TRUNCATE LT1000 (2) 10752003 First after TRUNCATE LT1000 (3) 10762004 First after TRUNCATE LT1000 (4) 10772005 First after TRUNCATE LT2000 (1) 1078# Truncate with FLUSH after 1079ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1080FLUSH TABLES; 1081INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (2)"); 1082SELECT * FROM t1 ORDER BY a; 1083a b 10842000 First after TRUNCATE MAX (4) 10852001 First after TRUNCATE LT1000 (1) 10862002 First after TRUNCATE LT1000 (2) 10872003 First after TRUNCATE LT1000 (3) 10882004 First after TRUNCATE LT1000 (4) 10892005 First after TRUNCATE LT2000 (1) 10902006 First after TRUNCATE LT2000 (2) 1091# Truncate with FLUSH before 1092FLUSH TABLES; 1093ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1094INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (3)"); 1095SELECT * FROM t1 ORDER BY a; 1096a b 10972000 First after TRUNCATE MAX (4) 10982001 First after TRUNCATE LT1000 (1) 10992002 First after TRUNCATE LT1000 (2) 11002003 First after TRUNCATE LT1000 (3) 11012004 First after TRUNCATE LT1000 (4) 11022005 First after TRUNCATE LT2000 (1) 11032006 First after TRUNCATE LT2000 (2) 11042007 First after TRUNCATE LT2000 (3) 1105# Truncate with FLUSH after INSERT 1106FLUSH TABLES; 1107ALTER TABLE t1 TRUNCATE PARTITION LT2000; 1108INSERT INTO t1 VALUES (NULL, "First after TRUNCATE LT2000 (4)"); 1109SELECT * FROM t1 ORDER BY a; 1110a b 11112000 First after TRUNCATE MAX (4) 11122001 First after TRUNCATE LT1000 (1) 11132002 First after TRUNCATE LT1000 (2) 11142003 First after TRUNCATE LT1000 (3) 11152004 First after TRUNCATE LT1000 (4) 11162005 First after TRUNCATE LT2000 (1) 11172006 First after TRUNCATE LT2000 (2) 11182007 First after TRUNCATE LT2000 (3) 11192008 First after TRUNCATE LT2000 (4) 1120DROP TABLE t1; 1121# Cleaning up before exit 1122USE test; 1123DROP DATABASE MySQL_Test_DB; 1124