Bug #104293 index dir ERROR: 1031 Table storage engine for 'th' doesn't have this option

• Submitted: 13 Jul 2021 8:37
• Modified: 13 Jul 2021 11:50
• Reporter: Server Murat Koçum
• Status: Not a Bug
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 8.0.25
• OS: Any
• CPU Architecture: Any
• Tags: partition index directory

[13 Jul 2021 8:37] Server Murat Koçum

Description:
In reference for mysql8 on page 2337 partition with index dir is not working.
ERROR: 1031 (HY000): Table storage engine for 'th' doesn't have this option 
Please see below.

MySQL  192.168.:3306 ssl  db  SQL > CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
                                            -> PARTITION BY LIST(YEAR(adate))
                                            -> (
                                            -> PARTITION p1999 VALUES IN (1995, 1999, 2003)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24',
                                            -> PARTITION p2000 VALUES IN (1996, 2000, 2004)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2001 VALUES IN (1997, 2001, 2005)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24',
                                            -> PARTITION p2002 VALUES IN (1998, 2002, 2006)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24'
                                            -> );
ERROR: 1031 (HY000): Table storage engine for 'th' doesn't have this option
 MySQL  192.168.:3306 ssl  cdr_db  SQL > CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
                                            -> PARTITION BY LIST(YEAR(adate))
                                            -> (
                                            -> PARTITION p1999 VALUES IN (1995, 1999, 2003)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2000 VALUES IN (1996, 2000, 2004)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2001 VALUES IN (1997, 2001, 2005)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2002 VALUES IN (1998, 2002, 2006)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> );
Query OK, 0 rows affected (0.9034 sec)

How to repeat:
MySQL  192.168.:3306 ssl  db  SQL > CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
                                            -> PARTITION BY LIST(YEAR(adate))
                                            -> (
                                            -> PARTITION p1999 VALUES IN (1995, 1999, 2003)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24',
                                            -> PARTITION p2000 VALUES IN (1996, 2000, 2004)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2001 VALUES IN (1997, 2001, 2005)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24',
                                            -> PARTITION p2002 VALUES IN (1998, 2002, 2006)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> INDEX DIRECTORY = '/data/index/hours24'
                                            -> );
ERROR: 1031 (HY000): Table storage engine for 'th' doesn't have this option
 MySQL  192.168.:3306 ssl  cdr_db  SQL > CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)
                                            -> PARTITION BY LIST(YEAR(adate))
                                            -> (
                                            -> PARTITION p1999 VALUES IN (1995, 1999, 2003)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2000 VALUES IN (1996, 2000, 2004)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2001 VALUES IN (1997, 2001, 2005)
                                            -> DATA DIRECTORY = '/data/data/hours24',
                                            -> PARTITION p2002 VALUES IN (1998, 2002, 2006)
                                            -> DATA DIRECTORY = '/data/data/hours24'
                                            -> );
Query OK, 0 rows affected (0.9034 sec)

[13 Jul 2021 11:50] MySQL Verification Team

Hi Mr. Kocum,

Thank you for your bug report.

However, this is not a bug. 

Our Reference Manual, in the paragraph 15.6.1.2 , explains when can these options be used and when not, so this is not supported. 

For example, for InnoDB SE, you can not even set INDEX DIRECTORY, since that SE does not have separate data and index tablespaces .......

Not a bug.