MySQL Bugs: #67267: WB 5.2.44/mysql 5.6: InnoDb table directory not handled

Bug #67267	WB 5.2.44/mysql 5.6: InnoDb table directory not handled
Submitted:	16 Oct 2012 15:39	Modified:	29 Oct 2012 16:08
Reporter:	Gerhard Smith	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.44	OS:	Any (Tested on Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any
Tags:	InnoDB directory 5.6

Description:
When using the 'Alter Table' on an InnoDB table that was created with 'Data Directory=...' on mysql 5.6, the 'alter table' screen displays the directory in the options sub-panel correctly, but if you change it, it does not detect the change or allow it to be applied. The 'Data Directory' is a new feature for InnoDB tables in mysql 5.6.

How to repeat:
1. Create a table with an InndoDB storage engine and add 'DATA DIRECTORY=<some dir> to the CREATE statement.
2. Using workbench, right click on the table and select 'alter table'
3. Click on 'Options' for the table.
4. Scroll down to where 'Data Directory' is displayed.
5. Change the directory and click 'apply'.
6. It will report 'no changes detected'.

Suggested fix:
I suppose this is still in the process of being implemented to support mysql 5.6.

Thank you for the bug report. Are you able to change the path of DATA DIRECTORY using the mysql.exe command client?. Thanks.

I am not using Windows, using Ubuntu Linux so it is just /usr/local/mysql/bin/mysql
No I have not managed to change the DATA Directory for an InnoDB table after I created it with a DATA DIRECTORY set, BUT I could be getting the syntax wrong even though mysql client does not complain about it.
I had a look at the schema tables and the one table is dedicated to setting the directories for each table by tableID, but I was a bit wary to go in and change the table in case it affects something else somewhere.
I do get a warning from mysql that the DATA DIRECTORY has been ignored though when I try to 'ALTER' the table's DATA DIRECTORY.

Indeed I got the same: alter table ignores the DATA DIRECTORY, so I foujd at respective Manual: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

"table_option signifies a table option of the kind that can be used in the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, or AVG_ROW_LENGTH. (Section 13.1.14, “CREATE TABLE Syntax”, lists all table options.) However, ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options."

So WorkBench behaves according the above. Thanks.

I agree that that is the documentation, however, why does the workbench then display the directory in an editable field. I will test some more, but I think it works for myisam tables.

The problem is that at this point there is no way, barring changing the 'create table' and doing an unload/reload, to move the datafile.

Thanks for your very quick response.

This behavior match to MyISAM tables but not supported for InnoDB:

"MyISAM tables have 3 files per table, a table.FRM for table format, table.MYD for data, and table.MYI for indexes. 
InnoDB also has the .frm file but stores both data and indexes in table.ibd. This setup has the slight advantage of having less files open to access your tables, which can become important particularly for partitioned tables or setups with a low open_files_limit setting.
By using separate files per table, the opportunity arises to split your data up across different directories and disks. 
This can be done easily with MyISAM tables by specifying a DATA DIRECTORY and INDEX DIRECTORY in your CREATE TABLE syntax, allowing you spread your tables across directories and disks with ease. The problem is that InnoDB ignores these specifications..."

Please , if you consider this  behavior is not according to MySQL databases, feel free to reopen this bug and provide more comments.

Thanks for Interest on MySQL workbench.