Re: LOAD DATA INFILE - what is the path to file?
Chris wrote: Thank you for your detailed response. You're welcome. 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. 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. If I look at: 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. 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. Thanks, Chris P.S. It might be a good idea `man chmod` to review file permissions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - what is the path to file?
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. If I look at: > > 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 "Michael Stassen" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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]
Re: LOAD DATA INFILE - what is the path to file?
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]
Re: LOAD DATA INFILE - what is the path to file?
Hi, load data infile tries to load data from specified directory on the server. if your file is on the client, try LOAD DATA LOCAL and verify the parameter --enable-local-infile more details are in http://dev.mysql.com/doc/mysql/en/load-data-local.html about data on the web server. Nota bene : *** D:\>perror 13 OS error code 13: Permission denied You have an os permission eroor. Mathias Selon Chris <[EMAIL PROTECTED]>: > 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) > > Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) > > 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) > > 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. > > So how would I specify a path to a file that is outside the directory where > my database is located? > > 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. > > Thanks for replies, > Chris > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE - what is the path to file?
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) Can't get stat of '/home/path_to_my_file/datafile.txt' (Errcode: 13) 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) 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. So how would I specify a path to a file that is outside the directory where my database is located? 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. Thanks for replies, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
