Optimize table removes the data directory in partitions

[Chavy]

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) */;

OPTIMIZE TABLE TEST;

It becomes:

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) */;

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

Percona: 5.6.21-69.0

[Chavy]

Is this a known bug? Or is it something Im doing wrong?

[niljoshi]

Hi Chavy,

Thanks for reporting this. This is indeed bug and not well known so I have already reported it. Please subscribe it for further update.

[url]MySQL Bugs: #75112: Optimize table removes the data directory in partitions – upstream
[url]https://bugs.launchpad.net/percona-server/+bug/1399562[/url]

[Rick_James]

If your goal was to get a performance boost by carefully placing MySQL files on different physical drives… In general, you get a better perf boost by using RAID striping.