Bug #20271 LOAD DATA INFILE doesn't work with networked drive in WIN environment
Submitted: 5 Jun 2006 17:51 Modified: 13 Jul 2006 17:33
Reporter: Danny Holstein Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:Windows (Win XP)
Assigned to: Iggy Galarza CPU Architecture:Any

[5 Jun 2006 17:51] Danny Holstein
Description:
The SQL - "LOAD DATA INFILE" works fine with a file on the C: drive, MySQL reports that the file can't be found when reading the SAME file from a networked drive.

   ...Dan

How to repeat:
Use "LOAD DATA INFILE" on a networked file, then the same file from the C: drive.

Suggested fix:
We were able to create a symbolic link from the C: drive to the networked drive as  a workaround -- obviously not a good solution.
[6 Jun 2006 12:52] Valeriy Kravchuk
Thank you for a problem report. Please, specify what exactly do you mean: network share mapped to a drive letter (Z:) or URI (\\server\some_share\file)?
[6 Jun 2006 15:02] Danny Holstein
The error occurs with a network share mapped to a drive such as Z:
[1 Jul 2006 12:22] Valeriy Kravchuk
Verified just as described:

mysql> create table tc(c1 int, c2 char(20), c3 double);
Query OK, 0 rows affected (0.08 sec)

mysql> load data infile 'z:/tl.txt' into table tl;
ERROR 1146 (42S02): Table 'test.tl' doesn't exist
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>dir z:\tl.txt
 Volume in drive Z is MAIN
 Volume Serial Number is D0A3-4245

 Directory of z:\

01.07.2006  15:10                36 tl.txt
               1 File(s)             36 bytes
               0 Dir(s)     594 579 456 bytes free

And yes, if I copy file to a local disk, LOAD DATA INFILE works. It is a Windows-specific bug.
[13 Jul 2006 16:05] Iggy Galarza
I believe this is a Windows limitation not a MySQL bug.

I was able to reproduce the described results with the MySQL server running as a service.
---
mysql> load data infile 'z:/t1.txt' into table t1;
ERROR 29 (HY000): File 'z:\t1.txt' not found (Errcode: 2)
mysql>

When I ran the MySQL server process manually as a logged in user with the proper drive mapping I was not able to reproduce the problem.
---
mysql> load data infile 'z:/t1.txt' into table t1;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

When a Windows service starts as a given user account it does not restore mapped drives.  Mapped drives are only restored during an interactive logon.
[13 Jul 2006 16:27] Danny Holstein
OK, that makes perfect sense.  It would be just as applicable on any other OS as far as I can figure.

It seems inherently confusing, the user with his client (command line client, API client or ODBC) references everything to the client machine and presumes the client file is "magically" copied over to the server to be processed.  It'd be nice if the client were able to carry over the information, otherwise, the documentation needs to be made clear.

   ...Dan
[13 Jul 2006 16:39] Iggy Galarza
According to the docs(http://dev.mysql.com/doc/refman/5.0/en/load-data.html), you should be able to specify the LOCAL keyword in the LOAD DATA INFILE command to have the file read by the client and sent to the server.
[13 Jul 2006 17:33] Danny Holstein
That's a great idea!  You guys have (nearly) everything covered.

   ...Dan
[10 Oct 2006 16:29] Samuel Jones
LOAD DATA LOCAL INFILE is not an option if the IGNORE behavior is undesirable (LOAD DATA LOCAL adds IGNORE as the default, no way to disable IGNORE)