Mysql error 13 hy000 can t get stat of

Jason C

unread,

Nov 17, 2011, 5:14:38 AM11/17/11

to

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?

Joe Makowiec

unread,

Nov 17, 2011, 7:19:12 AM11/17/11

to

The Natural Philosopher

unread,

Nov 17, 2011, 7:30:57 AM11/17/11

to

try running the command manually without php involved

Its not clear what is going on here.

Axel Schwenke

unread,

Nov 17, 2011, 7:37:20 AM11/17/11

to

Jason C <> wrote:

> $local_file = "/home/myacct/www/dir/file.csv";
> $upload = "LOAD DATA INFILE \"$local_file\" ";

...

> 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):
>

> I also ensured that $local_file is chmod to 0755.

~ $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

Jason C

unread,

Nov 17, 2011, 4:46:20 PM11/17/11

to

On Thursday, November 17, 2011 7:30:57 AM UTC-5, The Natural Philosopher wrote:
> try running the command manually without php involved
>
> Its not clear what is going on here.

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';

Jason C

unread,

Nov 17, 2011, 4:40:49 PM11/17/11

to

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?

Jason C

unread,

Nov 17, 2011, 4:55:05 PM11/17/11

to

On Thursday, November 17, 2011 7:37:20 AM UTC-5, Axel Schwenke wrote:
> > I also ensured that $local_file is chmod to 0755.
>
> ~ $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

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.

Axel Schwenke

unread,

Nov 17, 2011, 5:40:14 PM11/17/11

to

Jason C <> wrote:
> On Thursday, November 17, 2011 7:37:20 AM UTC-5, Axel Schwenke wrote:

>> ~ $perror 13
>> OS error code 13: Permission denied
>>
>> So obviously something is wrong with file permissions.

> 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?

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

Jason C

unread,

Nov 18, 2011, 2:34:02 AM11/18/11

to

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.