# Creating database MySQL_TEST_DB CREATE DATABASE MySQL_Test_DB; USE MySQL_Test_DB; # 1.0 KEY partitioning mgm # Creating KEY partitioned table CREATE TABLE TableA (a INT) ENGINE = 'Memory' PARTITION BY KEY (a) (PARTITION parta , PARTITION partB , PARTITION Partc , PARTITION PartD ); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Test of ADD/COALESCE PARTITIONS # expecting duplicate partition name ALTER TABLE TableA ADD PARTITION (PARTITION partA, PARTITION Parta, PARTITION PartA); ERROR HY000: Duplicate partition name parta ALTER TABLE TableA ADD PARTITION (PARTITION partE, PARTITION Partf, PARTITION PartG); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY, PARTITION PartD ENGINE = MEMORY, PARTITION partE ENGINE = MEMORY, PARTITION Partf ENGINE = MEMORY, PARTITION PartG ENGINE = MEMORY) */ ALTER TABLE TableA COALESCE PARTITION 4; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY) */ # Test of REORGANIZE PARTITIONS # Should not work on HASH/KEY ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION PARTA , PARTITION partc ); ERROR HY000: REORGANISE PARTITION can only be used to reorganise partitions not to change their numbers ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB , PARTITION parta ); ERROR HY000: When reorganising a set of partitions they must be in consecutive order ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO (PARTITION partB COMMENT="Previusly named parta", PARTITION parta COMMENT="Previusly named partB"); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY) */ # Test of RENAME TABLE RENAME TABLE TableA to TableB; SELECT * FROM TableB; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE TableB to TableA; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Checking name comparision Upper vs Lower case # Error if lower_case_table_names != 0 # lower_case_table_names: 0 CREATE TABLE tablea (a INT) ENGINE = 'Memory' PARTITION BY KEY (a) (PARTITION parta , PARTITION partB , PARTITION Partc , PARTITION PartD ); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; Tables_in_MySQL_Test_DB TableA tablea RENAME TABLE TableA to tableA; SELECT * FROM tablea; a 1 10 2 7 8 9 SELECT * FROM tableA; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE tableA to TableA; SHOW CREATE TABLE tablea; Table Create Table tablea CREATE TABLE `tablea` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY, PARTITION PartD ENGINE = MEMORY) */ DROP TABLE tablea; # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after KEY PARTITIONING test DROP TABLE TableA; # 2.0 HASH partitioning mgm # expecting duplicate partition name CREATE TABLE TableA (a INT) ENGINE = 'Memory' PARTITION BY HASH (a) (PARTITION parta , PARTITION partA , PARTITION Parta , PARTITION PartA ); ERROR HY000: Duplicate partition name parta # Creating Hash partitioned table CREATE TABLE TableA (a INT) ENGINE = 'Memory' PARTITION BY HASH (a) (PARTITION parta , PARTITION partB , PARTITION Partc , PARTITION PartD ); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Test of ADD/COALESCE PARTITIONS # expecting duplicate partition name ALTER TABLE TableA ADD PARTITION (PARTITION partA, PARTITION Parta, PARTITION PartA); ERROR HY000: Duplicate partition name parta ALTER TABLE TableA ADD PARTITION (PARTITION partE, PARTITION Partf, PARTITION PartG); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY, PARTITION PartD ENGINE = MEMORY, PARTITION partE ENGINE = MEMORY, PARTITION Partf ENGINE = MEMORY, PARTITION PartG ENGINE = MEMORY) */ ALTER TABLE TableA COALESCE PARTITION 4; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY) */ # Test of REORGANIZE PARTITIONS # Should not work on HASH/KEY ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION PARTA , PARTITION partc ); ERROR HY000: REORGANISE PARTITION can only be used to reorganise partitions not to change their numbers ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB , PARTITION parta ); ERROR HY000: When reorganising a set of partitions they must be in consecutive order ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO (PARTITION partB COMMENT="Previusly named parta", PARTITION parta COMMENT="Previusly named partB"); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION partB COMMENT = 'Previusly named parta' ENGINE = MEMORY, PARTITION parta COMMENT = 'Previusly named partB' ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY) */ # Test of RENAME TABLE RENAME TABLE TableA to TableB; SELECT * FROM TableB; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE TableB to TableA; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Checking name comparision Upper vs Lower case # Error if lower_case_table_names != 0 # lower_case_table_names: 0 CREATE TABLE tablea (a INT) ENGINE = 'Memory' PARTITION BY HASH (a) (PARTITION parta , PARTITION partB , PARTITION Partc , PARTITION PartD ); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; Tables_in_MySQL_Test_DB TableA tablea RENAME TABLE TableA to tableA; SELECT * FROM tablea; a 1 10 2 7 8 9 SELECT * FROM tableA; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE tableA to TableA; SHOW CREATE TABLE tablea; Table Create Table tablea CREATE TABLE `tablea` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (a) (PARTITION parta ENGINE = MEMORY, PARTITION partB ENGINE = MEMORY, PARTITION Partc ENGINE = MEMORY, PARTITION PartD ENGINE = MEMORY) */ DROP TABLE tablea; # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after HASH PARTITIONING test DROP TABLE TableA; # 3.0 RANGE partitioning mgm # Creating RANGE partitioned table CREATE TABLE TableA (a INT) ENGINE = 'Memory' PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) , PARTITION partB VALUES LESS THAN (7) , PARTITION Partc VALUES LESS THAN (10) , PARTITION PartD VALUES LESS THAN (13) ); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Test of ADD/DROP PARTITIONS # expecting duplicate partition name ALTER TABLE TableA ADD PARTITION (PARTITION partA VALUES LESS THAN (MAXVALUE)); ERROR HY000: Duplicate partition name parta ALTER TABLE TableA ADD PARTITION (PARTITION partE VALUES LESS THAN (16), PARTITION Partf VALUES LESS THAN (19), PARTITION PartG VALUES LESS THAN (22)); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY, PARTITION partB VALUES LESS THAN (7) ENGINE = MEMORY, PARTITION Partc VALUES LESS THAN (10) ENGINE = MEMORY, PARTITION PartD VALUES LESS THAN (13) ENGINE = MEMORY, PARTITION partE VALUES LESS THAN (16) ENGINE = MEMORY, PARTITION Partf VALUES LESS THAN (19) ENGINE = MEMORY, PARTITION PartG VALUES LESS THAN (22) ENGINE = MEMORY) */ ALTER TABLE TableA DROP PARTITION partE, PartG; ALTER TABLE TableA DROP PARTITION Partf; ALTER TABLE TableA ADD PARTITION (PARTITION PartE VALUES LESS THAN (MAXVALUE)); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY, PARTITION partB VALUES LESS THAN (7) ENGINE = MEMORY, PARTITION Partc VALUES LESS THAN (10) ENGINE = MEMORY, PARTITION PartD VALUES LESS THAN (13) ENGINE = MEMORY, PARTITION PartE VALUES LESS THAN MAXVALUE ENGINE = MEMORY) */ # Test of REORGANIZE PARTITIONS # Error since it must reorganize a consecutive range ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB VALUES LESS THAN (3) , PARTITION parta VALUES LESS THAN (11) ); ERROR HY000: When reorganising a set of partitions they must be in consecutive order ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO (PARTITION partD VALUES LESS THAN (8) COMMENT="Previously partB and partly Partc", PARTITION partB VALUES LESS THAN (11) COMMENT="Previously partly Partc and partly PartD", PARTITION partC VALUES LESS THAN (MAXVALUE) COMMENT="Previously partly PartD"); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY, PARTITION partD VALUES LESS THAN (8) COMMENT = 'Previously partB and partly Partc' ENGINE = MEMORY, PARTITION partB VALUES LESS THAN (11) COMMENT = 'Previously partly Partc and partly PartD' ENGINE = MEMORY, PARTITION partC VALUES LESS THAN MAXVALUE COMMENT = 'Previously partly PartD' ENGINE = MEMORY) */ # Test of RENAME TABLE RENAME TABLE TableA to TableB; SELECT * FROM TableB; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE TableB to TableA; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Checking name comparision Upper vs Lower case # Error if lower_case_table_names != 0 # lower_case_table_names: 0 CREATE TABLE tablea (a INT) ENGINE = 'Memory' PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) , PARTITION partB VALUES LESS THAN (7) , PARTITION Partc VALUES LESS THAN (10) , PARTITION PartD VALUES LESS THAN (13) ); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; Tables_in_MySQL_Test_DB TableA tablea RENAME TABLE TableA to tableA; SELECT * FROM tablea; a 1 10 2 7 8 9 SELECT * FROM tableA; a 1 10 11 12 2 3 4 5 6 7 8 9 RENAME TABLE tableA to TableA; SHOW CREATE TABLE tablea; Table Create Table tablea CREATE TABLE `tablea` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) ENGINE = MEMORY, PARTITION partB VALUES LESS THAN (7) ENGINE = MEMORY, PARTITION Partc VALUES LESS THAN (10) ENGINE = MEMORY, PARTITION PartD VALUES LESS THAN (13) ENGINE = MEMORY) */ DROP TABLE tablea; # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after RANGE PARTITIONING test DROP TABLE TableA; # 4.0 LIST partitioning mgm # Creating LIST partitioned table CREATE TABLE TableA (a INT) ENGINE = 'Memory' PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) , PARTITION partB VALUES IN (2,10,11) , PARTITION Partc VALUES IN (3,4,7) , PARTITION PartD VALUES IN (5,6,12) ); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 # Test of ADD/DROP PARTITIONS # expecting duplicate partition name ALTER TABLE TableA ADD PARTITION (PARTITION partA VALUES IN (0)); ERROR HY000: Duplicate partition name parta ALTER TABLE TableA ADD PARTITION (PARTITION partE VALUES IN (16), PARTITION Partf VALUES IN (19), PARTITION PartG VALUES IN (22)); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) ENGINE = MEMORY, PARTITION partB VALUES IN (2,10,11) ENGINE = MEMORY, PARTITION Partc VALUES IN (3,4,7) ENGINE = MEMORY, PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY, PARTITION partE VALUES IN (16) ENGINE = MEMORY, PARTITION Partf VALUES IN (19) ENGINE = MEMORY, PARTITION PartG VALUES IN (22) ENGINE = MEMORY) */ ALTER TABLE TableA DROP PARTITION partE, PartG; ALTER TABLE TableA DROP PARTITION Partf; ALTER TABLE TableA ADD PARTITION (PARTITION PartE VALUES IN (13)); SELECT * FROM TableA; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) ENGINE = MEMORY, PARTITION partB VALUES IN (2,10,11) ENGINE = MEMORY, PARTITION Partc VALUES IN (3,4,7) ENGINE = MEMORY, PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY, PARTITION PartE VALUES IN (13) ENGINE = MEMORY) */ # Test of REORGANIZE PARTITIONS ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION Partc VALUES IN (1,7) COMMENT = "Mix 1 of old parta and Partc", PARTITION partF VALUES IN (3,9) COMMENT = "Mix 2 of old parta and Partc", PARTITION parta VALUES IN (4,8) COMMENT = "Mix 3 of old parta and Partc"); ERROR HY000: When reorganising a set of partitions they must be in consecutive order ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION Partc VALUES IN (1,7) COMMENT = "Mix 1 of old parta and Partc", PARTITION parta VALUES IN (3,9) COMMENT = "Mix 2 of old parta and Partc", PARTITION partB VALUES IN (4,8) COMMENT = "Mix 3 of old parta and Partc"); SELECT * FROM TableA; a 1 12 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION Partc VALUES IN (1,7) COMMENT = 'Mix 1 of old parta and Partc' ENGINE = MEMORY, PARTITION parta VALUES IN (3,9) COMMENT = 'Mix 2 of old parta and Partc' ENGINE = MEMORY, PARTITION partB VALUES IN (4,8) COMMENT = 'Mix 3 of old parta and Partc' ENGINE = MEMORY, PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY, PARTITION PartE VALUES IN (13) ENGINE = MEMORY) */ # Test of RENAME TABLE RENAME TABLE TableA to TableB; SELECT * FROM TableB; a 1 12 3 4 5 6 7 8 9 RENAME TABLE TableB to TableA; SELECT * FROM TableA; a 1 12 3 4 5 6 7 8 9 # Checking name comparision Upper vs Lower case # Error if lower_case_table_names != 0 # lower_case_table_names: 0 CREATE TABLE tablea (a INT) ENGINE = 'Memory' PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) , PARTITION partB VALUES IN (2,10,11) , PARTITION Partc VALUES IN (3,4,7) , PARTITION PartD VALUES IN (5,6,12) ); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; Tables_in_MySQL_Test_DB TableA tablea RENAME TABLE TableA to tableA; SELECT * FROM tablea; a 1 10 2 7 8 9 SELECT * FROM tableA; a 1 12 3 4 5 6 7 8 9 RENAME TABLE tableA to TableA; SHOW CREATE TABLE tablea; Table Create Table tablea CREATE TABLE `tablea` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) ENGINE = MEMORY, PARTITION partB VALUES IN (2,10,11) ENGINE = MEMORY, PARTITION Partc VALUES IN (3,4,7) ENGINE = MEMORY, PARTITION PartD VALUES IN (5,6,12) ENGINE = MEMORY) */ DROP TABLE tablea; # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; SELECT * FROM TableA; a 1 12 3 4 5 6 7 8 9 SHOW CREATE TABLE TableA; Table Create Table TableA CREATE TABLE `TableA` ( `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; # Cleaning up before exit USE test; DROP DATABASE MySQL_Test_DB;