1# InnoDB supports native partitioning and is required by the test. 2CREATE TABLE t1 (a int) 3ENGINE = InnoDB 4PARTITION BY HASH (a) PARTITIONS 2; 5INSERT INTO t1 VALUES (1); 6SELECT * FROM t1; 7a 81 9SHOW CREATE TABLE t1; 10Table Create Table 11t1 CREATE TABLE `t1` ( 12 `a` int(11) DEFAULT NULL 13) ENGINE=InnoDB DEFAULT CHARSET=latin1 14/*!50100 PARTITION BY HASH (a) 15PARTITIONS 2 */ 16DROP TABLE t1; 17# MyISAM requires ha_partition to support partitioning. 18CREATE TABLE t1 (a int) 19ENGINE = MyISAM 20PARTITION BY HASH (a) PARTITIONS 2; 21ERROR HY000: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1' 22INSTALL PLUGIN `partition` SONAME 'ha_partition.so'; 23SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_AUTHOR, PLUGIN_DESCRIPTION, PLUGIN_LICENSE, LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'PARTITION'; 24PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_LIBRARY PLUGIN_AUTHOR PLUGIN_DESCRIPTION PLUGIN_LICENSE LOAD_OPTION 25partition 1.0 ACTIVE STORAGE ENGINE ha_partition.so Mikael Ronstrom, MySQL AB Partition Storage Engine Helper GPL ON 26INSTALL PLUGIN `PARTITION` SONAME 'ha_partition.so'; 27ERROR HY000: Function 'PARTITION' already exists 28CREATE TABLE t1 (a int) 29ENGINE = MyISAM 30PARTITION BY HASH (a) PARTITIONS 2; 31INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 32# UNINSTALL will give a warning since an open table is in the table cache 33UNINSTALL PLUGIN `partition`; 34Warnings: 35Warning 1620 Plugin is busy and will be uninstalled on shutdown 36# Still working due to UNINSTALL not yet completed 37SHOW CREATE TABLE t1; 38Table Create Table 39t1 CREATE TABLE `t1` ( 40 `a` int(11) DEFAULT NULL 41) ENGINE=MyISAM DEFAULT CHARSET=latin1 42/*!50100 PARTITION BY HASH (a) 43PARTITIONS 2 */ 44SELECT COUNT(*) FROM t1; 45COUNT(*) 465 47SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'PARTITION'; 48PLUGIN_NAME PLUGIN_STATUS 49partition DELETED 50SELECT COUNT(*) FROM mysql.plugin; 51COUNT(*) 520 53# FLUSH TABLES will close all tables and allow the UNINSTALL to complete 54FLUSH TABLES; 55SELECT COUNT(*) FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name = 'partition'; 56COUNT(*) 570 58SELECT COUNT(*) FROM mysql.plugin; 59COUNT(*) 600 61SHOW CREATE TABLE t1; 62ERROR HY000: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1' 63SELECT COUNT(*) FROM t1; 64ERROR HY000: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1' 65CREATE TABLE t2 (a int) 66ENGINE = MyISAM 67PARTITION BY HASH (a) PARTITIONS 2; 68ERROR HY000: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1' 69DROP TABLE t1; 70ERROR HY000: Storage engine for table 'test'.'t1' is not loaded. 71INSTALL PLUGIN `PARTITION` SONAME 'ha_partition.so'; 72SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_LIBRARY, PLUGIN_AUTHOR, PLUGIN_DESCRIPTION, PLUGIN_LICENSE, LOAD_OPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'PARTITION'; 73PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_LIBRARY PLUGIN_AUTHOR PLUGIN_DESCRIPTION PLUGIN_LICENSE LOAD_OPTION 74partition 1.0 ACTIVE STORAGE ENGINE ha_partition.so Mikael Ronstrom, MySQL AB Partition Storage Engine Helper GPL ON 75INSTALL PLUGIN `partition` SONAME 'ha_partition.so'; 76ERROR HY000: Function 'partition' already exists 77DROP TABLE t1; 78# Simple partitioned table test 79CREATE TABLE t1(a int) PARTITION BY HASH (a) PARTITIONS 2; 80SHOW CREATE TABLE t1; 81Table Create Table 82t1 CREATE TABLE `t1` ( 83 `a` int(11) DEFAULT NULL 84) ENGINE=InnoDB DEFAULT CHARSET=latin1 85/*!50100 PARTITION BY HASH (a) 86PARTITIONS 2 */ 87INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 88ALTER TABLE t1 ADD PARTITION PARTITIONS 1; 89INSERT INTO t1 VALUES (6), (7), (8); 90SELECT COUNT(*) FROM t1; 91COUNT(*) 928 93# Cannot drop a partition from a HASH partitioned table 94ALTER TABLE t1 DROP PARTITION p0; 95ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions 96ALTER TABLE t1 COALESCE PARTITION 2; 97ALTER TABLE t1 COALESCE PARTITION 1; 98ERROR HY000: Cannot remove all partitions, use DROP TABLE instead 99SHOW CREATE TABLE t1; 100Table Create Table 101t1 CREATE TABLE `t1` ( 102 `a` int(11) DEFAULT NULL 103) ENGINE=InnoDB DEFAULT CHARSET=latin1 104/*!50100 PARTITION BY HASH (a) 105PARTITIONS 1 */ 106DROP TABLE t1; 107UNINSTALL PLUGIN `partition`; 108UNINSTALL PLUGIN `PARTITION`; 109ERROR 42000: PLUGIN PARTITION does not exist 110