problems with FILE priv granting

[news]

I've a PHP script that is supposed to write results to a file using
INTO OUTFILE, but it's not.

The mysql_error says: Could not write file: Can't create/write to file
'/var/www/html/accounts/samplesdownload/1136572640.txt' (Errcode: 2)

The permissions on that folder has "a+w -R" chmoded to it, and in fact,
other scripts can write to it, but not from a SQL querey.

So looking to mySQL I look at the privileges for the user account being
used for that database.
And the mysql.db table, the user has Y for all privileges, but there's
no column for the FILE privilege!

So I grant the user global FILE privilege and flush, and still no
change in the error.

Any feedback as to what direction to go with this would be greatly
appreciated!
-Liam

[Markus Popp]

Hi,

IIRC, the SELECT ... INTO OUTFILE command can't write to a file, if the file
already exists. So make sure that you delete an existing file (e.g. with the
PHP unlink() command) before you create a new one.

Markus

[news]

Markus Popp wrote:

Hi,

IIRC, the SELECT ... INTO OUTFILE command can't write to a file, if the file
already exists. So make sure that you delete an existing file (e.g. with the
PHP unlink() command) before you create a new one.

Markus

Good tip!
Except in this case the name of the file is a unix timestamp, so it's
always unique. =)
Thanks for the reply!
-Liam

[news]

ne**@celticbear.com wrote:

I've a PHP script that is supposed to write results to a file using
INTO OUTFILE, but it's not.

The mysql_error says: Could not write file: Can't create/write to file
'/var/www/html/accounts/samplesdownload/1136572640.txt' (Errcode: 2)

The permissions on that folder has "a+w -R" chmoded to it, and in fact,
other scripts can write to it, but not from a SQL querey.

So looking to mySQL I look at the privileges for the user account being
used for that database.
And the mysql.db table, the user has Y for all privileges, but there's
no column for the FILE privilege!

So I grant the user global FILE privilege and flush, and still no
change in the error.

Any feedback as to what direction to go with this would be greatly
appreciated!
-Liam

Oh yeah, forgot to mention.
I also tried:

mysql> GRANT FILE ON printing.* TO 'usernamet'@'servername';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Guess the fact there's no File column on the db table would explain why
that failed. =/

-Liam

[Markus Popp]

The FILE privilege can only be granted on the user level - that's alright.
If the user has FILE privileges (you can check it with SHOW GRANTS) and you
have write privileges in the directory you want to write to (you can check
it with the 'ls -l' command) and the file you want to create doesn't exist,
then it has to work.

Take care that the SELECT ... INTO OUTFILE command writes to the file system
of the database server, so if you have a remote MySQL server, it will write
the data to the remote file system, not to your local one.

These are all conditions that should apply to using this command, but if it
still doesn't work, I cannot do more than to refer you to

http://dev.mysql.com/doc/refman/5.0/en/privileges.html

http://dev.mysql.com/doc/refman/5.0/en/select.html

and if it still doesn't work, to file a bug report.

Markus

[news]

Markus Popp wrote:
[..]

Take care that the SELECT ... INTO OUTFILE command writes to the file system
of the database server, so if you have a remote MySQL server, it will write
the data to the remote file system, not to your local one.

[..]

Oh! That's the problem. The mySQL server IS a remote server.
I'll have to OUTFILE to that server and then scp it over to the Web
server I guess.
Thanks for the reply!
-Liam