Select INTO OUTFILE

------=_NextPart_000_00E0_01C522A1.D57A2C80
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,



I want to use Select INTO OUTFILE to create a simple delimited file

I am running it locally on the same server that the database resides on

We are running MySQL version 4.0.22

I have tried running directly in MySQL and also from the command line by
redirecting the source sql



Here is the Select

SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\r\n'

FROM TB_FCT_Address;



From the command line I do the following and get the following error
message - I know the login info is good because if I redirect in a
simple program that does a basic select it works fine

mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
sqlOutFileAddress.sql

ERROR 1045 at line 1: Access denied for user:
'securer_devUser@localhost' (Using password: NO)



If I copy the select directly into a mySQL session I get a similar error
message

ERROR 1045: Access denied for user: 'securer_devUser@localhost' (Using
password: YES)



The user that I have logged in as has write permissions in the directory
specified in the Select INTO OUTFILE



Any help would be appreciated



Thanks



Eric Gewirtz

SolutionOne

Phone - 845-729-7800

Fax - 845-279-5502

egewirtz@rcn.com




------=_NextPart_000_00E0_01C522A1.D57A2C80--

Re: Select INTO OUTFILE

Hello Eric,

Try using mysqldump.exe and the -T option to dump your database, check
the manual for the syntax.

Good luck,

On Sun, 6 Mar 2005 23:11:25 -0500, Eric Gewirtz wrote:
> Hi,
>
> I want to use Select INTO OUTFILE to create a simple delimited file
>
> I am running it locally on the same server that the database resides on
>
> We are running MySQL version 4.0.22
>
> I have tried running directly in MySQL and also from the command line by
> redirecting the source sql
>
> Here is the Select
>
> SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'
>
> FIELDS TERMINATED BY '|'
>
> LINES TERMINATED BY '\r\n'
>
> FROM TB_FCT_Address;
>
> From the command line I do the following and get the following error
> message - I know the login info is good because if I redirect in a
> simple program that does a basic select it works fine
>
> mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
> sqlOutFileAddress.sql
>
> ERROR 1045 at line 1: Access denied for user:
> 'securer_devUser@localhost' (Using password: NO)
>
> If I copy the select directly into a mySQL session I get a similar error
> message
>
> ERROR 1045: Access denied for user: 'securer_devUser@localhost' (Using
> password: YES)
>
> The user that I have logged in as has write permissions in the directory
> specified in the Select INTO OUTFILE
>
> Any help would be appreciated
>
> Thanks
>
> Eric Gewirtz
>
> SolutionOne
>
> Phone - 845-729-7800
>
> Fax - 845-279-5502
>
> egewirtz@rcn.com
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Select INTO OUTFILE

--=_alternative 006FB77885256FBD_=
Content-Type: text/plain; charset="US-ASCII"

"Eric Gewirtz" wrote on 03/06/2005 11:11:25 PM:

> Hi,
>
>
>
> I want to use Select INTO OUTFILE to create a simple delimited file
>
> I am running it locally on the same server that the database resides on
>
> We are running MySQL version 4.0.22
>
> I have tried running directly in MySQL and also from the command line by
> redirecting the source sql
>
>
>
> Here is the Select
>
> SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'
>
> FIELDS TERMINATED BY '|'
>
> LINES TERMINATED BY '\r\n'
>
> FROM TB_FCT_Address;
>
>
>
> From the command line I do the following and get the following error
> message - I know the login info is good because if I redirect in a
> simple program that does a basic select it works fine
>
> mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
> sqlOutFileAddress.sql
>
> ERROR 1045 at line 1: Access denied for user:
> 'securer_devUser@localhost' (Using password: NO)
>
>
>
> If I copy the select directly into a mySQL session I get a similar error
> message
>
> ERROR 1045: Access denied for user: 'securer_devUser@localhost' (Using
> password: YES)
>
>
>
> The user that I have logged in as has write permissions in the directory
> specified in the Select INTO OUTFILE
>
>
>
> Any help would be appreciated
>
>
>
> Thanks
>
>
>
> Eric Gewirtz
>
> SolutionOne
>
> Phone - 845-729-7800
>
> Fax - 845-279-5502
>
> egewirtz@rcn.com
>


Review the results of:

SHOW GRANTS FOR 'securer_devUser'@'localhost';

