################################################################################ # inc/partition_mgm.inc # # # # Purpose: # # Test of partition management functions including different Upper/Lower # # case names of databases, tables and partitions # # # # # # Uses following variables: # # engine Use specified storage engine # # can_only_key Storage engine only able to use HASH/KEY (not range/list) # # (E.g. not ndbcluster) # # part_optA-D Extra partitioning options (E.g. INDEX/DATA DIR) # # # # have_bug33158 NDB case insensitive create, but case sensitive rename # # have_bug37719 Archive, crash when rename and then select # #------------------------------------------------------------------------------# # Original Author: mattiasj # # Original Date: 2008-06-27 # ################################################################################ --enable_abort_on_error let $old_db= `SELECT DATABASE()`; --echo # Creating database MySQL_TEST_DB CREATE DATABASE MySQL_Test_DB; USE MySQL_Test_DB; --echo # 1.0 KEY partitioning mgm --echo # Creating KEY partitioned table eval CREATE TABLE TableA (a INT) ENGINE = $engine PARTITION BY KEY (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); --sorted_result SELECT * FROM TableA; --echo # Test of ADD/COALESCE PARTITIONS --echo # expecting duplicate partition name --error ER_SAME_NAME_PARTITION ALTER TABLE TableA ADD PARTITION (PARTITION partA, PARTITION Parta, PARTITION PartA); ALTER TABLE TableA ADD PARTITION (PARTITION partE, PARTITION Partf, PARTITION PartG); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; ALTER TABLE TableA COALESCE PARTITION 4; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of REORGANIZE PARTITIONS --echo # Should not work on HASH/KEY --error ER_REORG_HASH_ONLY_ON_SAME_NO eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION PARTA $part_optA, PARTITION partc $part_optC); --error ER_CONSECUTIVE_REORG_PARTITIONS eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB $part_optA, PARTITION parta $part_optC); eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO (PARTITION partB $part_optA COMMENT="Previusly named parta", PARTITION parta $part_optB COMMENT="Previusly named partB"); if ($fixed_bug20129) { ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc; ALTER TABLE TableA CHECK PARTITION parta, partB, Partc; ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc; ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc; } --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of RENAME TABLE RENAME TABLE TableA to TableB; --sorted_result SELECT * FROM TableB; RENAME TABLE TableB to TableA; --sorted_result SELECT * FROM TableA; --echo # Checking name comparision Upper vs Lower case --echo # Error if lower_case_table_names != 0 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`; --echo # lower_case_table_names: $lower_case_table_names if ($lower_case_table_names) { --error ER_TABLE_EXISTS_ERROR eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY KEY (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); SHOW TABLES; --error ER_TABLE_EXISTS_ERROR RENAME TABLE TableA to tablea; --error ER_TABLE_EXISTS_ERROR RENAME TABLE tablea to TableA; --sorted_result SELECT * FROM tablea; SHOW CREATE TABLE tablea; } if (!$lower_case_table_names) { if (!$have_bug33158) { eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY KEY (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; if (!$have_bug37719) { RENAME TABLE TableA to tableA; } --sorted_result SELECT * FROM tablea; if (!$have_bug37719) { --sorted_result SELECT * FROM tableA; RENAME TABLE tableA to TableA; } SHOW CREATE TABLE tablea; DROP TABLE tablea; } } --echo # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Cleaning up after KEY PARTITIONING test DROP TABLE TableA; if (!$can_only_key) { --echo # 2.0 HASH partitioning mgm --echo # expecting duplicate partition name --error ER_SAME_NAME_PARTITION eval CREATE TABLE TableA (a INT) ENGINE = $engine PARTITION BY HASH (a) (PARTITION parta $part_optA, PARTITION partA $part_optB, PARTITION Parta $part_optC, PARTITION PartA $part_optD); --echo # Creating Hash partitioned table eval CREATE TABLE TableA (a INT) ENGINE = $engine PARTITION BY HASH (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); --sorted_result SELECT * FROM TableA; --echo # Test of ADD/COALESCE PARTITIONS --echo # expecting duplicate partition name --error ER_SAME_NAME_PARTITION ALTER TABLE TableA ADD PARTITION (PARTITION partA, PARTITION Parta, PARTITION PartA); ALTER TABLE TableA ADD PARTITION (PARTITION partE, PARTITION Partf, PARTITION PartG); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; ALTER TABLE TableA COALESCE PARTITION 4; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of REORGANIZE PARTITIONS --echo # Should not work on HASH/KEY --error ER_REORG_HASH_ONLY_ON_SAME_NO eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION PARTA $part_optA, PARTITION partc $part_optC); --error ER_CONSECUTIVE_REORG_PARTITIONS eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB $part_optA, PARTITION parta $part_optC); eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB INTO (PARTITION partB $part_optA COMMENT="Previusly named parta", PARTITION parta $part_optB COMMENT="Previusly named partB"); if ($fixed_bug20129) { ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc; ALTER TABLE TableA CHECK PARTITION parta, partB, Partc; ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc; ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc; } --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of RENAME TABLE RENAME TABLE TableA to TableB; --sorted_result SELECT * FROM TableB; RENAME TABLE TableB to TableA; --sorted_result SELECT * FROM TableA; --echo # Checking name comparision Upper vs Lower case --echo # Error if lower_case_table_names != 0 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`; --echo # lower_case_table_names: $lower_case_table_names if ($lower_case_table_names) { --error ER_TABLE_EXISTS_ERROR eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY HASH (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); SHOW TABLES; --error ER_TABLE_EXISTS_ERROR RENAME TABLE TableA to tablea; --error ER_TABLE_EXISTS_ERROR RENAME TABLE tablea to TableA; --sorted_result SELECT * FROM tablea; SHOW CREATE TABLE tablea; } if (!$lower_case_table_names) { eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY HASH (a) (PARTITION parta $part_optA, PARTITION partB $part_optB, PARTITION Partc $part_optC, PARTITION PartD $part_optD); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; if (!$have_bug37719) { RENAME TABLE TableA to tableA; } --sorted_result SELECT * FROM tablea; if (!$have_bug37719) { --sorted_result SELECT * FROM tableA; RENAME TABLE tableA to TableA; } SHOW CREATE TABLE tablea; DROP TABLE tablea; } --echo # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Cleaning up after HASH PARTITIONING test DROP TABLE TableA; --echo # 3.0 RANGE partitioning mgm --echo # Creating RANGE partitioned table eval CREATE TABLE TableA (a INT) ENGINE = $engine PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) $part_optA, PARTITION partB VALUES LESS THAN (7) $part_optB, PARTITION Partc VALUES LESS THAN (10) $part_optC, PARTITION PartD VALUES LESS THAN (13) $part_optD); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); --sorted_result SELECT * FROM TableA; --echo # Test of ADD/DROP PARTITIONS --echo # expecting duplicate partition name --error ER_SAME_NAME_PARTITION ALTER TABLE TableA ADD PARTITION (PARTITION partA VALUES LESS THAN (MAXVALUE)); ALTER TABLE TableA ADD PARTITION (PARTITION partE VALUES LESS THAN (16), PARTITION Partf VALUES LESS THAN (19), PARTITION PartG VALUES LESS THAN (22)); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; ALTER TABLE TableA DROP PARTITION partE, PartG; ALTER TABLE TableA DROP PARTITION Partf; ALTER TABLE TableA ADD PARTITION (PARTITION PartE VALUES LESS THAN (MAXVALUE)); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of REORGANIZE PARTITIONS --echo # Error since it must reorganize a consecutive range --error ER_CONSECUTIVE_REORG_PARTITIONS eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION partB VALUES LESS THAN (3) $part_optA, PARTITION parta VALUES LESS THAN (11) $part_optC); eval ALTER TABLE TableA REORGANIZE PARTITION partB,Partc,PartD,PartE INTO (PARTITION partD VALUES LESS THAN (8) $part_optB COMMENT="Previously partB and partly Partc", PARTITION partB VALUES LESS THAN (11) $part_optC COMMENT="Previously partly Partc and partly PartD", PARTITION partC VALUES LESS THAN (MAXVALUE) $part_optD COMMENT="Previously partly PartD"); if ($fixed_bug20129) { ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc; ALTER TABLE TableA CHECK PARTITION parta, partB, Partc; ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc; ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc; } --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of RENAME TABLE RENAME TABLE TableA to TableB; --sorted_result SELECT * FROM TableB; RENAME TABLE TableB to TableA; --sorted_result SELECT * FROM TableA; --echo # Checking name comparision Upper vs Lower case --echo # Error if lower_case_table_names != 0 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`; --echo # lower_case_table_names: $lower_case_table_names if ($lower_case_table_names) { --error ER_TABLE_EXISTS_ERROR eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) $part_optA, PARTITION partB VALUES LESS THAN (7) $part_optB, PARTITION Partc VALUES LESS THAN (10) $part_optC, PARTITION PartD VALUES LESS THAN (13) $part_optD); SHOW TABLES; --error ER_TABLE_EXISTS_ERROR RENAME TABLE TableA to tablea; --error ER_TABLE_EXISTS_ERROR RENAME TABLE tablea to TableA; --sorted_result SELECT * FROM tablea; SHOW CREATE TABLE tablea; } if (!$lower_case_table_names) { eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY RANGE (a) (PARTITION parta VALUES LESS THAN (4) $part_optA, PARTITION partB VALUES LESS THAN (7) $part_optB, PARTITION Partc VALUES LESS THAN (10) $part_optC, PARTITION PartD VALUES LESS THAN (13) $part_optD); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; if (!$have_bug37719) { RENAME TABLE TableA to tableA; } --sorted_result SELECT * FROM tablea; if (!$have_bug37719) { --sorted_result SELECT * FROM tableA; RENAME TABLE tableA to TableA; } SHOW CREATE TABLE tablea; DROP TABLE tablea; } --echo # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Cleaning up after RANGE PARTITIONING test DROP TABLE TableA; --echo # 4.0 LIST partitioning mgm --echo # Creating LIST partitioned table eval CREATE TABLE TableA (a INT) ENGINE = $engine PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) $part_optA, PARTITION partB VALUES IN (2,10,11) $part_optB, PARTITION Partc VALUES IN (3,4,7) $part_optC, PARTITION PartD VALUES IN (5,6,12) $part_optD); INSERT INTO TableA VALUES (1), (2), (7), (8), (9), (10); INSERT INTO TableA VALUES (3), (4), (5), (6), (11), (12); --sorted_result SELECT * FROM TableA; --echo # Test of ADD/DROP PARTITIONS --echo # expecting duplicate partition name --error ER_SAME_NAME_PARTITION ALTER TABLE TableA ADD PARTITION (PARTITION partA VALUES IN (0)); ALTER TABLE TableA ADD PARTITION (PARTITION partE VALUES IN (16), PARTITION Partf VALUES IN (19), PARTITION PartG VALUES IN (22)); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; ALTER TABLE TableA DROP PARTITION partE, PartG; ALTER TABLE TableA DROP PARTITION Partf; ALTER TABLE TableA ADD PARTITION (PARTITION PartE VALUES IN (13)); --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of REORGANIZE PARTITIONS --error ER_CONSECUTIVE_REORG_PARTITIONS eval ALTER TABLE TableA REORGANIZE PARTITION parta,Partc INTO (PARTITION Partc VALUES IN (1,7) $part_optA COMMENT = "Mix 1 of old parta and Partc", PARTITION partF VALUES IN (3,9) $part_optC COMMENT = "Mix 2 of old parta and Partc", PARTITION parta VALUES IN (4,8) $part_optC COMMENT = "Mix 3 of old parta and Partc"); eval ALTER TABLE TableA REORGANIZE PARTITION parta,partB,Partc INTO (PARTITION Partc VALUES IN (1,7) $part_optA COMMENT = "Mix 1 of old parta and Partc", PARTITION parta VALUES IN (3,9) $part_optC COMMENT = "Mix 2 of old parta and Partc", PARTITION partB VALUES IN (4,8) $part_optC COMMENT = "Mix 3 of old parta and Partc"); if ($fixed_bug20129) { ALTER TABLE TableA ANALYZE PARTITION parta, partB, Partc; ALTER TABLE TableA CHECK PARTITION parta, partB, Partc; ALTER TABLE TableA OPTIMIZE PARTITION parta, partB, Partc; ALTER TABLE TableA REPAIR PARTITION parta, partB, Partc; } --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Test of RENAME TABLE RENAME TABLE TableA to TableB; --sorted_result SELECT * FROM TableB; RENAME TABLE TableB to TableA; --sorted_result SELECT * FROM TableA; --echo # Checking name comparision Upper vs Lower case --echo # Error if lower_case_table_names != 0 let $lower_case_table_names= `SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'lower_case_table_names'`; --echo # lower_case_table_names: $lower_case_table_names if ($lower_case_table_names) { --error ER_TABLE_EXISTS_ERROR eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) $part_optA, PARTITION partB VALUES IN (2,10,11) $part_optB, PARTITION Partc VALUES IN (3,4,7) $part_optC, PARTITION PartD VALUES IN (5,6,12) $part_optD); SHOW TABLES; --error ER_TABLE_EXISTS_ERROR RENAME TABLE TableA to tablea; --error ER_TABLE_EXISTS_ERROR RENAME TABLE tablea to TableA; --sorted_result SELECT * FROM tablea; SHOW CREATE TABLE tablea; } if (!$lower_case_table_names) { eval CREATE TABLE tablea (a INT) ENGINE = $engine PARTITION BY LIST (a) (PARTITION parta VALUES IN (1,8,9) $part_optA, PARTITION partB VALUES IN (2,10,11) $part_optB, PARTITION Partc VALUES IN (3,4,7) $part_optC, PARTITION PartD VALUES IN (5,6,12) $part_optD); INSERT INTO tablea VALUES (1), (2), (7), (8), (9), (10); SHOW TABLES; if (!$have_bug37719) { RENAME TABLE TableA to tableA; } --sorted_result SELECT * FROM tablea; if (!$have_bug37719) { --sorted_result SELECT * FROM tableA; RENAME TABLE tableA to TableA; } SHOW CREATE TABLE tablea; DROP TABLE tablea; } --echo # Test of REMOVE PARTITIONING ALTER TABLE TableA REMOVE PARTITIONING; --sorted_result SELECT * FROM TableA; SHOW CREATE TABLE TableA; --echo # Cleaning up after LIST PARTITIONING test DROP TABLE TableA; } # End of $can_only_key --echo # Cleaning up before exit eval USE $old_db; DROP DATABASE MySQL_Test_DB;