access denied for load data infile in MySQL
I use MySQL queries all the time in PHP, but when I try
LOAD DATA INFILE
I get the following error
#1045 - Access denied for user 'user'@'localhost' (using password: YES)
Does anyone know what this means?
I just ran into this issue as well. I had to add LOCAL to my SQL statement.
For example, this gives the permission problem:
LOAD DATA INFILE '{$file}' INTO TABLE {$table}
Add LOCAL to your statement and the permissions issue should go away. Like so:
LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table}
I had this problem. I searched around and did not find a satisfactory answer. I summarise below the results of my searches.
The access denied error could mean that:
- 'user'@'localhost' does not have the FILE privilege (
GRANT FILE on *.* to user@'localhost'); or, - the file you are trying to load does not exist on the machine running mysql server (if using LOAD DATA INFILE); or,
- the file you are trying to load does not exist on your local machine (if using LOAD DATA LOCAL INFILE); or,
- the file you are trying to load is not world readable (you need the file and all parent directories to be world-readable: chmod 755 directory; and, chmod 744 file.dat)
Try using this command:
load data local infile 'home/data.txt' into table customer;
This should work. It worked in my case.
Ensure your MySQL user has the FILE privilege granted.
If you are on shared web hosting, there is a chance this is blocked by your hosting provider.