0

I am trying to export a MySQL table to a csv file. I am using the following query which I think is correct:

SELECT *  
INTO OUTFILE 'result.csv'  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n'  
FROM Table

However I get the following error message from MySQL when I try running the query in the SQL query box of phpmyadmin:

1045 - Access denied for user 'user'@'localhost' (using password: YES)

I contacted my host and he could not figure it out since it looks like I have all the right permissions.

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Pierre
  • 51
  • 1
  • 8

2 Answers2

2

As documented under Causes of Access-Denied Errors:

If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table does not have the FILE privilege enabled.

eggyal
  • 122,705
  • 18
  • 212
  • 237
1

The issue is your MySQL user does not have FILE privileges. Which means it cannot save output to a file.

GRANT FILE ON [database_name].[table_name] TO 'user'@'localhost';

EDIT: Ignore above. Just looked this up & it seems you cannot grant FILE to just one database. More details here. So you need to do this instead:

GRANT FILE ON *.* TO 'user'@'localhost';

And if your ISP does not grant you FILE access, then perhaps some of the PHP code based tips here can help.

Community
  • 1
  • 1
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
  • Thanks, I tried it but I even get the message when I try your query (GRANT FILE).#1045 - Access denied for user 'user'@'localhost' (using password: YES) – Pierre May 03 '14 at 15:00
  • @user3573826 Check my latest edit. Turns out you cannot grant `FILE` to a single database. So you need to grant it on `*.*`. – Giacomo1968 May 03 '14 at 15:30
  • Thanks but I still get the same "access denied" message. – Pierre May 03 '14 at 15:57
  • @user3573826 If that’s the case, you probably need someone with real MySQL administrative privileges to make the change for you. Since you mention contacting your ISP, I would recommend contacting them with this info. – Giacomo1968 May 03 '14 at 16:18
  • 1
    @user3573826: Many shared hosting providers won't provide you with this privilege, for security reasons. – eggyal May 03 '14 at 21:57
  • My provider does not want to provide me with the FILE privilege. any work around it? – Pierre May 03 '14 at 23:48
  • @user3573826 Well, that answers that. But perhaps you can try the PHP code recommended here. http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv – Giacomo1968 May 03 '14 at 23:49