content_image/711548.jpg
mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = ’es’; 

<img src='content_image/711548.jpg'>

8 rows in set (0.00 sec) mysql> SELECT * FROM es2; +------+-------+-------+ | id | fname | lname | +------+-------+-------+ | 1669 | Jim | Smith | | 337 | Mary | Jones | | 2005 | Linda | Black | +------+-------+-------+ 3 rows in set (0.00 sec) 

If a table is subpartitioned, you can exchange only a subpartition of the table—not an entire partition—with an unpartitioned table, as shown here: 

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2; ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition 

Table structures are compared in a strict fashion; the number, order, names, and types of columns and indexes of the partitioned table and the nonpartitioned table must match exactly. In addition, both tables must use the same storage engine: 

mysql> CREATE TABLE es3 LIKE e; Query OK, 0 rows affected (1.31 sec) mysql> ALTER TABLE es3 REMOVE PARTITIONING; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE es3\G *************************** 1. row *************************** Table: es3 Create Table: CREATE TABLE ‘es3‘ ( ‘id‘ int(11) NOT NULL, ‘fname‘ varchar(30) DEFAULT NULL, ‘lname‘ varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) mysql> ALTER TABLE es3 ENGINE = MyISAM; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3; ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL 

## 24.3.4 Maintenance of Partitions 

A number of table and partition maintenance tasks can be carried out on partitioned tables using SQL statements intended for such purposes. 