From the manual (http://dev.mysql.com/doc/mysql/en/select.html):
>>>>>>>>>>>>>>>>>>>
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 currently
exist, which among other things prevents files such as /etc/passwd and
database tables from being destroyed.
<<<<<<<<<<<<<<<<

The FILE privilege they discuss is a MYSQL permission (a GRANT) not an OS
permission. The OS rights to write the file to disk must exist for the
account that the MySQL server processes is running under, not the mysql
user account being used to execute the command. Make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 006FB77885256FBD_=--

RE: Select INTO OUTFILE

Thanks Shawn, this was helpful. I'm now having an issuing granting the
"FILE" privilege to the user.

GRANT FILE ON xxxxxxxx TO 'securer_devUser@localhost' IDENTIFIED BY
'xxxxxxxxxx';
ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual
which privileges can be used.

Why would I be prevented from issuing the grant, assuming I have the
privilege to issue it?

Eric

Eric Gewirtz
SolutionOne
Phone - 845-729-7800
Fax - 845-279-5502
egewirtz@rcn.com


>-----Original Message-----
>From: SGreen@unimin.com [mailto:SGreen@unimin.com]
>Sent: Monday, March 07, 2005 3:17 PM
>To: Eric Gewirtz
>Cc: win32@lists.mysql.com
>Subject: Re: Select INTO OUTFILE
>
>"Eric Gewirtz" wrote on 03/06/2005 11:11:25 PM:
>
>> Hi,
>>
>>
>>
>> I want to use Select INTO OUTFILE to create a simple delimited file
>>
>> I am running it locally on the same server that the database resides
on
>>
>> We are running MySQL version 4.0.22
>>
>> I have tried running directly in MySQL and also from the command line
by
>> redirecting the source sql
>>
>>
>>
>> Here is the Select
>>
>> SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'
>>
>> FIELDS TERMINATED BY '|'
>>
>> LINES TERMINATED BY '\r\n'
>>
>> FROM TB_FCT_Address;
>>
>>
>>
>> From the command line I do the following and get the following error
>> message - I know the login info is good because if I redirect in a
>> simple program that does a basic select it works fine
>>
>> mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
>> sqlOutFileAddress.sql
>>
>> ERROR 1045 at line 1: Access denied for user:
>> 'securer_devUser@localhost' (Using password: NO)
>>
>>
>>
>> If I copy the select directly into a mySQL session I get a similar
error
>> message
>>
>> ERROR 1045: Access denied for user: 'securer_devUser@localhost'
(Using
>> password: YES)
>>
>>
>>
>> The user that I have logged in as has write permissions in the
directory
>> specified in the Select INTO OUTFILE
>>
>>
>>
>> Any help would be appreciated
>>
>>
>>
>> Thanks
>>
>>
>>
>> Eric Gewirtz
>>
>> SolutionOne
>>
>> Phone - 845-729-7800
>>
>> Fax - 845-279-5502
>>
>> egewirtz@rcn.com
>>
>
>
>Review the results of:
>
>SHOW GRANTS FOR 'securer_devUser'@'localhost';
>
>>From the manual (http://dev.mysql.com/doc/mysql/en/select.html):
>>>>>>>>>>>>>>>>>>>>
> 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
currently
>exist, which among other things prevents files such as /etc/passwd and
>database tables from being destroyed.
><<<<<<<<<<<<<<<<
>
>The FILE privilege they discuss is a MYSQL permission (a GRANT) not an
OS
>permission. The OS rights to write the file to disk must exist for the
>account that the MySQL server processes is running under, not the mysql
>user account being used to execute the command. Make sense?
>
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Select INTO OUTFILE

--=_alternative 00790D8E85256FBD_=
Content-Type: text/plain; charset="US-ASCII"

There are several reasons why this could be failing:
a) A misspelled user name. Try: 'securer_devUser'@'localhost'.
(This is the most likely cause)
b) YOUR user id does not have GRANT privileges.
c) The user already exists and you are being prevented from
changing their password. Try ending the command BEFORE you say "IDENTIFIED
...."
d) there is no table xxxxxx. Check your spelling.

As an example, if this user securer_devUser@localhost needs FILE
permission on every table in the Umpityfratz database you would write:

