MySQL Bugs: #30459: Partitioning across disks failing on Windows Server (64-bit)

Bug #30459	Partitioning across disks failing on Windows Server (64-bit)
Submitted:	16 Aug 2007 15:29	Modified:	28 Mar 2008 15:02
Reporter:	Ronald Rudy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.1.19-beta-community-nt-debug-log	OS:	Windows
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	data directory, index directory, partitioning, unscheduled, windows

Description:
When attempting to specify a DATA DIRECTORY and INDEX DIRECTORY for a partition that spans across different drives the create works, but any subsequent operations on that table fail with a "ERROR 1017 (HY000): Can't find file" message.

How to repeat:
Create the necessary directories on the file system, such as:

md D:\partitiontest\data\testdb
md E:\partitiontest\indexes\testdb

Then try to create a table specifying the above as data and index directories respectively, then insert into the table:

[code]
create table testp
(
   id int primary key auto_increment,
   value varchar(255)
 )
 partition by hash(id)
 (
   partition p0
     data directory = 'D:/partitiontest/data/testdb'
     index directory = 'E:/partitiontest/indexes/testdb'
 );

insert into testp (value) values ('xyz');
[/code]

Here is my output when I attempt the above query:

[code]
mysql> create table testp
    -> (
    ->   id int primary key auto_increment,
    ->   value varchar(255)
    -> )
    -> partition by hash(id)
    -> (
    ->   partition p0
    ->     data directory = 'D:/partitiontest/data/testdb'
    ->     index directory = 'E:/partitiontest/indexes/testdb'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into testp (value) values ('xyz');
ERROR 1017 (HY000): Can't find file: 'testp' (errno: 2)
[/code]

In addition, any attempt to drop the table through the MySQL client will cause connection to hang.  Attempts to stop the service fail.  Server needs to be rebooted.

Note also that the files are created in the proper directories, with the .MYD file in the data directory and the .MYI file in the index directory.  It seems to be simply failing on any activity on the data.

Thank you for the bug report.

Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.

c:\dev>cd 5.1

c:\dev\5.1>md E:\partitiontest\indexes\testdb

c:\dev\5.1>md Z:\partitiontest\data\testdb

mysql> create table testp
    -> (
    ->    id int primary key auto_increment,
    ->    value varchar(255)
    ->  )
    ->  partition by hash(id)
    ->  (
    ->    partition p0
    ->      data directory = 'Z:/partitiontest/data/testdb'
    ->      index directory = 'E:/partitiontest/indexes/testdb'
    ->  );
Query OK, 0 rows affected (0.45 sec)

mysql> insert into testp (value) values ('xyz');
ERROR 1017 (HY000): Can't find file: 'testp' (errno: 2)
mysql>

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

symlinks is not supported on Windows. The bug is that it does not ignore the DATA/INDEX DIR in the partitioning clause with a warning. (Should be fixed the same way as bug#17498).

So partitioning the data and index files across multiple physical disks will not be possible on MySQL?  The MySQL engine itself actually relies on symlinks to read data from different physical locations?

See Bug#17498, which refers to http://dev.mysql.com/doc/refman/5.1/en/windows-symbolic-links.html

I don't see why we can't support DATA DIRECTORY and INDEX DIRECTORY for partitions in the same fashion.

(I also don't see why we don't create the .sym files automatically, rather than making the user do so manually, but that's possibly a separate issue.)

Will using symlinks on Windows as described 

http://dev.mysql.com/doc/refman/5.1/en/windows-symbolic-links.html

work for partitioning as-is?

Symlinks on Windows only works for directories (i.e. databases in MySQL).
(as the link explains)

That should work for partitioning as well, but I believe the question was about having partitions data and index files on different directories (e.g. partition 1 on d: and partition 2 on e: or data-files on d: and indexes on e:).

That is not possible on Windows, since it does not have support for symbolic linked files (which is needed for DATA/INDEX DIRECTORY in MyISAM, Archive and partitioned MyISAM, Archive.)

It seems strange to me that the partitioning of a multi-OS database system would rely on OS-specific features such as symbolic linking.  What kind of effort would it take to enhance the data/index directory parameters across MySQL to actually physically locate the separate files where the parameter specifies, rather than relying on symbolic links for such functionality?

Discussed this morning with Matthias and Mikael.

This is not an issue with Partitioning per se, but rather with how the MySQL Server works on Windows.

I have updated the Partitioning and SQL Syntax documentation to indicate that this is a known limitation, and changed this bug's severity to "Feature Request".

According to this page http://en.wikipedia.org/wiki/NTFS_symbolic_link, Windows Vista does support symlinks.

For reference:

In Vista (and probably Windows Server 2008) it is currently possible to manually use symbolic links for MyISAM files (and probably other storage engines). I have tested it on a partitioned table using the following steps:
1. create the table "CREATE TABLE t1 (c1 INT) ENGINE=MyISAM PARTITION BY HASH(c1) PARTITIONS 2;"
2. "FLUSH TABLES;" (to close the files for the table)
3. start a "Command Promt" with "Run as administrator", cd to the database directory.
4. "move <files...> <new disk/directory>"
   "mklink t1#P#p1.MYI <new disk/directory>\t1#P#p1.MYI"
   "mklink t1#P#p1.MYD <new disk/directory>\t1#P#p1.MYD"
   (and so on for every file that should be on another disk/directory)
5. Done, it should now work in mysql! (please use "FLUSH TABLES WITH READ LOCK" if using in a multi user environment, and afterwards "UNLOCK TABLES".)

(I used Windows Vista Ultimate c:-NTFS symlinked e:-FAT32)

We will fix the bug as mentioned above:
symlinks is not supported on Windows. The bug is that it does not ignore the DATA/INDEX
DIR in the partitioning clause with a warning. (Should be fixed the same way as
bug#17498).

and when MyISAM and Archive supports symlinked files on Windows, it should be enabled on Partitioned MyISAM and Archive as well.

Created a new bug for the feature request:
Bug#33687 - Symbolic linked files is not implemented for Vista or Windows Server 2008

Which will not be a part of this bug anymore. Only the failure with DATA/INDEX DIRECTORY in PARTITIONED tables in windows, which will be fixed by ignoring them with a warning.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40553

ChangeSet@1.2650, 2008-01-04 12:06:20+01:00, mattiasj@witty. +7 -0
  Bug#30459 Partitioning across disks failing on Windows
  
  Problem was that there are no support for symlinked files on Windows for
  mysqld. So we fail when trying to create them.
  
  Solution: Ignore the DATA/INDEX DIRECTORY clause for partitions and push
  a warning. (Just like a MyISAM table)

Ok to push.

Ok to push

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented as follows in the 5.1.24 and 6.0.5 changelogs:

        Using the DATA DIRECTORY and INDEX DIRECTORY options for partitions
        with CREATE TABLE or ALTER TABLE statements appeared to work on Windows,
        although they are not supported by MySQL on Windows systems, and
        subsequent attempts to use the tables referenced caused errors. Now
        these options are disabled on Windows, and attempting to use them
        generates a warning.

Also updated create-table and partitioning-overview sections of the 5.1 and 6.0 manuals.