I have a file that I'm grabbing by FTP via cron, then formatting via PHP. Then, also in PHP, I'm trying to import the formatted CSV file into that table.

Here's the query that I'm using (I'm leaving it in PHP, just in case you see a problem with the escaping):

$local_file = "/home/myacct/www/dir/file.csv";

$upload = "LOAD DATA INFILE \"$local_file\" ";
$upload .= "INTO TABLE table ";
$upload .= "FIELDS TERMINATED BY ',' ";
$upload .= "ENCLOSED BY '\"' ESCAPED BY '\\\\' ";
$upload .= "LINES TERMINATED BY '\\n';";

$sth = mysql_query($upload);
if (!$sth) die(mysql_error());

This script gives me the error of:

Can't get stat of '[filename]' (Errcode: 13)

I've been researching this error, and it seems like the most common explanation is a failed permission. But, I did give the user FILE permission, using (as root):

GRANT FILE ON *.* TO [username]@localhost IDENTIFIED BY '[password]';

I also ensured that $local_file is chmod to 0755.

Any suggestions?

On 17 Nov 2011 in comp.databases.mysql, Jason C wrote:

http://www.google.com/search?q=mysql+can't+get+stat+of+errcode+13

leads to:

http://bugs.mysql.com/bug.php?id=31670

which suggests using 'LOAD DATA local INFILE ...'

I got errorcode 2 on my server, which led more directly to the answer:

http://stackoverflow.com/questions/3...file-errcode-2

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe
Usenet Improvement Project: http://twovoyagers.com/improve-usenet.org/

Joe Makowiec wrote:

Its not clear what is going on here.

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:

....

~ $perror 13
OS error code 13: Permission denied

So obviously something is wrong with file permissions. It must be
world-readable (because it's outside datadir). Check also the
directory where the file resides. Must be readable by the user
that runs the MySQL server.

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html


XL

On Thursday, November 17, 2011 7:19:12 AM UTC-5, Joe Makowiec wrote:
I had actually found that link before, too. I tried to use LOAD DATA LOCAL INFILE last night, but then quickly discovered that I have it disabled for security purposes (due to a strong suggestion from my firewall).

I'm not sure what the security problem is with it, but is this something that should be re-enabled? Is there no other way to do what I need without it?

On Thursday, November 17, 2011 7:30:57 AM UTC-5, The Natural Philosopher wrote:
I did, but had the same error. I only posted it here in PHP in case you guys thought there was an escaping problem (because I spent about 20 minutes trying to figure one of those out, only to find that I needed to escape the \n).

The query I'm pasting directly in to SSH is:

LOAD DATA INFILE '/home/myacct/www/dir/file.csv'
INTO TABLE `table`
FIELDS TERMINATED BY ','
ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';

On Thursday, November 17, 2011 7:37:20 AM UTC-5, Axel Schwenke wrote:
By default, all of my public directories are created at 0755. I double checked, and yes, it's definitely world-readable.

When I FTP to the file as root, though, I see that file.csv has an Owner/Group of root/root (where the PHP file is user/user). Could that be a problem?

It's a live database, so I don't want to run the query again until late tonight, when the traffic is down. Unless you say otherwise, I'll try changing the O/G, and see if that helps.

Jason C <jwcarlton (AT) gmail (DOT) com> wrote:
The file is opened (or tried to) by the mysqld process which is
typically running under it's own uid of 'mysql'. This user must be
able to open the file. If you want to try that, become that user
(su - mysql) and try if you can open the file.

The error message indicates an error at stat'ing the file. So mysqld
did not even check the file permissions - it was denied permission
when trying to do so.

Maybe you're running apparmour or similar? This could also explain
why mysqld couldn't access the file. Check the syslog.


XL

For anyone following, I enabled LOCAL (go to /etc/my.cnf and remove the line that says "local-infile=0", then restart MySQL), and then using LOAD DATA LOCAL INFILE worked without error.

This is apparently a security risk, but I haven't found any other way to load the file without it. I can import a file with phpMyAdmin, though, so I know it's possible... I just haven't found it.