Recipe 10.2. Specifying the Datafile Location

Problem

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

Solution

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

Discussion

You can load files that are located on the server host or on the client host from which you issue the LOAD DATA statement. By default, the MySQL server assumes that the datafile is located on the server host. However, that might not be appropriate in all cases:

• 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 default database. Most MySQL users do not have the FILE privilege (because it enables them to do dangerous things), and you might not want to make the file world readable (for security reasons) or be able to put it in the database directory.

Fortunately, you can load local files that are located on the client host 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. One caveat is that the LOCAL keyword might 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. (Many of the examples in this chapter assume that LOCAL can be used. If that's not true for your system, you'll need to adapt these examples. Omit LOCAL from the statement, make sure that the file is located on the MySQL server host, and specify its pathname using the following rules. For example, specify the full pathname.)

If the LOCAL keyword is not present in the LOAD DATA statement, the MySQL server reads the datafile. It looks for the file 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 such as mytbl.txt, MySQL looks for the file in the database directory for the default database. (The statement will fail if you have not selected a default database.) For a multiple-component filename such as xyz/mytbl.txt, MySQL looks for the file beginning in the MySQL data directory. That is, it expects to find mytbl.txt in a directory named xyz.

Database directories are located directly under the server's data directory, so these two statements are equivalent if the default 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 in the LOAD DATA statement, your client program looks for the file on the client host and sends its contents to the server. The client 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 specifies the location of the file 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 (28000): Access denied for user: 'user_name @host_name ' (Using password: YES) 

That Access denied message can be confusing: 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, right? The meaning of the error message is that the MySQL tried to open mytbl.txt on the server host and could not access it.

If your MySQL server runs on the host from which you issue the LOAD DATA statement, "remote" and "local" refer to the same host. But the rules just discussed for locating datafiles still apply. Without LOCAL, the server reads the datafile. With LOCAL, the client program reads the file and sends its contents to the server.

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

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

% mysqlimport --local cookbook mytbl.txt             

To use LOAD DATA to load a file into a specific database rather than the default database, 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 data-file name and the table name. Specifically, it uses the last component of the filename to determine the table name. For example, mysqlimport interprets mytbl, mytbl.txt, mytbl.dat, /tmp/mytbl.txt, /u/paul/data/mytbl.csv, and C:\projects\mytbl.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, use either doubled backslashes or forward slashes instead. These two statements show two ways of referring to the same Windows file:

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

If the NO_BACKSLASH_ESCAPES SQL mode is enabled, backslash is not special, and you do not double it:

mysql> SET sql_mode = 'NO_BACKSLASH_ESCAPES'; mysql> LOAD DATA LOCAL INFILE 'C:\projects\mydata.txt' INTO mytbl;