Optimize table removes the data directory in partitions
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@
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
*******
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
*******
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 |
The same counts for ALTER TABLE statements which recreate the whole tablespace:
ip-10-36-36-72 mysql>
DATA DIRECTORY = '/tmp/partdb/2014',
DATA DIRECTORY = '/tmp/partdb/2015',
DATA DIRECTORY = '/tmp/partdb/rest'
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)
PARTITION p2015 VALUES LESS THAN (2016)
PARTITION p2020 VALUES LESS THAN MAXVALUE
);
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/ 2014/test 2014/test/ partitionedtabl e#P#p2014. ibd 2015/test 2015/test/ partitionedtabl e#P#p2015. ibd rest/test rest/test/ partitionedtabl e#P#p2020. ibd
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/
/tmp/partdb/
/tmp/partdb/2015
/tmp/partdb/
/tmp/partdb/
/tmp/partdb/rest
/tmp/partdb/
/tmp/partdb/
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/ 2014/test 2015/test rest/test
/tmp/partdb/
/tmp/partdb/2014
/tmp/partdb/
/tmp/partdb/2015
/tmp/partdb/
/tmp/partdb/rest
/tmp/partdb/
[root@ip- 10-36-36- 72 partdb]# ls -alhs /var/lib/ mysql/test/ e#P#p2014. ibd e#P#p2015. ibd e#P#p2020. ibd e.frm e.par
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 partitionedtabl
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtabl
96K -rw-rw----. 1 mysql mysql 96K Jan 16 13:21 partitionedtabl
12K -rw-rw----. 1 mysql mysql 8.5K Jan 16 13:21 partitionedtabl
4.0K -rw-rw----. 1 mysql mysql 40 Jan 16 13:21 partitionedtabl