MySQL Bugs: #75112: Optimize table removes the data directory in partitions

Bug #75112	Optimize table removes the data directory in partitions
Submitted:	5 Dec 2014 8:11	Modified:	26 Jan 2016 15:54
Reporter:	Nilnandan Joshi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.6.21, 5.6.23, 5.7.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Optimize table removes the data directory in partitions. 

Check "How to repeat" for more information. 

Originally reported here. http://www.percona.com/forums/questions-discussions/mysql-and-percona-server/27196-optimiz...

How to repeat:
[root@centos65 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `TEST` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `a2` mediumtext NOT NULL,
    ->   `a3` char(100) NOT NULL,
    ->   `prt` tinyint(1) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`,`prt`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
    -> /*!50100 PARTITION BY LIST (`prt`)
    -> (PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */;
Query OK, 0 rows affected, 2 warnings (0.23 sec)

mysql> show create table TEST \G
*************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> 
mysql> OPTIMIZE TABLE TEST;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.TEST | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.TEST | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.40 sec)

mysql> show create table TEST \G
*************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */
1 row in set (0.01 sec)

mysql> 

Partition p1 moves from /tmp into main mysql db directory (in datadir)

Hello Nilnandan Joshi,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.23

Build
======
commit: df34098068ae4a07f245ad13a4819ca01bde71df
date: 2014-11-18 09:54:31 +0530
build-date: 2014-11-18 18:01:47 +0100
short: df34098
branch: mysql-5.6
MySQL source 5.6.23

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>
mysql> CREATE TABLE `TEST` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `a2` mediumtext NOT NULL,
    ->   `a3` char(100) NOT NULL,
    ->   `prt` tinyint(1) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`,`prt`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
    -> /*!50100 PARTITION BY LIST (`prt`)
    -> (PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show create table TEST\G
*************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> \! ls -l /tmp/test
total 128
-rw-rw---- 1 umshastr common 98304 Dec  5 11:42 TEST#P#p1.ibd
mysql>
mysql> OPTIMIZE TABLE TEST;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.TEST | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.TEST | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.02 sec)

mysql> show create table TEST\G                                                                                                                                                                   *************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql>
mysql> \! ls -l /tmp/test
total 0

// 5.7.6
// build used

commit: 1982dd7ab813e3cdb28d3bb119cc7b706cf01b15
date: 2014-11-27 13:27:39 +0300
build-date: 2014-11-27 12:38:45 +0100
short: 1982dd7
branch: mysql-trunk
MySQL source 5.7.6

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE `TEST` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `a2` mediumtext NOT NULL,
    ->   `a3` char(100) NOT NULL,
    ->   `prt` tinyint(1) unsigned NOT NULL,
    ->   PRIMARY KEY (`id`,`prt`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
    -> /*!50100 PARTITION BY LIST (`prt`)
    -> (PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
    ->  PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show create table TEST \G
*************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) DATA DIRECTORY = '/tmp' ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> \! ls -l /tmp/test
total 96
-rw-rw---- 1 umshastr common 98304 Dec  5 11:49 TEST#P#p1.ibd
mysql>
mysql> OPTIMIZE TABLE TEST;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.TEST | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.TEST | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.02 sec)

mysql>
mysql> show create table TEST \G
*************************** 1. row ***************************
       Table: TEST
Create Table: CREATE TABLE `TEST` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a2` mediumtext NOT NULL,
  `a3` char(100) NOT NULL,
  `prt` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`,`prt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
/*!50100 PARTITION BY LIST (`prt`)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (1) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> \! ls -l /tmp/test
total 0
mysql>

The same counts for every DDL you do that has to recreate the tablespace:

ip-10-36-36-72 mysql> 
CREATE TABLE partitionedtable
(
        ID int unsigned not null auto_increment,
        col varchar(32) null,
        timer datetime not null,
        PRIMARY KEY(ID,timer)
)
ENGINE=InnoDB
PARTITION BY RANGE(YEAR(timer)) (
        PARTITION p2014 VALUES LESS THAN (2015)
                DATA DIRECTORY = '/tmp/partdb/2014',
        PARTITION p2015 VALUES LESS THAN (2016)
                DATA DIRECTORY = '/tmp/partdb/2015',
        PARTITION p2020 VALUES LESS THAN MAXVALUE 
                DATA DIRECTORY = '/tmp/partdb/rest'
);

Query OK, 0 rows affected (0.07 sec)

ip-10-36-36-72 mysql> show create table partitionedtable\G
*************************** 1. row ***************************
       Table: partitionedtable
Create Table: CREATE TABLE `partitionedtable` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col` varchar(32) DEFAULT NULL,
  `timer` datetime NOT NULL,
  PRIMARY KEY (`ID`,`timer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(timer))
(PARTITION p2014 VALUES LESS THAN (2015) DATA DIRECTORY = '/tmp/partdb/2014' ENGINE = InnoDB,
 PARTITION p2015 VALUES LESS THAN (2016) DATA DIRECTORY = '/tmp/partdb/2015' ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/tmp/partdb/rest' ENGINE = InnoDB) */
1 row in set (0.00 sec)

[root@ip-10-36-36-72 partdb]# find /tmp/partdb/
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/2014/test
/tmp/partdb/2014/test/partitionedtable#P#p2014.ibd
/tmp/partdb/2015
/tmp/partdb/2015/test
/tmp/partdb/2015/test/partitionedtable#P#p2015.ibd
/tmp/partdb/rest
/tmp/partdb/rest/test
/tmp/partdb/rest/test/partitionedtable#P#p2020.ibd

ip-10-36-36-72 mysql> alter table partitionedtable add extracol varchar(32) null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

[root@ip-10-36-36-72 partdb]# find /tmp/partdb/
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/2014/test
/tmp/partdb/2015
/tmp/partdb/2015/test
/tmp/partdb/rest
/tmp/partdb/rest/test

[root@ip-10-36-36-72 partdb]# ls -alhs /var/lib/mysql/test/
total 316K
4.0K drwx------. 2 mysql mysql 4.0K Jan 16 13:21 .
4.0K drwxr-xr-x. 6 mysql mysql 4.0K Jan 16 10:28 ..
4.0K -rw-rw----. 1 mysql mysql   65 Jan 16 10:05 db.opt
 96K -rw-rw----. 1 mysql mysql  96K Jan 16 13:21 partitionedtable#P#p2014.ibd
 96K -rw-rw----. 1 mysql mysql  96K Jan 16 13:21 partitionedtable#P#p2015.ibd
 96K -rw-rw----. 1 mysql mysql  96K Jan 16 13:21 partitionedtable#P#p2020.ibd
 12K -rw-rw----. 1 mysql mysql 8.5K Jan 16 13:21 partitionedtable.frm
4.0K -rw-rw----. 1 mysql mysql   40 Jan 16 13:21 partitionedtable.par

Documented fix in the MySQL 5.6.30 as 5.7.11 changelogs as follows:

    When OPTIMIZE TABLE rebuilt a partitioned InnoDB table, it
    placed the resulting partition tablespace files (*.ibd files) in
    the default data directory instead of the directory specified
    using the DATA DIRECTORY option.

Closed.