Does SELECT ... INTO OUTFILE work?

[vernp]

I get an error when I paste the query into the phpmyadmin sql panel:

Database vernp_play - Table goldtontine running on localhost

Error

SQL-query :

SELECT *
INTO OUTFILE '/home/vernp/data/goldtontine.dat'
FROM `goldtontine`

MySQL said:
#1045 - Access denied for user: 'vernp@localhost' (Using password: YES)

I already tried the obvious, which was to set the permissions on the /home/vernp/data/ directory to 777, but got exactly the same error message.

Is there any way to use the inverse of LOAD DATA INFILE LOCAL where I can write the file into my local PC? All I really want to accomplish here is to get a tab-separated file of the data in the table on my PC...

Thanks for any ideas!

cathy :-)

[sonjay]

The problem doesn't look like a permissions issue on the directory you're trying to write to... It looks like the problem is access to the MySQL server. Have you added your db user to the db, in your HM MySQL control panel?

[vernp]

Yeah, I thought the same thing, but SELECT * FROM `goldtontine` works like a charm...

cathy :-)

[vernp]

:blush: {insert sheepish look} :blush:

phpmyadmin on the export tab has a "CSV" option which does pretty much what I need it to do. No need to write any sql at all!

cathy :-)

[sonjay]

Well, I'm glad you're fixed up, but that still doesn't explain why your SQL query didn't work.

[vernp]

I think I know the answer to that, to... From the mysql manual:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. The file cannot already exist, which among other things prevents files such as `/etc/passwd' and database tables from being destroyed. The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table on the server machine. If you want to create the resulting file on some client host other than the server host, you can't use SELECT ... INTO OUTFILE. In that case, you should instead use some command like mysql -e "SELECT ..." > file_name on the client host to generate the file. SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE;

The FILE privilege can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed using SELECT to transfer its contents to the client host.

(So you could, for example, read /etc/passwd into your mysql db, and then if this is a system without a shadow password file you would have all of the encrypted passwords which you could crack at your leisure.) I'm pretty sure that it's not a good idea for us to have FILE privilege!

cathy :-)

[sonjay]

Well, it looks like you're right. I don't see an option for the "Files" privilege in the HM MySQL control panel, so I guess we don't have that privilege. I've created and deleted files using php, so I guess I just assumed that meant the MySQL would be able to do a SELECT into outfile. But I guess that's a whole different animal.

Maybe Annette or someone in the know will chime in here and confirm, but I'd have to agree -- you're just not allowed to do that.

The good news, it's not needed for what you're trying to do. Nor for most things, I'd guess. I have a cron job set up that automatically exports my db to a .sql file daily and e-mails it to me, so you *can* create files on the server -- just not through the "select into outfile" command.