Optimize table removes the data directory in partitions

[Nilnandan Joshi]

nilnandan@Dell-XPS:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.21-70.1 Percona Server (GPL), Release 70.1, Revision 698

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
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
Database changed
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 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.08 sec)

mysql> show warnings;
+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+--------------------------------------+
2 rows in set (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> 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.16 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>

[Kenny Gryp (gryp)]

The same counts for ALTER TABLE statements which recreate the whole 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

[Yura Sorokin (yura-sorokin)]

The fix is now in 5.6 trunk after upstream 5.6.30 merge.
https://github.com/percona/percona-server/pull/511

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-854