| Bug #32439 | Implement the syntax for putting tables in different directories | ||
|---|---|---|---|
| Submitted: | 16 Nov 2007 14:55 | Modified: | 3 Jul 2015 15:20 |
| Reporter: | Gustaf Thorslund | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S4 (Feature request) |
| Version: | OS: | Any | |
| Assigned to: | Vasil Dimov | CPU Architecture: | Any |
[18 Nov 2007 15:59]
Valeriy Kravchuk
Thank you for a reasonable feature request.
[8 Apr 2008 14:07]
Heikki Tuuri
Hmm... how about using symlinks, or MySQL table partitioning? But remember that symlinks will break if you rebuild the table.
[8 Apr 2008 14:07]
Heikki Tuuri
Sorry, I meant symlinks to place the partition tables to the desired location?
[21 Oct 2008 14:12]
Adrian Chapela Cordeiro
I am interested on this feature too.
[29 Oct 2010 11:22]
Vasil Dimov
Does the workaround using symlinks works? Another way to workaround this limitation would be to create tables in a different databases and use innodb_file_per_table=1. Then .ibd files will go to a different directories which could be mounted or symlinked.
[29 Oct 2010 11:37]
Vasil Dimov
Using symlinks for the .ibd files themselves is a bad idea because ALTER TABLE could recreate the table which will wipe away the symlinks and will create regular files on their places.
[29 Oct 2010 13:14]
Vasil Dimov
Tobias, of course symlinks are not a _solution_ rather they are a limited workaround.
[3 Jul 2015 15:20]
Mattias Jonsson
Posted by developer: Implemented in 5.6: http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html Enhanced even further in 5.7: http://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html Both are supported per partitions.
Description: Currently it is not possible to put tables or partitions of tables in different directories when creating a table. This would be useful when a database or single table grows to make it use different disks or disk partitions. How to repeat: Set the option innodb_file_per_table. Simple test case: CREATE TABLE foo (id INT) DATA DIRECTORY='/tmp'; And a more extensive example using partitioning: CREATE TABLE sales1 ( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, sales_date DATE NOT NULL DEFAULT '0000-00-00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (5000) DATA DIRECTORY = '/dsk1/s1/data/', PARTITION p1 VALUES LESS THAN (10000) DATA DIRECTORY = '/dsk2/s1/data/', PARTITION p2 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/dsk3/s1/data/' ); Check if there are any files created in the directories pointed to above. Suggested fix: Implement support in InnoDB to store tables in different directories.