0

When running a csv export query in MySQL I am getting an access denied error.

The query in question is:

SELECT id_no, name, address
FROM master
INTO OUTFILE '/Users/home/go_results.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

This results in: 

Access denied for user 'user'@'%' (using password: YES)

The same user can run a range of other select, create and so on queries.

I am using AWS RDS so I do not have root access or I would run from there.

LearningSlowly
  • 8,641
  • 19
  • 55
  • 78

1 Answers1

1

user does not have the required privilege.

From MySQL doc

The file is created on the server host, so you must have the FILE privilege

Execute the following statement:

GRANT FILE ON *.* TO 'user'@'%'

Also note that as @Anthony Neace pointed out in his comment and as is stated in the documentation, the SELECT INTO OUTFILE syntax will create a file on the server. Although your question wasn't related to that so the answer stands.

marekful
  • 14,986
  • 6
  • 37
  • 59
  • 1
    It is precisely related to OP's question because they specified they are in the Amazon RDS environment. It is in-fact the entire reason why this answer won't work for them. `SELECT... INTO OUTFILE` is not supported on Amazon RDS. More details: https://forums.aws.amazon.com/message.jspa?messageID=162499#162499 – Anthony Neace Dec 07 '15 at 16:33
  • Thanks, Anthony! While it won't work on RDS, in this answer there is a resolution to that particular problem the OP specified (Why is there a MySQL permission error for that statement). It is a good practice to let the OP know that he really wanted ask something else, but not to down-vote an answered to what really was asked. – marekful Dec 07 '15 at 16:40
  • No, I disagree; OP included that detail from their initial posting and it is an important part of the question since it invalidates your answer. It is doing a disservice to the OP to suggest that they asked the wrong question because you answered the wrong question. I would _strongly_ agree with you if they edited that detail in after the fact because it is unfair to the answerer to change the nature of the question, but that is not what happened here. – Anthony Neace Dec 07 '15 at 16:46
  • I generally agree with you. While the last line of the question invalidates the question itself from the OP's perspective apart from that last line the information provided may be useful in other cases so I'm not removing my answer. – marekful Dec 07 '15 at 16:51
  • 1
    Thanks guys! My issue is now solved ;) As you say Marekful, the last line changes things. I appreciate your answer but it was Anthony that truly cut to the chase ;) – LearningSlowly Dec 07 '15 at 17:23