Site icon Leonid Mamchenkov

MySQL export CSV into OUTFILE triggers “access denied” error

I came across a weird problem today.  Gladly, the web is full of solutions, but I’m going to post this anyway, just to have it nearby for the next time.  I needed to export the results of some query into a CSV file directly from MySQL.  I prepared my query, made sure that I can see the correct results and than changed it to export into the file. The query looked something like this:

SELECT id, field1, field2, field3
INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM data_table
WHERE field1 = 0;

I was quite surprised to find myself staring at:

ERROR 1045 (28000): Access denied for user ‘db_user’@’localhost’ (using password: YES)

My database user definitely had full access to the database.  I definitely could see the results of the query before the redirect to the file.  And I definitely had enough permissions to create files in /tmp directory.  And on top of that, I’m sure I used MySQL export functionality a gadzillion times and it always worked without any problems.   What’s wrong this time?

A quick search around got me to this Stack Overflow question.  Apparently, database user has to be given a FILE privilege, which is global (not per-database).   Here is what I did to solve the problem (you’ll need to use MySQL root user of course):

USE mysql;
UPDATE user SET File_priv = 'Y' WHERE User = 'db_user';
FLUSH PRIVILEGES;

I think that it worked for me before was because I exported as root, who does have this permission set to ‘Y’.

Exit mobile version