I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written?

This is the query I was using...

mysql> select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights
into outfile '/tmp/srhs9.txt'
from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname;

Kebbel, John wrote:
> I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written?
>
> This is the query I was using...
>
> mysql> select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights
> into outfile '/tmp/srhs9.txt'
>
> from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname;
>
The user 'mysql' must have write permission in the target directory.

--
Gerald L. Clark
Supplier Systems Corporation

cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home) shows me this for mysql ...

mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false
The home directory for mysql is /var/empty, which does exist. Should I write ~into file~ output to this folder or should I change the mysql home directory to some other folder?

> ----------
> From: Gerald L. Clark
> Sent: Friday, April 27, 2007 10:09 AM
> To: Kebbel, John
> Cc: mysql@lists.mysql.com
> Subject: Re: Permissions and Into Outfile
>
> Kebbel, John wrote:
> > I was trying to write the output of a select statement to a tab-delimited text file. I could not write the file to a folder inside /var/www or to my home file because of permission problems. After a moment's reflection, I realized /tmp had stuff written to it all the time, so its permissions must be wide open. I tried writing the file to /tmp and it worked fine. Since you cannot write to an existing file, you cannot create the file in advance and set its permissions. What is the trick to getting the file to write successfully anywhere you want it to be written?
> >
> > This is the query I was using...
> >
> > mysql> select sched_students.id, firstName, lastName, grade, race, gender, dob, school, phone, program, hsMath, hsEnglish, hsScience, major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI, 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama, 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2, 9SWeights
> > into outfile '/tmp/srhs9.txt'
> >
> > from sched_students, SCHED_COURSES where (sched_students.id = SCHED_COURSES.id) and (grade = 8) order by lastname, firstname;
> >
> The user 'mysql' must have write permission in the target directory.
>
> --
> Gerald L. Clark
> Supplier Systems Corporation
>
>

Kebbel, John wrote:
> cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home) shows me this for mysql ...
>
> mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false
> The home directory for mysql is /var/empty, which does exist. Should I write ~into file~ output to this folder or should I change the mysql home directory to some other folder?
>
>

Neither.
Just make sure that mysql has permission to write in the directory you
want to contain the output file.


--
Gerald L. Clark
Supplier Systems Corporation

Another variant would be, to create a directory under your
www area, and give it <your-user>:mysql 775 permissions, or
maybe <web-server-user>:mysql 775 permissions, depending
on what you want to do with the csv file.

On Fri, April 27, 2007 16:41, Kebbel, John wrote:
> cat /etc/passwd on my Macintosh 10.3 at work (I'm using Linux at home)
> shows me this for mysql ...
>
> mysql:*:74:74:MySQL Server:/var/empty:/usr/bin/false
> The home directory for mysql is /var/empty, which does exist. Should I
> write ~into file~ output to this folder or should I change the mysql home
> directory to some other folder?
>
>> ----------
>> From: Gerald L. Clark
>> Sent: Friday, April 27, 2007 10:09 AM
>> To: Kebbel, John
>> Cc: mysql@lists.mysql.com
>> Subject: Re: Permissions and Into Outfile
>>
>> Kebbel, John wrote:
>> > I was trying to write the output of a select statement to a
>> tab-delimited text file. I could not write the file to a folder
>> inside /var/www or to my home file because of permission problems.
>> After a moment's reflection, I realized /tmp had stuff written to it
>> all the time, so its permissions must be wide open. I tried writing
>> the file to /tmp and it worked fine. Since you cannot write to an
>> existing file, you cannot create the file in advance and set its
>> permissions. What is the trick to getting the file to write
>> successfully anywhere you want it to be written?
>> >
>> > This is the query I was using...
>> >
>> > mysql> select sched_students.id, firstName, lastName, grade, race,
>> gender, dob, school, phone, program, hsMath, hsEnglish, hsScience,
>> major, 9YBand, 9YChorus, 9YGifted, 9YTV, 9YROTC, 9YSpanishI,
>> 9YSpanish2, 9YFrenchI, 9SAerobics, 9SArt, 9SBasketball, 9SDrama,
>> 9SDriverEd1, 9SDriverEd2, 9SRecreation, 9STeamSports1, 9STeamSports2,
>> 9SWeights
>> > into outfile '/tmp/srhs9.txt'
>> >
>> > from sched_students, SCHED_COURSES where (sched_students.id =
>> SCHED_COURSES.id) and (grade = 8) order by lastname, firstname;
>> >
>> The user 'mysql' must have write permission in the target directory.
>>
>> --
>> Gerald L. Clark
>> Supplier Systems Corporation
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens@fumlersoft.dk
>
>
> --
> This message has been scanned for viruses and
> dangerous content by OpenProtect(http://www.openprotect.com), and is
> believed to be clean.
>
>


--
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.