MySQL Bugs: #87761: Alter table partitioning data directory complains about table name

Bug #87761	Alter table partitioning data directory complains about table name
Submitted:	14 Sep 2017 18:15	Modified:	21 Sep 2017 16:02
Reporter:	Gregg Wonderly	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.6.21	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	directory, name, partition, smb

Description:
When creating partitioning on a table, we need to use a data directory which is on a NAS file server (via 10gig networking).  I am trying to use partition declarations of the form

	PARTITION 00jan VALUES LESS THAN (2) 
              DATA DIRECTORY '\\\\FS8600\\MySqlData\\others\\00jan',

This results in two different errors, depending on where its used. During table creation, we get 

    SQL Error (1030): Got error -1 from storage engine

error dialog.  When used with alter table, we get

    SQL Error (1103): Incorrect table name '//FS8600/MySqlData/others/00jan'

error dialog.

  Replacing the `\\\\FS8600' component of the path with `c:' allows the operation to succeed.  I don't know whether I have a permission problem (I have checked and all seems well), or if there is some other problem.  It may just be that UNC paths are not adequately supported for "DATA DIRECTORY" on a partition declaration.  Our my.ini has

      data_directory=\\fs8600\MySqlData\data

in it, and is working fine.

There needs to be better error messages (the actual errors) for these so that I know if there is a filesystem/access error, or the use of a UNC path needs to be supported here.  I did some testing a few months back and had created a working partitioning with UNC references.  So, I am not sure what as happened.

How to repeat:
drop table if exists testtable;
CREATE TABLE `testtable` (
	`NodeRefId` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
	`NodeDateTime` DATETIME NOT NULL,
	`NodeValue` MEDIUMTEXT NOT NULL,
	`NodeDateTimeOffset` DECIMAL(3,1) NULL DEFAULT NULL,
	`NodeQuality` INT(11) NOT NULL,
	`Message` TEXT NULL,
	`DuplicateCount` INT(11) NULL DEFAULT '0',
	`CreateDate` DATETIME NOT NULL,
	`UpdateDate` DATETIME NOT NULL,
	PRIMARY KEY (`NodeRefId`, `NodeDateTime`)
);
show create table `testtable`;
ALTER TABLE `testtable` 
# This is 60 partitions from 12 months * 5 years worth of keys.  2017-09-XX goes into the
# partition where the value is less than 209, which is 02aug.
PARTITION BY RANGE ( ((year(`nodedatetime`) mod 5) * 100 ) + month(`nodedatetime`))
SUBPARTITION BY KEY (noderefid)
# use 20 subpartitions on noderefid to group like readings into the same files so that there is
# less data/indexes loaded to retrieve the complete set of readings in the month.
SUBPARTITIONS 2
(
	# Year 0
	PARTITION 00jan VALUES LESS THAN (maxvalue) DATA DIRECTORY '\\\\localhost\\MySqlDatabase\others'
)

Hi,

Thanks for the report. I agree having more verbose errors would be a good thing. 

all best
Bogdan

Right now, MySQL does not support using alter table with 'data directory' in the partition declaration.  This seems a bit short sighted.  If partitioning can cause a restructure of the file usage and total number of partitions etc., then why can't 'data directory' explicitly indicate file paths to use?

Additionally, we discovered that when using a UNC path for creating a table with partitions using DATA DIRECTORY paths, that MySQL has problems validating path components when part of the path structure does not exist.

For example, with the \\FS8600\MySqlData\others\01sep path, if "others" does not exist yet, the path validation being used, starts trying to "create" \\FS8600 component as a directory, which fails and causes the abort of the create table.   If the \\FS8600\MySqlData\others directories all exist, then the DATA DIRECTORY path works and mysql creates the 01sep directory and moves on successfully.  

It seems that the code which processes the error in creating \\FS8600\MySqlData\others\01sep maybe not trying to understand the differences between UNC paths and normal paths and is thus confused by the UNC string.