CODEWITHSUNDEEP
mysqlpartition
B. Bram
B. Bram

Reputation: 23

How to get the DATA DIRECTORY of a MySql partition?

I am using MySQL Server 5.7, and created the following table:

CREATE TABLE MYDATA (
  hashpart SMALLINT UNSIGNED NOT NULL)
  PARTITION BY RANGE COLUMNS(hashpart) (
    PARTITION p_123 VALUES LESS THAN(123) DATA DIRECTORY = 'C:\\MyDataBase',
    PARTITION p_MAXVALUE VALUES LESS THAN(MAXVALUE) DATA DIRECTORY = 'C:\\Log\\MyDataBase'
);

Is there a way to know, using only MySQL queries, the value of the DATA DIRECTORY of each partition ? The following query doesn't help much:

SELECT * FROM information_schema.partitions WHERE TABLE_NAME = 'MYDATA';

Thanks in advance for your answers !

Upvotes: 2

Views: 1732

Answers (1)

wchiquito
wchiquito

Reputation: 16551

If your partitions use InnoDB (the query does not work with another type of engine), you can run a query like the following (modify the query as needed):

mysql> DROP TABLE IF EXISTS `MYDATA`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `MYDATA` (
    ->   `hashpart` SMALLINT UNSIGNED NOT NULL)
    ->   PARTITION BY RANGE COLUMNS(`hashpart`) (
    ->     PARTITION `p_123` VALUES LESS THAN(123) DATA DIRECTORY = '/path/to/partition/MyDataBase0',
    ->     PARTITION `p_MAXVALUE` VALUES LESS THAN(MAXVALUE) DATA DIRECTORY = '/path/to/partition/MyDataBase1'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->   `p`.`PARTITION_NAME`,
    ->   LEFT(`isdf`.`PATH`, INSTR(`isdf`.`PATH`, `ist`.`NAME`) - 2) `PATH`
    -> FROM
    ->   `information_schema`.`PARTITIONS` `p`
    ->   INNER JOIN `information_schema`.`INNODB_SYS_TABLESPACES` `ist` ON
    ->     `ist`.`NAME` LIKE CONCAT(`p`.`TABLE_SCHEMA`, '/', `p`.`TABLE_NAME`, '%', `p`.`PARTITION_NAME`)
    ->   INNER JOIN `information_schema`.`INNODB_SYS_DATAFILES` `isdf` ON
    ->     `ist`.`SPACE` = `isdf`.`SPACE`
    -> WHERE
    ->   `TABLE_SCHEMA` = 'test' AND
    ->   `TABLE_NAME` = 'MYDATA';
+----------------+--------------------------------+
| PARTITION_NAME | PATH                           |
+----------------+--------------------------------+
| p_123          | /path/to/partition/MyDataBase0 |
| p_MAXVALUE     | /path/to/partition/MyDataBase1 |
+----------------+--------------------------------+
2 rows in set (0.01 sec)

See db-fiddle.

Upvotes: 3

Related Questions