MySQL Bugs: #79789: DATA/INDEX DIRECTORY are not in information

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

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.

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.

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)

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.

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)

I used the default configuration file and got no warnings.

Thank you for the feedback. 5.5/5.6/5.7 affected.