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)