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: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.6.21, 5.6.23, 5.7.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[5 Dec 2014 8:11]
Nilnandan Joshi
[5 Dec 2014 10:46]
MySQL Verification Team
Hello Nilnandan Joshi, Thank you for the report and test case. Thanks, Umesh
[5 Dec 2014 10:46]
MySQL Verification Team
// 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 Dec 2014 10:52]
MySQL Verification Team
// 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>
[16 Jan 2015 13:27]
Kenny Gryp
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
[26 Jan 2016 15:54]
Jon Stephens
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.