Thread: Mysql 'LOAD DATA INFILE' command line issues

Hi guys, I have a stupidly large csv I need to import into a database, I've done this before and it's worked fine, but today it's just not, which is odd as I don't think I'm doing anything different.

I've FTP'd the csv up to the server, navigated to the folder with the 'myfile.csv' file in it and started mysql as the user with access to that db and run:

Code:

LOAD DATA INFILE 'myfile.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

It worked perfectly when I did this last year, but today I'm getting the error:

Code:

ERROR 1045 (28000): Access denied for user 'myuser'@'%' (using password: YES)

I tried just a simpler version of the command;

Code:

LOAD DATA INFILE 'myfile.csv' INTO TABLE mytable;

But this gives the same error, I'm not too tip-top on command line sql-ing, so any tips would be greatly appreciated. I tried logging in as root to do this, but it looks like there isn't a root user in the mysql table (not sure if that's right, as I can log in as root if I disable passwords), but then running the same command (as root) I get new errors:

Code:

ERROR 29 (HY000): File '/var/lib/mysql/mydatabase/myfile.csv' not found (Errcode: 2)

I thought it should look in the directory I'm currently in on the server (it did that last time), so I tried setting the directory of the file in the mysql command to see if it'd look there:

Code:

mysql> LOAD DATA INFILE '/var/www/vhosts/mydomain.com/httpdocs/uploaded/myfile.csv' INTO TABLE mytable;

And I get this error:

Code:

ERROR 13 (HY000): Can't get stat of '/var/www/vhosts/mydomain.com/httpdocs/uploaded/myfile.csv' (Errcode: 13)

Any suggestions or hints as to where I'm going wrong?

Thanks,

Stu

the last error is due to a rights problem. the MySQL user can't access the file where it is currently stored, either change the rights or move the file to a directory where the MySQL user can access it.