LOAD DATA INFILE - what is the path to file?

[Chris]

Thank you for your detailed response.

It seems my problem is trying to define the path to my data file and this is
where I seem to be missing something. Permissions on all directories in the
path are by default set to 755 except for the director at the top of the
directories in my hosting account public_html which is set to 750.

LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

which works from my tmp directory. The tmp directory is at the same level as
public_html and has permissions 700. So I guess I don't know why I can't
specify the location of my data file from
'/public_html/path_to_my_file/datafile.txt'

Thanks,
Chris

I have been using LOAD DATA INFILE to load an ASCII data file into my
database. The datafile is uploaded to the server temp area and the name

of

LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
I now want to load data using LOAD DATA INFILE from a data file located
within my http_public directory. I can create a path to the file from my
DOCUMENT_ROOT, however passing that path to LOAD DATA INFILE generates

this

error: (NOTE: I set the file permissions to 777)

Don't do that, it's horribly insecure. Anybody could modify this file
before mysql loads it. You should probably never make a file
world-writable. Mysql only needs to read the file, so set the
permissions to 744. Better yet, make it owned by the mysql group, and
set permissions to 740.

Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13)

~: perror 13
OS error code 13: Permission denied
In order to read the file, the mysql user must have read permission on
the file (you've done that), and must have execute permission on every
directory in the path to the file. So, for mysql to read
/home/path/to/file/datafile.txt, you will need to set permissions of 711
on /home, /home/path, /home/path/to, and /home/path/to/file, in addition
to the 744 permissions on datafile.txt.

LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
I get this error
File './my_database_name/datafile.txt' not found (Errcode: 2)

~: perror 2
OS error code 2: No such file or directory

Which seems to tell me that LOAD DATA INFILE is looking for my data file

in

a location that is outside my hosting account. I just have an account

with a

shared hosting service provider.

Without a leading /, the path is treated as a relative path -- relative
to the server's data directory. Your file isn't there, hence the error.

So how would I specify a path to a file that is outside the directory

where

my database is located?

With a full path, as you did originally. You just have to make sure
mysql has all the permissions neede to access it.

OBSERVATION: It appears the tmp directory must be in the database path
because, files uploaded to the tmp dir can be loaded using LOAD DATA

INFILE.

No, /tmp works because it (usually) has 1777 permissions, so mysql has
the necessary execute permission to access /tmp's contents.

Thanks for replies,
Chris

Michael

[Michael Stassen]

Post by Chris
Thank you for your detailed response.

You're welcome.

Post by Chris
It seems my problem is trying to define the path to my data file and this is
where I seem to be missing something.

No, the problem is permissions. As I explained previously, a relative
path (one without a leading /) means a location under mysql's data
directory, not a location relative to your script. That's why you get a
"No such file or directory" error when you tried a relative path.
Hence, to load a file not under mysql's data directory, you must use an
absolute path (one starting with /), as you did originally. Note that
with the absolute path, you got a "Permission denied" error, not a "No
such file or directory" error. Mysql could not read the path you gave it.

Post by Chris
Permissions on all directories in the
path are by default set to 755 except for the director at the top of the
directories in my hosting account public_html which is set to 750.

As I said, *every* directory in the path must be readable by mysql.
Your top directory has 750 permissions, so, unless it is owned by user
mysql or in the mysql group, mysql cannot access it. Either set it to
751, or change it to group mysql.

Post by Chris

LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

which works from my tmp directory. The tmp directory is at the same level as
public_html and has permissions 700.

I'm sorry, but I just don't believe that. Permissions are user, group,
world, in that order, so 700 means only the owner can access /tmp. If
mysql can access /tmp, either /tmp is owned by mysql, which would be
strange, or it doesn't have 700 permissions. Do this

cd / && ls -aFl

and include the lines for . and tmp and public_html in your next post.

Post by Chris
So I guess I don't know why I can't
specify the location of my data file from
'/public_html/path_to_my_file/datafile.txt'

You can, if you can fix the permissions.

Post by Chris
Thanks,
Chris

P.S. It might be a good idea `man chmod` to review file permissions.