MySQL Bugs: #22264: Can never load a file called 'c:\cfmdata\csv\co\co.csv'. Easily reproduced.

Bug #22264	Can never load a file called 'c:\cfmdata\csv\co\co.csv'. Easily reproduced.
Submitted:	12 Sep 2006 13:33	Modified:	25 Sep 2006 13:19
Reporter:	Joel Dare	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	errcode: 13, file exists, file not found

Description:
Getting the error, "File 'c:\cfmdata\csv\co\co.csv' not found (Errcode: 13)".

When trying to load data infile with a filename of 'c:/cfmdata/csv/co/co.csv', MySQL reports a File not found error, even though the file exists.  Change the co/co to ut/ut and everything works fine.  This is a bug that I created and reproduced under Windows XP.  I reproduced it under MySQL version 4.1.14 and 5.0.24a.  I also reproduced the bug on two different machines.  It seems to have something to do with importing from a filename of 'c:/cfmleads/csv/co/co.csv', although I'm not sure why.  I also tried it with 'c:\\cfmleads\\csv\\co\\co.csv' and get the same result.

I was able to work around the problem by adding the 'LOCAL' keyword (LOAD DATA LOCAL INFILE).  But, the import is being done on the local machine so the LOCAL keyword should not be necessary.

How to repeat:
1. Create a new database called 'bugtestjd' (database name is not important).

2. Create a new table called 'test' in the 'bugtestjd' database (table name is not important).

	CREATE TABLE `bugtestjd`.`test` (
	  `test` VARCHAR(50) NOT NULL
	)
	ENGINE = MYISAM;

3. Create a directory structure for 'c:\cfmleads\csv\co\' (exact directory structure is necessary).

	C:
	CD\
	MKDIR cfmleads
	CD cfmleads
	MKDIR csv
	CD csv
	MKDIR co

4. Create a file in the new directory called 'co.csv' and put only the word 'test' inside it (exact filename is important).

	ECHO test > co.csv

5. Now run the following query to attempt to load the data in from the test file that you just created.

	LOAD DATA INFILE 'c:/cfmdata/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Suggested fix:
There must be some type of interpretation of this filename.  For example "/co/" might be interpreted as a regular expression.  This is only a guess as I do not know the precise part of the filename that is a problem.

Hi Joel,
Please check you didn't make a typo in the directory name. This should work:

LOAD DATA INFILE 'c:/cfmleads/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED
BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

Thank you for a problem report. Please, double check actions you are performing. Look:

	CD\
	MKDIR cfmleads
	CD cfmleads
	MKDIR csv
	CD csv
	MKDIR co

You are creting directory c:\cfmleads\csv\co, and then file co.csv in it:

	ECHO test > co.csv

While in your query:

LOAD DATA INFILE 'c:/cfmdata/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED
BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

pathname 'c:/cfmdata/csv/co/co.csv' (note cfmdata, not cfmleads!) is used. What you expected to get after that?

I assumed the tester would know to "CD co" before creating the file (bad assumption).  My file is physically located at c:\cfmleads\csv\co\co.csv.  Here's a test I ran from the command prompt to verify the location.  Also, I copied and pasted the query so there is no typo in the filename (I also tried it with forward slashes and double backslashes as indicated).

C:\>type c:\cfmleads\csv\co\co.csv
test

Also, to be sure there was no typo, I copied and pasted my query.  This query fails and I'm pasting it directly here:

LOAD DATA INFILE 'c:/cfmdata/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

This query also fails (also pasted):

LOAD DATA INFILE 'c:\\cfmdata\\csv\\co\\co.csv' INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

I worked on this problem for a couple hours before I submitted the bug.  I also had another co-worker take a look.  Both of us thought it must be a bug because we couldn't find any typos.  After that, I reproduced it on my laptop at home.  Then finally I upgraded mysql and reproduced it under MySQL 5.0.24a.

Please, compare:

C:\>type c:\cfmleads\csv\co\co.csv
test

(look, it is 'cfmleads'!)

to

LOAD DATA INFILE 'c:/cfmdata/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED BY
',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

LOOK AGAIN, it is 'cfmdata'. Do you see the difference?

I do see the difference.  My re-creation of the original problem is flawed.

I was using a PHP array and a foreach loop when I discovered the original problem, so I'm fairly sure I didn't have the same typo there.  I'll go back and check this.

Reopen this report when you'll create a new, correct test case.

In case anyone comes accross this, I did reproduce this again under 4.1.14, but it is fixed under 5.0.24a. This *might* be specific to my machine somehow, as well. Here's how I reproduced it (typo's fixed extra data added to test against):

1. Create a new database called 'bugtestjd' (database name is not important).

2. Create a new table called 'test' in the 'bugtestjd' database (table name is
not important).

CREATE TABLE `bugtestjd`.`test` (
`test` VARCHAR(50) NOT NULL
)
ENGINE = MYISAM;

3. Create a directory structure for 'c:\cfmleads\csv\co\', a test directory of 'c:\cfmleads\csv\ut\' and files called co.csv and ut.csv in each of those directories. (exact directory
structure is necessary).

C:
CD\
MKDIR cfmleads
CD cfmleads
MKDIR csv
CD csv
MKDIR co
MKDIR ut
CD co
ECHO test > co.csv
CD ..
CD ut
ECHO test > ut.csv

4. Run a couple quick tests to make sure we got the file right.

C:\cfmleads\csv\ut>type c:\cfmleads\csv\ut\ut.csv
test

C:\cfmleads\csv\ut>type c:\cfmleads\csv\co\co.csv
test

5. Start MySQL and use the bugtestjd database.

USE bugtestjd;

6. Now run the following query to attempt to load the UT data. This one will work.

LOAD DATA INFILE 'c:/cfmleads/csv/ut/ut.csv' INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

7. Now run the following query to attemp to load the CO data.

LOAD DATA INFILE 'c:/cfmleads/csv/co/co.csv' INTO TABLE test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

So, there is no bug in 5.0.24a, but there is, it seems, in 4.1.14, right? Please, try to repeat with the latest version from 4.1.x family, 4.1.21, and inform about the results.

This issue is closed.  It is not reproducable under 4.1.21.  Just to make sure I'm not going crazy, I did re-load 4.1.14 in a virtual PC and ran through my latest set of instructions and the bug did exist in 4.1.14.  I couldn't find a bug report on it, however, so this one may still be useful to someone.

The output of my DOS commands showing that the files exist and then the error under version 4.1.14 are below, in case your curious.

-----

C:\Program Files\MySQL\MySQL Server 4.1\bin>type c:\cfmleads\csv\ut\ut.csv
test

C:\Program Files\MySQL\MySQL Server 4.1\bin>type c:\cfmleads\csv\co\co.csv

C:\Program Files\MySQL\MySQL Server 4.1\bin>mysql -uroot -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.14-nt

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

mysql> LOAD DATA INFILE 'c:/cfmleads/csv/ut/ut.csv' INTO TABLE test FIELDS TERMI
NATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 1 row affected (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE 'c:/cfmleads/csv/co/co.csv' INTO TABLE test FIELDS TERMI
NATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
ERROR 1105 (HY000): File 'c:\cfmleads\csv\co\co.csv' not found (Errcode: 13)

Err.  My copy and pasting fails me.  My output was:

C:\Program Files\MySQL\MySQL Server 4.1\bin>type c:\cfmleads\csv\ut\ut.csv
test

C:\Program Files\MySQL\MySQL Server 4.1\bin>type c:\cfmleads\csv\co\co.csv
test

I missed the last line showing "test" in the output of my previous example.

Anyway, moving on...

duplicate of bug#12325