Description:
I create a partitioned table using the DATA DIRECTORY and INDEX DIRECTORY options for the partitions. The directory is accessible to all users for read/write.
MySQL reports that the table is successfully created, and the table is listed in the output of SHOW TABLES. When I check the specified directory, I find that the .MYI and .MYD files that I would expect to be created are there, and are world readable/writeable (owner is Everybody).
However, running any statement referring to this table by name yields a "Can't find file" error.
The same table creation statements work as expected on Linux.
How to repeat:
mysql> CREATE TABLE orders_key6 (
-> id INT AUTO_INCREMENT,
-> customer_surname VARCHAR(30),
-> store_id INT,
-> salesperson_id INT,
-> order_date DATE,
-> note VARCHAR(500),
-> INDEX idx (id)
-> ) ENGINE = MYISAM
-> PARTITION BY KEY(order_date) (
-> PARTITION p0
-> INDEX DIRECTORY = 'D:/gswap'
-> DATA DIRECTORY = 'D:/gswap',
-> PARTITION p1,
-> PARTITION p2
-> INDEX DIRECTORY = 'D:/gswap'
-> DATA DIRECTORY = 'D:/gswap',
-> PARTITION p3
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> show create table orders_key6;
ERROR 1017 (HY000): Can't find file: 'orders_key6' (errno: 2)
mysql> show tables;
+------------------+
| Tables_in_wikidb |
+------------------+
| archive |
| blobs |
...
| oldimage |
| orders_key6 |
| querycache |
...
| watchlist |
+------------------+
27 rows in set (0.00 sec)
mysql> show create table orders_key6;
ERROR 1017 (HY000): Can't find file: 'orders_key6' (errno: 2)
mysql> select * from orders_key6;
ERROR 1017 (HY000): Can't find file: 'orders_key6' (errno: 2)
mysql> CREATE TABLE orders_key7 (
-> id INT AUTO_INCREMENT,
-> customer_surname VARCHAR(30),
-> store_id INT,
-> salesperson_id INT,
-> order_date DATE,
-> note VARCHAR(500),
-> INDEX idx (id)
-> ) ENGINE = MYISAM
-> PARTITION BY KEY(order_date) (
-> PARTITION p0
-> INDEX DIRECTORY = 'D:\\gswap'
-> DATA DIRECTORY = 'D:\\gswap',
-> PARTITION p1,
-> PARTITION p2
-> INDEX DIRECTORY = 'D:\\gswap'
-> DATA DIRECTORY = 'D:\\gswap',
-> PARTITION p3
-> );
Query OK, 0 rows affected (0.19 sec)
mysql> show tables;
+------------------+
| Tables_in_wikidb |
+------------------+
| archive |
| blobs |
| brokenlinks |
| categorylinks |
| cur |
| hitcounter |
| image |
| imagelinks |
| interwiki |
| ipblocks |
| links |
| linkscc |
| logging |
| math |
| objectcache |
| old |
| oldimage |
| orders_key6 |
| orders_key7 |
| querycache |
| recentchanges |
| searchindex |
| site_stats |
| user |
| user_newtalk |
| user_rights |
| validate |
| watchlist |
+------------------+
28 rows in set (0.00 sec)
mysql> show create table orders_key7;
ERROR 1017 (HY000): Can't find file: 'orders_key7' (errno: 2)
mysql> CREATE TABLE orders_key8 (
-> id INT AUTO_INCREMENT,
-> customer_surname VARCHAR(30),
-> store_id INT,
-> salesperson_id INT,
-> order_date DATE,
-> note VARCHAR(500),
-> INDEX idx (id)
-> ) ENGINE = MYISAM
-> PARTITION BY KEY(order_date) (
-> PARTITION p0
-> INDEX DIRECTORY = 'M:/partition-data'
-> DATA DIRECTORY = 'M:/partition-data',
-> PARTITION p1,
-> PARTITION p2
-> INDEX DIRECTORY = 'M:/partition-data'
-> DATA DIRECTORY = 'M:/partition-data',
-> PARTITION p3
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> show create table orders_key8;
ERROR 1017 (HY000): Can't find file: 'orders_key8' (errno: 2)
mysql> CREATE TABLE orders_key9 (
-> id INT AUTO_INCREMENT,
-> customer_surname VARCHAR(30),
-> store_id INT,
-> salesperson_id INT,
-> order_date DATE,
-> note VARCHAR(500),
-> INDEX idx (id)
-> ) ENGINE = MYISAM
-> PARTITION BY KEY(order_date) (
-> PARTITION p0
-> INDEX DIRECTORY = 'm:/partition-data'
-> DATA DIRECTORY = 'm:/partition-data',
-> PARTITION p1,
-> PARTITION p2
-> INDEX DIRECTORY = 'm:/partition-data'
-> DATA DIRECTORY = 'm:/partition-data',
-> PARTITION p3
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> select * from orders_key9;
ERROR 1017 (HY000): Can't find file: 'orders_key9' (errno: 2)
Suggested fix:
The server should be able to see the files it has just created.