Chris wrote:
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
the file is passed to LOAD DATA INFILE query like:
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.
Now if I just create a query like:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
