Optimize table removes the data directory in partitions

Bug #1399562 reported by Nilnandan Joshi on 2014-12-05
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
New
High
Unassigned
5.6
Fix Released
High
Unassigned
5.7
Fix Released
High
Unassigned

Bug Description

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>

Changed in percona-server:
status: New → Confirmed
tags: added: upstream
Kenny Gryp (gryp) wrote :

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) wrote :

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

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Related blueprints

Remote bug watches

Bug watches keep track of this bug in other bug trackers.