LOAD DATA INFILE Directory Structure

Question

I've been scouring the internet and attempting to troubleshoot this issue for hours and finally caved and decided to ask for help directly.

I'm running a linux dedicated server and need to load data into my mysql database by running a mysql query executed from a php page on the host machine.

The problem I'm having is I have no idea what the directory structure needs to look like in order to actually find my file and have it be uploaded. I'll give an example below.

LOAD DATA INFILE 'WHAT IN THE WORLD GOES HERE TO MAKE THIS WORK?' 
INTO TABLE customers

The error that returns every time I try to run this is

Error Code: 1045. Access denied for user

I've already checked the permissions and the user has FULL permissions on the appropriate database. From my own research I have found that it can throw this error for a few reasons, one of which being that it cannot find the file I am trying to upload.

My file structure after you get into the public_html folder is as follows:

reports/uploads/fileName.csv

Thanks for any help in advance! This has been driving me insane. If I've just been doing this entirely the wrong way, or there is an easier way to accomplish what I am doing I'm also open to suggestions on that front.

Answer 1

Some thoughts: to use LOAD DATA INFILE on a file present on the server, the user performing the load data must have the FILE privileges. If the file is present client side, use LOAD DATA LOCAL INFILE.

This link is useful: http://dev.mysql.com/doc/refman/5.1/en/load-data.html

It's usually good to test the LOAD FILE with a file in an easily accessible location, such as /tmp to confirm that the privileges are okay. Then you can start to debug access issues to the location where your real file is located.

Hope this helps.

Answer 2

As explained in the documentation:

• If the file name is an absolute path name, the server uses it as given.
• If the file name is a relative path name with one or more leading components, the server searches for the file relative to the server's data directory.
• If a file name with no leading components is given, the server looks for the file in the database directory of the default database.

The public_html folder is irrelevant, since the file is being read by the MySQL server. If your webserver and database servers are different machines, and you're trying to load a file from the client, not the server, you need to use LOAD DATA LOCAL INFILE to specify that. Then the filename will be interpreted relative to the working directory of the client application.