Bug #79789 DATA/INDEX DIRECTORY are not in information_schema.TABLES
Submitted: 28 Dec 2015 23:54 Modified: 30 Dec 2015 10:39
Reporter: Federico Razzoli Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.5/5.6/5.7 OS:Any
Assigned to: CPU Architecture:Any

[28 Dec 2015 23:54] Federico Razzoli
Description:
Table options are stored in information_schema.TABLES. For example, MAX_ROWS is stored in the TABLE_OPTIONS column, and ENGINE is stored in the ENGINE column. But DATA DIRECTORY and INDEX DIRECTORY table options are not stored in any column.

I originally reported this bug to MariaDB, then I was told that it is an upstream bug:
https://mariadb.atlassian.net/browse/MDEV-9307

How to repeat:
Search the I_S for that information.

Suggested fix:
Add that information to the CREATE_OPTIONS column.
[29 Dec 2015 15:44] Miguel Solorzano
Please check if in the create table command you get warnings if yes please print here. Check also if applied with https://bugs.mysql.com/bug.php?id=77773 as duplicate. Thanks.
[29 Dec 2015 16:13] Miguel Solorzano
Forgot to paste:

mysql> CREATE TABLE `t` (
    ->   `c` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=1000 DATA DIRECTORY='/tmp/' INDEX DIRECTORY='/tmp/' ;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

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

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.10                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.38 sec)
[29 Dec 2015 17:52] Elena Stepanova
MySQL [test]> CREATE TABLE `t` (
    ->   `c` int(11) DEFAULT NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MIN_ROWS=1000 DATA DIRECTORY='/tmp/' INDEX DIRECTORY='/tmp/' ;
Query OK, 0 rows affected (0.29 sec)

MySQL [test]> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.7.10              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| tls_version             | TLSv1,TLSv1.1       |
| version                 | 5.7.10-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
8 rows in set (0.00 sec)

Maybe you are running with symbolic links disabled, this would make the options be ignored. 

Bug #77773 is different, it's specifically about partitioned tables, while this one has nothing to do with partitions.
[30 Dec 2015 3:24] Federico Razzoli
I've provided the link to the test case... but maybe Oracle ignored it because it contains the word "MyISAM", so I'll adapt the test case to InnoDB:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> CREATE TABLE t (c INT) ENGINE = InnoDB, ROW_FORMAT = REDUNDANT, DATA DIRECTORY = '/tmp';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT CREATE_OPTIONS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't';
+----------------------+
| CREATE_OPTIONS       |
+----------------------+
| row_format=REDUNDANT |
+----------------------+
1 row in set (0.00 sec)
[30 Dec 2015 3:26] Federico Razzoli
I used the default configuration file and got no warnings.
[30 Dec 2015 10:39] Miguel Solorzano
Thank you for the feedback. 5.5/5.6/5.7 affected.