Specifying the Datafile Location

10.3.1 Problem

You're not sure how to tell LOAD DATA where to look for your datafile, particularly if it's located in another directory.

10.3.2 Solution

It's a matter of knowing the rules that determine where MySQL looks for the file.

10.3.3 Discussion

When you issue a LOAD DATA statement, the MySQL server normally assumes the datafile is located on the server host. However, you may not be able to load data that way:

• If you access the MySQL server from a remote client host and have no means of transferring your file to the server host (such as a login account there), you won't be able to put the file on the server.
• Even if you have a login account on the server host, your MySQL account must be enabled with the FILE privilege, and the file to be loaded must be either world readable or located in the data directory for the current database. Most MySQL users do not have the FILE privilege (because it allows you to do dangerous things), and you may not want to make the file world readable (for security reasons) or be able to put it in the database directory.

Fortunately, if you have MySQL 3.22.15 or later, you can load local files that are located on the client host by using LOAD DATA LOCAL rather than LOAD DATA. The only permission you need to import a local file is the ability to read the file yourself.[2]

[2] As of MySQL 3.23.49, use of the LOCAL keyword may be disabled by default. You may be able to turn it on using the --local-infile option for mysql. If that doesn't work, your server has been configured not to allow LOAD DATA LOCAL at all.

If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using the following rules:

• An absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. MySQL reads the file from the given location.
• A relative pathname is interpreted two ways, depending on whether it has a single component or multiple components. For a single-component filename like mytbl.txt, MySQL looks for the file in the database directory for the current database. For a multiple-component filename like xyz/mytbl.txt, MySQL looks for the file beginning in the MySQL data directory. (It expects to find mytbl.txt in a directory named xyz.)

Database directories are located directly under the data directory, so these two statements are equivalent if the current database is cookbook:

mysql> LOAD DATA INFILE 'mytbl.txt' INTO TABLE mytbl;
mysql> LOAD DATA INFILE 'cookbook/mytbl.txt' INTO TABLE mytbl;

If the LOCAL keyword is specified, MySQL looks for the file on the client host, and interprets the pathname the same way your command interpreter does:

• An absolute pathname fully specifies the location of the file, beginning from the root of the filesystem.
• A relative pathname is interpreted relative to your current directory.

If your file is located on the client host, but you forget to indicate that it's local, you'll get an error.

mysql> LOAD DATA 'mytbl.txt' INTO TABLE mytbl;
ERROR 1045: Access denied for user: 'cbuser@localhost' (Using password: YES)

That Access denied message can be confusing, given that if you're able to connect to the server and issue the LOAD DATA statement, it would seem that you've already gained access to MySQL. What the error message means is that the MySQL tried to open mytbl.txt on the server host and could not access it.

mysqlimport uses the same rules for finding files as LOAD DATA. By default, it assumes the datafile is located on the server host. To use a local file, specify the --local (or -L) option on the command line.

LOAD DATA assumes the table is located in the current database unless you specify the database name explicitly. mysqlimport always requires a database argument:

% mysqlimport --local cookbook mytbl.txt

If you want to use LOAD DATA to load a file into a database other than the current one, you can qualify the table name with the database name. The following statement does this, indicating that the mytbl table is located in the other_db database:

mysql> LOAD DATA LOCAL 'mytbl.txt' INTO TABLE other_db.mytbl;

LOAD DATA assumes no relationship between the name of the datafile and the name of the table into which you're loading the file's contents. mysqlimport assumes a fixed relationship between the datafile name and the table name. Specifically, it uses the last component of the filename to determine the table name. For example, mysqlimport would interpret mytbl.txt, mytbl.dat, /tmp/mytbl.txt, /u/paul/data/mytbl.csv, and D:projectsmytbl.txt all as files containing data for the mytbl table.

Naming Datafiles Under Windows

Windows systems use as the pathname separator in filenames. That's a bit of a problem, because MySQL interprets backslash as the escape character in string values. To specify a Windows pathname, either use doubled backslashes, or use forward slashes instead. These two statements show two ways of referring to the same Windows file:

mysql> LOAD DATA LOCAL INFILE 'D:\projects\mydata.txt' INTO mytbl;

mysql> LOAD DATA LOCAL INFILE 'D:/projects/mydata.txt' INTO mytbl;