GRANT FILE ON Umpityfratz.* TO 'securer_devUser'@'localhost';

HTH,

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Eric Gewirtz" wrote on 03/07/2005 04:54:17 PM:

> Thanks Shawn, this was helpful. I'm now having an issuing granting the
> "FILE" privilege to the user.
>
> GRANT FILE ON xxxxxxxx TO 'securer_devUser@localhost' IDENTIFIED BY
> 'xxxxxxxxxx';
> ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual
> which privileges can be used.
>
> Why would I be prevented from issuing the grant, assuming I have the
> privilege to issue it?
>
> Eric
>
> Eric Gewirtz
> SolutionOne
> Phone - 845-729-7800
> Fax - 845-279-5502
> egewirtz@rcn.com
>
>
> >-----Original Message-----
> >From: SGreen@unimin.com [mailto:SGreen@unimin.com]
> >Sent: Monday, March 07, 2005 3:17 PM
> >To: Eric Gewirtz
> >Cc: win32@lists.mysql.com
> >Subject: Re: Select INTO OUTFILE
> >
> >"Eric Gewirtz" wrote on 03/06/2005 11:11:25 PM:
> >
> >> Hi,
> >>
> >>
> >>
> >> I want to use Select INTO OUTFILE to create a simple delimited file
> >>
> >> I am running it locally on the same server that the database resides
> on
> >>
> >> We are running MySQL version 4.0.22
> >>
> >> I have tried running directly in MySQL and also from the command line
> by
> >> redirecting the source sql
> >>
> >>
> >>
> >> Here is the Select
> >>
> >> SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'
> >>
> >> FIELDS TERMINATED BY '|'
> >>
> >> LINES TERMINATED BY '\r\n'
> >>
> >> FROM TB_FCT_Address;
> >>
> >>
> >>
> >> From the command line I do the following and get the following error
> >> message - I know the login info is good because if I redirect in a
> >> simple program that does a basic select it works fine
> >>
> >> mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
> >> sqlOutFileAddress.sql
> >>
> >> ERROR 1045 at line 1: Access denied for user:
> >> 'securer_devUser@localhost' (Using password: NO)
> >>
> >>
> >>
> >> If I copy the select directly into a mySQL session I get a similar
> error
> >> message
> >>
> >> ERROR 1045: Access denied for user: 'securer_devUser@localhost'
> (Using
> >> password: YES)
> >>
> >>
> >>
> >> The user that I have logged in as has write permissions in the
> directory
> >> specified in the Select INTO OUTFILE
> >>
> >>
> >>
> >> Any help would be appreciated
> >>
> >>
> >>
> >> Thanks
> >>
> >>
> >>
> >> Eric Gewirtz
> >>
> >> SolutionOne
> >>
> >> Phone - 845-729-7800
> >>
> >> Fax - 845-279-5502
> >>
> >> egewirtz@rcn.com
> >>
> >
> >
> >Review the results of:
> >
> >SHOW GRANTS FOR 'securer_devUser'@'localhost';
> >
> >>From the manual (http://dev.mysql.com/doc/mysql/en/select.html):
> >>>>>>>>>>>>>>>>>>>>
> > 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
> currently
> >exist, which among other things prevents files such as /etc/passwd and
> >database tables from being destroyed.
> ><<<<<<<<<<<<<<<<
> >
> >The FILE privilege they discuss is a MYSQL permission (a GRANT) not an
> OS
> >permission. The OS rights to write the file to disk must exist for the
> >account that the MySQL server processes is running under, not the mysql
> >user account being used to execute the command. Make sense?
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
>
>

--=_alternative 00790D8E85256FBD_=--

RE: Select INTO OUTFILE

Eric,

I believe that the FILE priv is global only. This is probably what is
causing your error. Try:

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

Obviously even though this is global, the ability to do SELECT INTO
OUTFILE is still restricted based on select privs of specific db's.

--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

On Monday, March 07, 2005 15:59, SGreen@unimin.com wrote:

> There are several reasons why this could be failing:
> a) A misspelled user name. Try: 'securer_devUser'@'localhost'.
> (This is the most likely cause)
> b) YOUR user id does not have GRANT privileges.
> c) The user already exists and you are being prevented from
> changing their password. Try ending the command BEFORE you say
> "IDENTIFIED ..."
> d) there is no table xxxxxx. Check your spelling.
>
> As an example, if this user securer_devUser@localhost needs FILE
> permission on every table in the Umpityfratz database you would write:
>
> GRANT FILE ON Umpityfratz.* TO 'securer_devUser'@'localhost';
>
> HTH,
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "Eric Gewirtz" wrote on 03/07/2005 04:54:17 PM:
>
>> Thanks Shawn, this was helpful. I'm now having an issuing granting
>> the "FILE" privilege to the user.
>>
>> GRANT FILE ON xxxxxxxx TO 'securer_devUser@localhost' IDENTIFIED BY
>> 'xxxxxxxxxx'; ERROR 1144: Illegal GRANT/REVOKE command. Please
>> consult the manual which privileges can be used.
>>
>> Why would I be prevented from issuing the grant, assuming I have the
>> privilege to issue it?
>>
>> Eric
>>
>> Eric Gewirtz
>> SolutionOne
>> Phone - 845-729-7800
>> Fax - 845-279-5502
>> egewirtz@rcn.com
>>
>>
>>> -----Original Message-----
>>> From: SGreen@unimin.com [mailto:SGreen@unimin.com]
>>> Sent: Monday, March 07, 2005 3:17 PM
>>> To: Eric Gewirtz
>>> Cc: win32@lists.mysql.com
>>> Subject: Re: Select INTO OUTFILE
>>>
>>> "Eric Gewirtz" wrote on 03/06/2005 11:11:25
>>> PM:
>>>
>>>> Hi,
>>>>
>>>>
>>>>
>>>> I want to use Select INTO OUTFILE to create a simple delimited file
>>>>
>>>> I am running it locally on the same server that the database
>>>> resides on
>>>>
>>>> We are running MySQL version 4.0.22
>>>>
>>>> I have tried running directly in MySQL and also from the command
>>>> line by redirecting the source sql
>>>>
>>>>
>>>>
>>>> Here is the Select
>>>>
>>>> SELECT * INTO OUTFILE '/home/securer/MySQLAddress.txt'
>>>>
>>>> FIELDS TERMINATED BY '|'
>>>>
>>>> LINES TERMINATED BY '\r\n'
>>>>
>>>> FROM TB_FCT_Address;
>>>>
>>>>
>>>>
>>>> From the command line I do the following and get the following
>>>> error message - I know the login info is good because if I
>>>> redirect in a simple program that does a basic select it works fine
>>>>
>>>> mysql --host localhost --user xxxxxx --password=xxxxxxx dbName <
>>>> sqlOutFileAddress.sql
>>>>
>>>> ERROR 1045 at line 1: Access denied for user:
>>>> 'securer_devUser@localhost' (Using password: NO)
>>>>
>>>>
>>>>
>>>> If I copy the select directly into a mySQL session I get a similar
>>>> error message
>>>>
>>>> ERROR 1045: Access denied for user: 'securer_devUser@localhost'
>>>> (Using password: YES)
>>>>
>>>>
>>>>
>>>> The user that I have logged in as has write permissions in the
>>>> directory specified in the Select INTO OUTFILE
>>>>
>>>>
>>>>
>>>> Any help would be appreciated
>>>>
>>>>
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>> Eric Gewirtz
>>>>
>>>> SolutionOne
>>>>
>>>> Phone - 845-729-7800
>>>>
>>>> Fax - 845-279-5502
>>>>
>>>> egewirtz@rcn.com
>>>>
>>>
>>>
>>> Review the results of:
>>>
>>> SHOW GRANTS FOR 'securer_devUser'@'localhost';
>>>
>>>> From the manual (http://dev.mysql.com/doc/mysql/en/select.html):
>>>>>>>>>>>>>>>>>>>>>>
>>> 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 currently exist, which among other things prevents files
>>> such as /etc/passwd and database tables from being destroyed.
>>> <<<<<<<<<<<<<<<<
>>>
>>> The FILE privilege they discuss is a MYSQL permission (a GRANT) not
>>> an OS permission. The OS rights to write the file to disk must
>>> exist for the account that the MySQL server processes is running
>>> under, not the mysql user account being used to execute the
>>> command. Make sense?
>>>
>>> Shawn Green
>>> Database Administrator
>>> Unimin Corporation - Spruce Pine

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org