Bug #72614 DATA DIRECTORY for partitioned tables
Submitted: 12 May 2014 0:42 Modified: 12 May 2014 6:39
Reporter: Federico Razzoli Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.6.17, 5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[12 May 2014 0:42] Federico Razzoli
Description:
DATA DIRECTORY works fine if specified for a partition. But has no effect if it is specified at table level for a partitioned table. This is not good, because the user probably thinks he saved all partitions in a different path.

In my opinion, one of the following alternatives would be good:

a) Table-level DATA DIRECTORY is used for all partitions which don't have a DATA DIRECTORY clause.

OR

b) A warning is issued. At least for InnoDB in strict mode.

How to repeat:
CREATE TABLE employee (
	id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)
ENGINE = InnoDB
DATA DIRECTORY '/tmp'
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000),
    PARTITION p1 VALUES LESS THAN MAXVALUE
);

\! ls /tmp | grep employee

Suggested fix:
a)
[12 May 2014 0:49] Federico Razzoli
Also note that INDEX DIRECTORY with InnoDB produces a warning/error (depending on the strict mode), but this does NOT happen with partitioned tables.
[12 May 2014 6:39] Umesh Shastry
Hello Federico,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[12 May 2014 6:40] Umesh Shastry
// 5.6.17/19

mysql> use test
Database changed
mysql> CREATE TABLE employee (
    -> id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> )
    -> ENGINE = InnoDB
    -> DATA DIRECTORY '/tmp'
    -> PARTITION BY RANGE (id) (
    ->     PARTITION p0 VALUES LESS THAN (10000),
    ->     PARTITION p1 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> \! ls /tmp | grep employee
mysql> show global variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)