MySQL Bugs: #46816: Provide a means of locating all InnoDB files outside the default datadir

Bug #46816	Provide a means of locating all InnoDB files outside the default datadir
Submitted:	19 Aug 2009 21:28	Modified:	13 May 2010 16:12
Reporter:	Lachlan Mulcahy	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	Any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb feature request

Description:
Currently when innodb_data_home_dir is set along with innodb_file_per_table only the central tablespace files are stored in the new location. File-per-table tablespaces are still stored within the datadir under the relevant database's directory.

It would be nice to be able to relocate all InnoDB related files with a simple default setting like this parameter. 

This is handy if you wish to store your InnoDB files on a device with Direct IO enabled to avoid filesystem caching and leave your other MyISAM table files in the main datadir to benefit from filesytem caching.

The current alternative is to have your datadir on a Direct IO device and then individually specify INDEX DIRECTORY and DATA DIRECTORY for each MyISAM table upon creation which is an effort to maintain and enforce on all database users.

How to repeat:
n/a

Suggested fix:
Provide a parameter to enable alternate-homing of file-per-table tablespace files when innodb_data_home_dir is set.

Short of this, providing InnoDB support for an option at CREATE time similar to the INDEX DIRECTORY / DATA DIRECTORY options on MyISAM tables.

Verified as described.

This seems to be fixed.

mysl 5.6.14 [test] > CREATE TABLE t1(id int) DATA DIRECTORY = '/tmp';
Query OK, 0 rows affected (0.03 sec)

mysl 5.6.14 [test] > SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '/tmp/%';
+-------+------------------+
| SPACE | PATH             |
+-------+------------------+
|   102 | /tmp/test/t1.ibd |
+-------+------------------+
1 row in set (0.00 sec)