Mysql permission errors with 'load data'

Question

I am running into a permission error when trying to load data from a flat file database dump into a new table. I know that the schema of the file and my table is the same and I tried tweaking the permissions. What else should I try?

mysql> load data infile 'myfile.txt' into table mytable fields terminated by ',' enclosed by '"';
ERROR 1045 (28000): Access denied for user 'user'@'%' 

grant all on mytable.* to 'user'@'%

Answer 1

Here's a thread on the MySQL forums that discusses exactly this.

Here's the answer, posted by Ken Tassell

Problem resolved using the command below:

grant file on *.* to kentest@localhost identified by 'kentest1';

Answer 2

To read a file with LOAD DATA INFILE from the server, the user needs FILE privilege, and I don't think that's granted by default when you use "ALL" - grant that privilege and you should be fine.

Answer 3

You might have MySQL privileges on the destination table, but you also need the FILE privilege to execute LOAD DATA, and of course the MySQL Server process needs operating-system privileges to the data file too.