| Bug #21349 | Can't Find File error when using DATA/INDEX DIRECTORY partition options | ||
|---|---|---|---|
| Submitted: | 30 Jul 2006 12:28 | Modified: | 7 Aug 2006 7:15 |
| Reporter: | Jon Stephens | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.1.11/5.1BK | OS: | Windows (Windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[30 Jul 2006 14:08]
MySQL Verification Team
Thank you for the bug report.
PS C:\> c:/mysql/bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-pro
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
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 = 'C:/gswap'
-> DATA DIRECTORY = 'C:/gswap',
-> PARTITION p1,
-> PARTITION p2
-> INDEX DIRECTORY = 'C:/gswap'
-> DATA DIRECTORY = 'C:/gswap',
-> PARTITION p3
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show create table orders_key6;
ERROR 1017 (HY000): Can't find file: 'orders_key6' (errno: 2)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| orders_key6 |
+----------------+
1 row in set (0.00 sec)
mysql>
PS C:\> ls C:\gswap
Directory: Microsoft.PowerShell.Core\FileSystem::C:\gswap
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 2006-07-30 11:05 0 orders_key6#P#p0.MYD
-a--- 2006-07-30 11:05 1024 orders_key6#P#p0.MYI
-a--- 2006-07-30 11:05 0 orders_key6#P#p2.MYD
-a--- 2006-07-30 11:05 1024 orders_key6#P#p2.MYI
PS C:\>
[7 Aug 2006 7:15]
Mikael Ronström
This is the same bug as #19107, it is an issue with using DATA DIRECTORY with MyISAM on Windows
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.