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'@'%

See also What file and directory permissions are required for MySQL LOAD DATA INFILE? (try changing LOAD DATA INFILE 'myfile.txt' to LOAD DATA LOCAL INFILE 'myfile.txt' — Danny Beckett
– Danny Beckett, Commented Jan 20, 2014 at 14:39
possible duplicate of access denied for load data infile in MySQL — blo0p3r
– blo0p3r, Commented Apr 29, 2014 at 17:21
@DannyBeckett solution with 'LOCAL' might not be available for all mysql versions. For instance , for '5.7.34-log' won't work. — mesh
– mesh, Commented May 3, 2022 at 13:44
Correcting myself, The above solution with 'LOCAL' might require an additional set of the property 'allowLoadLocalInfile' to 'TRUE'. — mesh
– mesh, Commented May 3, 2022 at 13:51

Ólafur Waage · Accepted Answer · 2009-06-18 19:39:46Z

40

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

answered Jun 18, 2009 at 19:39

Ólafur Waage

70.2k22 gold badges147 silver badges199 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2009-06-18 19:42:36Z

7

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.

answered Jun 18, 2009 at 19:42

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

Petrogad Over a year ago

Thank you for the description of the problem. Helped me!

Collectives™ on Stack Overflow

Mysql permission errors with 'load data'

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related