MySQL Bugs: #28520: load data infile cannot resolve files on a substituted drive (windows 'subst')

Bug #28520	load data infile cannot resolve files on a substituted drive (windows 'subst')
Submitted:	18 May 2007 14:53	Modified:	31 May 2007 16:11
Reporter:	Quartz 12h	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10/5.0/5.1	OS:	Windows (xp sp2)
Assigned to:	Iggy Galarza	CPU Architecture:	Any

Description:
load data infile cannot resolve files on a substituted drive (windows 'subst')

I used to setup an application directory tree and and make that subfolder a 'subst' drive root.

Example commandline on windows:
   subst f: c:\foo\bar\project\version6.1

So this path becomes the F drive.
Now, I generate files for 'load data infile' and place them in under such drive\path. Example F:\infiledata\data.csv but the server complains it cannot find the file (error 2)

If I use the full path c:\foo\bar\project\version6.1\infiledata\data.csv
however, it does work.

Note, I have also tested and it fails with UNC paths like 

'\\\\MyServer\\General\\Public Folder\\data.csv'
(error 13, permission denied even though I definitely have r/w permission)

or

'\\\\tangent.somehost.com\\public\\bob\\data.csv'
(error 22, invalid argument, which indicates it doesn't like the hostname?)

Clearly, mysqld is not resolving the filenames the standard way through regular windows APIs.

How to repeat:
Create a substituted drive, place an infile there and try to 'load data infile'

Thank you for the bug report.

I was unable to reproduce the problem with the information provided.  Please 
clarify the steps to reproduce.  

Here is a summary of my testing:

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ subst g: c:\\testsub

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ cd g:

iggy@winbuild /cygdrive/g
$ ls

iggy@winbuild /cygdrive/g
$ echo "a b c d" > `cygpath 'c:\testsub\t1.txt'`

iggy@winbuild /cygdrive/g
$ ls
t1.txt

iggy@winbuild /cygdrive/g
$ cat t1.txt
a b c d

iggy@winbuild /cygdrive/r
$ cd r:/mysql-5.0-maint_pt/mysql-test/

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ export MTR_VS_CONFIG=debug

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ export MTR_BUILD_THREAD=50

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ ./mysql-test-run.pl --start-and-exit --mysqld="--secure-file-priv=g:"

...

Servers started, exiting

iggy@winbuild /cygdrive/r/mysql-5.0-maint_pt/mysql-test
$ ../client/debug/mysql.exe -uroot -P 10500
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.44-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test; use test;
Query OK, 1 row affected (0.02 sec)

Database changed
mysql> create table t1 (c1 VARCHAR(2), c2 VARCHAR(2), c3 VARCHAR(2), c4 VARCHAR(
2));
Query OK, 0 rows affected (0.02 sec)

mysql> load data LOCAL infile 'g:\\t1.txt' into table t1 fields terminated by '
' lines terminated by '\n';
Query OK, 1 row affected (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data infile 'g:\\t1.txt' into table t1 fields terminated by ' ' line
s terminated by '\n';
Query OK, 1 row affected (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Here how to repeat:

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

c:\>e:

E:\>dir manager-report\nov
 O volume na unidade E não tem nome.
 O Número de Série do Volume é 5CF4-4782

 Pasta de E:\manager-report\nov

29/05/2007  18:39    <DIR>          .
29/05/2007  18:39    <DIR>          ..
27/05/2007  18:17           317.261 dez01a.txt
               1 arquivo(s)        317.261 bytes
               2 pasta(s)      260.050.944 bytes disponíveis

E:\>subst k: e:\manager-report\nov

E:\>dir k:\
 O volume na unidade K não tem nome.
 O Número de Série do Volume é 5CF4-4782

 Pasta de k:\

29/05/2007  18:39    <DIR>          .
29/05/2007  18:39    <DIR>          ..
27/05/2007  18:17           317.261 dez01a.txt
               1 arquivo(s)        317.261 bytes
               2 pasta(s)      260.050.944 bytes disponíveis

E:\>

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use report
Database changed
mysql> load data infile 'K:/dez01a.txt' ignore into table dbdsep1 fields terminated by ',';
ERROR 29 (HY000): File 'K:\dez01a.txt' not found (Errcode: 2)
mysql> load data infile 'E:/manager-report/nov/dez01a.txt' ignore into table dbdsep1 fields terminated by ',';
Query OK, 235 rows affected, 927 warnings (9.55 sec)
Records: 1827  Deleted: 0  Skipped: 1592  Warnings: 926

Please attempt an additional test using the LOCAL keyword with the LOAD DATA INFILE statement to force the data to be loaded from the client. Also, please specify how the server was started and with what credentials.

The privileges were 'all' granted.

Streaming the data via LOCAL loading from client side is not an option for me
(performance wise). Meanwhile it is pointless to assert if the client stacks are working with substituted drives; it is a mysql server bug.

Thanks for your comments.  I am not able to reproduce the bug as described.  I can successfully LOAD DATA INFILE from both the server and client. Please see my comments dated 29 May 23:24 above for the steps I followed.  In my successful test,  mysqld was started as a process by the same user who executed the SUBST command.  The SUBST command is user session dependent, meaning that if UserA successfully issues a SUBST command then UserB may not access the virtual drive.  Also, the SUBST command is not persistent (you have to issue the command each logon) and will never be available to a process running as a service on Windows. Therefore, I'm marking this as Not a Bug.

Bug: http://bugs.mysql.com/bug.php?id=41113 has been marked as duplicate of this one.