Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

LOAD DATA INFILE cannot find the file it was given

ErrorCan't get stat of '/home/me/data.csv' (OS errno 2 - No such file or directory)

Short answer

Errcode 2 is the operating system's "No such file or directory". The MySQL server looked for your file on its own host and did not find it there. The usual reasons: the file lives on the client machine and LOCAL was left out, a relative path is read from the data directory, or Windows backslashes were turned into escape characters.

  • Applies to MySQL 8.4, mysql client 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the statement exactly as it is and read the path inside the quotes of the error. That is the path the server tried to open, after MySQL processed your string. Then check from the machine where the server runs.

    -- in the mysql client:
    SHOW VARIABLES LIKE 'datadir';
    SELECT @@hostname;
    -- in a shell on that host:
    ls -l /path/shown/in/the/error
  2. 2Match what you see, then jump to its fix

    1. 1The file opens fine in your editor, the server runs on another machine, in a container or in a VM, and the statement has no LOCAL keywordThe file is on your computer, but the server reads from its own hostFix
    2. 2The path in the error starts with the data directory or the folder of the current database, not with the folder you were inA relative path is read from the data directory, not from your shell folderFix
    3. 3The path in the error has lost its backslashes or contains odd characters, for example a tab or a line break in the middleBackslashes in a Windows path were read as escape sequencesFix

What does the error look like?

  • Can't get stat of '/home/me/data.csv' (OS errno 2 - No such file or directory)
  • Can't get stat of 'data.csv' (Errcode: 2)

Cause 1 of 3

The file is on your computer, but the server reads from its own host

1Check

Without LOCAL, the file must be on the server host and the server reads it directly. With LOCAL, the client program reads it from the client host. Compare the host from SELECT @@hostname with the machine that holds the file.

SELECT @@hostname, @@datadir;

2Fix

Use LOCAL so that the client sends the file, or copy the file onto the server host and use its path there. LOCAL only works when both server and client allow it. See the related pages for the errors that follow if they do not.

LOAD DATA LOCAL INFILE '/home/me/data.csv'
INTO TABLE items
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

3Verify

The statement returns a row count instead of the error.

SELECT COUNT(*) FROM items;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A relative path is read from the data directory, not from your shell folder

1Check

For a server-side load, a relative name with leading components such as ./myfile.txt is looked up under the data directory, and a bare name such as myfile.txt under the folder of the default database. Only with LOCAL is a relative name looked up from where the client was started.

SELECT DATABASE();
SHOW VARIABLES LIKE 'datadir';

2Fix

Give an absolute path that exists on the server host. The server can read it only if it is allowed to: check the FILE privilege and secure_file_priv, described on the related pages.

LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE items
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

3Verify

ls shows the file at that absolute path, on the server host, and the load runs.

ls -l /var/lib/mysql-files/data.csv

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Backslashes in a Windows path were read as escape sequences

1Check

In a MySQL string, \n is a newline, \t a tab and \r a carriage return, so 'C:\new\table.csv' is not the path you typed. The error message prints the path after this processing.

SELECT 'C:\new\table.csv' AS what_the_server_sees;

2Fix

On Windows write path separators as forward slashes or as doubled backslashes.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.4/Uploads/table.csv'
INTO TABLE items
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

3Verify

The path shown by an error, if one still appears, matches the real folder.

SELECT 'C:/ProgramData/MySQL/MySQL Server 8.4/Uploads/table.csv' AS what_the_server_sees;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Decide once whether the file is read by the server or by the client, and write LOCAL or not on purpose.
  • Use absolute paths in every LOAD DATA statement that runs from a script.
  • On Windows use forward slashes in paths, in SQL and in option files.
  • Run perror 2 when you meet an unfamiliar Errcode, to see the plain-language message.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.