MySQL Bugs: #77773: DATA DIRECTORY and INDEX DIRECTORY ignored for MyISAM partitions on Linux

Bug #77773	DATA DIRECTORY and INDEX DIRECTORY ignored for MyISAM partitions on Linux
Submitted:	17 Jul 2015 23:50	Modified:	20 Jul 2015 12:43
Reporter:	Geoff Montee	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.6.23, 5.6.25	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
The DATA DIRECTORY and INDEX DIRECTORY options are ignored for MyISAM partitions on Linux. The documentation only says this should be the case on Windows:

https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html

"DATA DIRECTORY and INDEX DIRECTORY options.  DATA DIRECTORY and INDEX DIRECTORY are subject to the following restrictions when used with partitioned tables:

* Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored (see Bug #32091).

* On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions of MyISAM tables (Bug #30459). However, you can use DATA DIRECTORY for individual partitions or subpartitions of InnoDB tables."

How to repeat:
Create a table with MyISAM partitions and specify the DATA DIRECTORY and INDEX DIRECTORY options. Then view the warnings that result.

e.g.:

create table test_partitioned (
id int not null
) PARTITION BY HASH ( id )
( partition p0 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
partition p1 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
partition p2 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
partition p3 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
partition p4 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data') ;
show warnings;

Output:

mysql> create table test_partitioned (
    -> id int not null
    -> ) PARTITION BY HASH ( id )
    -> ( partition p0 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
    -> partition p1 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
    -> partition p2 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
    -> partition p3 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data',
    -> partition p4 engine=MyISAM data directory = '/disk2/mysql/data' index directory = '/disk2/mysql/data') ;
Query OK, 0 rows affected, 10 warnings (0.04 sec)

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
10 rows in set (0.00 sec)

System info:

[gmontee@localhost ~]$ cat /etc/centos-release 
CentOS release 6.6 (Final)
[gmontee@localhost ~]$ uname -a
Linux localhost.localdomain 2.6.32-504.8.1.el6.x86_64 #1 SMP Wed Jan 28 21:11:36 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
[gmontee@localhost ~]$ rpm -qa | grep "mysql"
mysql-community-common-5.6.23-2.el6.x86_64
mysql-community-libs-5.6.23-2.el6.x86_64
mysql-community-libs-compat-5.6.23-2.el6.x86_64
mysql-community-server-5.6.23-2.el6.x86_64
mha4mysql-node-0.56-0.el6.noarch
mysql-community-client-5.6.23-2.el6.x86_64

Suggested fix:
Either:

1.) Allow allow MyISAM partitions to respect the DATA DIRECTORY and INDEX DIRECTORY options on Linux.

2.) Clarify the documentation.

I would prefer the first option.

Hello Geoff,

Thank you for the report.
Observed this with 5.6.25 on Fedora22.

Thanks,
Umesh

//

 create table test_partitioned (
 id int not null
 ) PARTITION BY HASH ( id )
 ( partition p0 engine=MyISAM data directory = '/tmp/' index directory = '/tmp/',
 partition p1 engine=MyISAM data directory = '/tmp/' index directory = '/tmp/') ;

mysql>  create table test_partitioned (
    ->  id int not null
    ->  ) PARTITION BY HASH ( id )
    ->  ( partition p0 engine=MyISAM data directory = '/tmp/' index directory = '/tmp/',
    ->  partition p1 engine=MyISAM data directory = '/tmp/' index directory = '/tmp/') ;
Query OK, 0 rows affected, 4 warnings (0.04 sec)

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
4 rows in set (0.00 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.25                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.25                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> \! mkdir -p /tmp/test
mysql> drop table test_partitioned;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_partitioned (  id int not null  ) PARTITION BY HASH ( id )  ( partition p0 engine=MyISAM data directory = '/tmp/test' index directory = '/tmp/test',  partition p1 engine=MyISAM data directory = '/tmp/test' index directory = '/tmp/test');
Query OK, 0 rows affected, 4 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
4 rows in set (0.00 sec)

mysql> \! ls -l /tmp/test
total 0

mysql> \! cat /etc/*release
Fedora release 22 (Twenty Two)
NAME=Fedora
VERSION="22 (Twenty Two)"
ID=fedora
VERSION_ID=22
PRETTY_NAME="Fedora 22 (Twenty Two)"
ANSI_COLOR="0;34"
CPE_NAME="cpe:/o:fedoraproject:fedora:22"
HOME_URL="https://fedoraproject.org/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"
REDHAT_BUGZILLA_PRODUCT="Fedora"
REDHAT_BUGZILLA_PRODUCT_VERSION=22
REDHAT_SUPPORT_PRODUCT="Fedora"
REDHAT_SUPPORT_PRODUCT_VERSION=22
PRIVACY_POLICY_URL=https://fedoraproject.org/wiki/Legal:PrivacyPolicy
VARIANT="Workstation Edition"
VARIANT_ID=workstation
Fedora release 22 (Twenty Two)
Fedora release 22 (Twenty Two)