Re: [PHP] It's Friday (a MySQL Question) [Solution]

[tedd]

At 5:48 PM -0400 8/13/10, tedd wrote:
Normally if I want to dump a MySQL database, I read the database via 
a PHP script (i.e., list tables and fetch rows) and save the results 
as a text file -- after which I download the file -- it's not a big 
deal.


However while I was doing my daily read of the MySQL Manual, namely:

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

I came across this statement:

SELECT * FROM table_reference INTO OUTFILE 'file_name'

It looked to be bit simpler/shorter than my code, so I tried it. But 
it reports:


Access denied for user 'me'@'localhost' (using password: YES).

I suspect that the "access being denied" is because MySQL doesn't 
have permission to create the output file. The MySQL manual reports: 
1) that a file cannot be present; 2) AND MySQL must have file 
privileges to create the file -- but I don't know how to set that up.


So, has anyone got this to work? If so, how did you do it? If at all 
possible, please provide code (MySQL/PHP) and not command-line 
statements.


The problem here was that I am on a hosted account and do not have 
"Server Access" to use the "SELECT ... INTO OUTFILE" syntax.


I suspected such, but it took several exchanges on the mysql list to 
prove the issue.


In short, unless you have "Server Access" , then you cannot use the 
"SELECT ... INTO OUTFILE" syntax in php.


Cheers,

tedd

--
---
http://sperling.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[chris h]

Well you certainly can not use this without the command line, however some
hosts restrict you from the command line but still allow you to run commands
via php's exec() function (Rackspace Sites is an example of this).

pseudo code example:
exec('mysqldump [options] --all-databases ... ');

Of course this is only if your hoster supports it, and if the user has
proper mysql privileges (which I think are SELECT, LOCK TABLES, and SHOW
VIEW).

mysqldump is a great program with many options so you can dump your db just
the way you want it ;)  If it's something you can and choose to use I would
suggest reading over the link I sent you so you can customize it's output.


Chris.

On Sat, Aug 14, 2010 at 8:51 AM, tedd  wrote:

> At 6:53 PM -0400 8/13/10, chris h wrote:
>
>> Tedd I don't know if this will resolve your issue or not, but have you
>> looked into using mysqldump?
>>
>> 
>> http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
>>
>>
>> That's what I use for my backups.
>>
>>
>> Chris.
>>
>
> Chris:
>
> I would like to do that, but I simply don't know how.
>
> I think the reason for that is that I usually work on shared hosted
> accounts and the hosts don't permit command line stuff -- so I think, but I
> could be wrong. Plus, I stopped doing command line stuff back in the Apple
> ][ days.
>
> Granted this is another hole in my knowledge, but I think that even if I
> knew how, I don't think it would solve my current problem. Am I wrong?
>
>
> Cheers,
>
> tedd
>
> --
> ---
> http://sperling.com/
>

Re: [PHP] It's Friday (a MySQL Question)

[tedd]

At 6:53 PM -0400 8/13/10, chris h wrote:
Tedd I don't know if this will resolve your issue or not, but have 
you looked into using mysqldump?


http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

That's what I use for my backups.


Chris.


Chris:

I would like to do that, but I simply don't know how.

I think the reason for that is that I usually work on shared hosted 
accounts and the hosts don't permit command line stuff -- so I think, 
but I could be wrong. Plus, I stopped doing command line stuff back 
in the Apple ][ days.


Granted this is another hole in my knowledge, but I think that even 
if I knew how, I don't think it would solve my current problem. Am I 
wrong?


Cheers,

tedd

--
---
http://sperling.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[Simon J Welsh]

On 14/08/2010, at 9:59 AM, Ashley Sheridan wrote:

> I've only ever done something like this via the command line. Having
> said that, could you maybe pass a command line string to exec().
> Something like (untested):
> 
> echo 'password' | mysql -u root -p < query
> 
> I believe that is the right sort of thing, but I've never quite done it
> all as a single statement like this before, I've always tended to type
> in things on a line-by-line basis.
> 
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk

That wont work, as pipping just passes stdout to stdin, which mysql grabs 
queries to run from (which you're using file redirection for).

I know of someone that wanted to do something similar, but didn't want to use a 
plaintext password in the command (shared host security and that sort of 
stuff), so they got PHP to generate a defaults file with the user/password and 
passed that instead (--defaults-extra-file)

---
Simon Welsh
Admin of http://simon.geek.nz/

Who said Microsoft never created a bug-free program? The blue screen never, 
ever crashes!

http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e





--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[chris h]

Tedd I don't know if this will resolve your issue or not, but have you
looked into using mysqldump?

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

That's what I use for my backups.


Chris.

On Fri, Aug 13, 2010 at 6:47 PM, tedd  wrote:

> At 6:11 PM -0400 8/13/10, Daniel P. Brown wrote:
>
>> On Fri, Aug 13, 2010 at 17:48, tedd  wrote:
>>
>>>
>>>SELECT * FROM table_reference INTO OUTFILE 'file_name'
>>>
>>>  It looked to be bit simpler/shorter than my code, so I tried it. But it
>>>  reports:
>>>
>>>Access denied for user 'me'@'localhost' (using password: YES).
>>>
>>>  I suspect that the "access being denied" is because MySQL doesn't have
>>>  permission to create the output file. The MySQL manual reports: 1) that
>>> a
>>>  file cannot be present; 2) AND MySQL must have file privileges to create
>>> the
>>>  file -- but I don't know how to set that up.
>>>
>>
>>No, the 'access denied' message means that either the username or
>> password is incorrect, or that the given user doesn't have permission
>> to access the given database on the given host.
>>
>
> Daniel :
>
> I don't think so and here's my reasoning.
>
> You see in the same script, I tested the connection to the database with a
> query *before* the OUTFILE statement and it worked! If the username and/or
> password had been wrong, then it would have also failed. Here's the demo:
>
> http://php1.net/c/db-dump/db-2-dir.php
>
> Please note that the string 'BKohl41' noted is the result of a query to the
> same database *before* the OUTFILE query.
>
> As far as I can tell, the MySQL statement does not have permission to
> create a file and that's the problem as I see it.
>
> I shall test Daevid's "GRANT SELECT, FILE ON mydb.table_reference TO 
> 'me'@'localhost';"
> statement tomorrow -- it looks promising.
>
> As for the:
>
>
> "You know you can pass that on the command line right and avoid this pipe
> business?"
>
> No, I don't know how to do that.
>
>
> Cheers,
>
> tedd
>
> --
> ---
> http://sperling.com/
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP] It's Friday (a MySQL Question)

[tedd]

At 6:11 PM -0400 8/13/10, Daniel P. Brown wrote:

On Fri, Aug 13, 2010 at 17:48, tedd  wrote:


SELECT * FROM table_reference INTO OUTFILE 'file_name'

 It looked to be bit simpler/shorter than my code, so I tried it. But it
 reports:

Access denied for user 'me'@'localhost' (using password: YES).

 I suspect that the "access being denied" is because MySQL doesn't have
 permission to create the output file. The MySQL manual reports: 1) that a
 file cannot be present; 2) AND MySQL must have file privileges to create the
 file -- but I don't know how to set that up.


No, the 'access denied' message means that either the username or
password is incorrect, or that the given user doesn't have permission
to access the given database on the given host.


Daniel :

I don't think so and here's my reasoning.

You see in the same script, I tested the connection to the database 
with a query *before* the OUTFILE statement and it worked! If the 
username and/or password had been wrong, then it would have also 
failed. Here's the demo:


http://php1.net/c/db-dump/db-2-dir.php

Please note that the string 'BKohl41' noted is the result of a query 
to the same database *before* the OUTFILE query.


As far as I can tell, the MySQL statement does not have permission to 
create a file and that's the problem as I see it.


I shall test Daevid's "GRANT SELECT, FILE ON mydb.table_reference TO 
'me'@'localhost';" statement tomorrow -- it looks promising.


As for the:

"You know you can pass that on the command line right and avoid this pipe
business?"

No, I don't know how to do that.

Cheers,

tedd

--
---
http://sperling.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] It's Friday (a MySQL Question)

[Daevid Vincent]

> -Original Message-
> From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] 
> Sent: Friday, August 13, 2010 3:23 PM
> To: Daniel P. Brown
> Cc: tedd; php-general@lists.php.net
> Subject: Re: [PHP] It's Friday (a MySQL Question)
> 
> On Fri, 2010-08-13 at 18:22 -0400, Daniel P. Brown wrote:
> 
> > On Fri, Aug 13, 2010 at 18:17, Ashley Sheridan 
>  wrote:
> > >
> > > To both David and Daniel, thank you! How on earth I ever 
> missed that argument before is a wonder known only to the 
> great electronic deity in the sky!
> > 
> > Hey, Daevid: you may have been thanked first, but at 
> least my name
> > was spelled correctly.
> > 
> 
> Sorry Daevid, blame it on the wine I've been drinking! It is a Friday
> after all!

For the amount of crap I stir up on this list, 
having my name misspelled is a slight penance. ;-p


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[Ashley Sheridan]

On Fri, 2010-08-13 at 18:22 -0400, Daniel P. Brown wrote:

> On Fri, Aug 13, 2010 at 18:17, Ashley Sheridan  
> wrote:
> >
> > To both David and Daniel, thank you! How on earth I ever missed that 
> > argument before is a wonder known only to the great electronic deity in the 
> > sky!
> 
> Hey, Daevid: you may have been thanked first, but at least my name
> was spelled correctly.
> 
> --
> 
> UNADVERTISED DEDICATED SERVER SPECIALS
> SAME-DAY SETUP
> Just ask me what we're offering today!
> daniel.br...@parasane.net || danbr...@php.net
> http://www.parasane.net/ || http://www.pilotpig.net/
> 


Sorry Daevid, blame it on the wine I've been drinking! It is a Friday
after all!

Thanks,
Ash
http://www.ashleysheridan.co.uk

Re: [PHP] It's Friday (a MySQL Question)

[Daniel P. Brown]

On Fri, Aug 13, 2010 at 18:17, Ashley Sheridan  
wrote:
>
> To both David and Daniel, thank you! How on earth I ever missed that argument 
> before is a wonder known only to the great electronic deity in the sky!

Hey, Daevid: you may have been thanked first, but at least my name
was spelled correctly.

--

UNADVERTISED DEDICATED SERVER SPECIALS
SAME-DAY SETUP
Just ask me what we're offering today!
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[Ashley Sheridan]

On Fri, 2010-08-13 at 18:14 -0400, Daniel P. Brown wrote:

> On Fri, Aug 13, 2010 at 17:59, Ashley Sheridan  
> wrote:
> >
> > echo 'password' | mysql -u root -p < query
> 
> If you're going to do the password in plain text from the command
> line like that (which is a bad idea), you don't need to pipe an echo.
> Just  type:
> 
> mysql -u root -ppassword < query
> 
> As long as there's no space between the -p flag and your password,
> you're good to go.  Still, not only is it a horrible idea, it's far
> worse if it's your MySQL root.  Far, far, FAR worse if it's the same
> as your system root.  You belong in jail --- no, *hell* --- if the
> MySQL and system root passwords are the same, and you have remote root
> login enabled with password authentication.
> 


To both David and Daniel, thank you! How on earth I ever missed that
argument before is a wonder known only to the great electronic deity in
the sky! 

Thanks,
Ash
http://www.ashleysheridan.co.uk

Re: [PHP] It's Friday (a MySQL Question)

[Daniel P. Brown]

On Fri, Aug 13, 2010 at 17:59, Ashley Sheridan  
wrote:
>
> echo 'password' | mysql -u root -p < query

If you're going to do the password in plain text from the command
line like that (which is a bad idea), you don't need to pipe an echo.
Just  type:

mysql -u root -ppassword < query

As long as there's no space between the -p flag and your password,
you're good to go.  Still, not only is it a horrible idea, it's far
worse if it's your MySQL root.  Far, far, FAR worse if it's the same
as your system root.  You belong in jail --- no, *hell* --- if the
MySQL and system root passwords are the same, and you have remote root
login enabled with password authentication.

-- 

UNADVERTISED DEDICATED SERVER SPECIALS
SAME-DAY SETUP
Just ask me what we're offering today!
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] It's Friday (a MySQL Question)

[Daevid Vincent]

 

> -Original Message-
> From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] 
> Sent: Friday, August 13, 2010 3:00 PM
> To: tedd
> Cc: php-general@lists.php.net
> Subject: Re: [PHP] It's Friday (a MySQL Question)
> 
> On Fri, 2010-08-13 at 17:48 -0400, tedd wrote:
> 
> > Hi gang:
> > 
> > Normally if I want to dump a MySQL database, I read the 
> database via 
> > a PHP script (i.e., list tables and fetch rows) and save 
> the results 
> > as a text file -- after which I download the file -- it's not a big 
> > deal.
> > 
> > However while I was doing my daily read of the MySQL Manual, namely:
> > 
> > http://dev.mysql.com/doc/refman/5.0/en/select.html
> > 
> > I came across this statement:
> > 
> >  SELECT * FROM table_reference INTO OUTFILE 'file_name'
> > 
> > It looked to be bit simpler/shorter than my code, so I 
> tried it. But 
> > it reports:
> > 
> >  Access denied for user 'me'@'localhost' (using password: YES).
> > 
> > I suspect that the "access being denied" is because MySQL doesn't 
> > have permission to create the output file. The MySQL manual 
> reports: 
> > 1) that a file cannot be present; 2) AND MySQL must have file 
> > privileges to create the file -- but I don't know how to 
> set that up.

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

http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html#priv_file

GRANT SELECT, FILE ON mydb.table_reference TO 'me'@'localhost';

> I've only ever done something like this via the command line. Having
> said that, could you maybe pass a command line string to exec().
> Something like (untested):
> 
> echo 'password' | mysql -u root -p < query

You know you can pass that on the command line right and avoid this pipe
business?

mysql -uroot -ppassword < query

Or

mysql --user=root --password=password < query

> I believe that is the right sort of thing, but I've never 
> quite done it
> all as a single statement like this before, I've always tended to type
> in things on a line-by-line basis.


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[Daniel P. Brown]

On Fri, Aug 13, 2010 at 17:48, tedd  wrote:
>
>    SELECT * FROM table_reference INTO OUTFILE 'file_name'
>
> It looked to be bit simpler/shorter than my code, so I tried it. But it
> reports:
>
>    Access denied for user 'me'@'localhost' (using password: YES).
>
> I suspect that the "access being denied" is because MySQL doesn't have
> permission to create the output file. The MySQL manual reports: 1) that a
> file cannot be present; 2) AND MySQL must have file privileges to create the
> file -- but I don't know how to set that up.

No, the 'access denied' message means that either the username or
password is incorrect, or that the given user doesn't have permission
to access the given database on the given host.

Easiest method, from the command line on the server from which you
want to dump the database:

mysqldump -u user -p database_name > outfile.sql

(Where `user` is the username, `database_name` is the database to
dump, and `outfile.sql` is the name of the file to which you will
write.  You will be prompted for the password.)

-- 

UNADVERTISED DEDICATED SERVER SPECIALS
SAME-DAY SETUP
Just ask me what we're offering today!
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] It's Friday (a MySQL Question)

[Ashley Sheridan]

On Fri, 2010-08-13 at 17:48 -0400, tedd wrote:

> Hi gang:
> 
> Normally if I want to dump a MySQL database, I read the database via 
> a PHP script (i.e., list tables and fetch rows) and save the results 
> as a text file -- after which I download the file -- it's not a big 
> deal.
> 
> However while I was doing my daily read of the MySQL Manual, namely:
> 
> http://dev.mysql.com/doc/refman/5.0/en/select.html
> 
> I came across this statement:
> 
>  SELECT * FROM table_reference INTO OUTFILE 'file_name'
> 
> It looked to be bit simpler/shorter than my code, so I tried it. But 
> it reports:
> 
>  Access denied for user 'me'@'localhost' (using password: YES).
> 
> I suspect that the "access being denied" is because MySQL doesn't 
> have permission to create the output file. The MySQL manual reports: 
> 1) that a file cannot be present; 2) AND MySQL must have file 
> privileges to create the file -- but I don't know how to set that up.
> 
> So, has anyone got this to work? If so, how did you do it? If at all 
> possible, please provide code (MySQL/PHP) and not command-line 
> statements.
> 
> Cheers,
> 
> tedd
> 
> -- 
> ---
> http://sperling.com/
> 


I've only ever done something like this via the command line. Having
said that, could you maybe pass a command line string to exec().
Something like (untested):

echo 'password' | mysql -u root -p < query

I believe that is the right sort of thing, but I've never quite done it
all as a single statement like this before, I've always tended to type
in things on a line-by-line basis.

Thanks,
Ash
http://www.ashleysheridan.co.uk

[PHP] It's Friday (a MySQL Question)

[tedd]

Hi gang:

Normally if I want to dump a MySQL database, I read the database via 
a PHP script (i.e., list tables and fetch rows) and save the results 
as a text file -- after which I download the file -- it's not a big 
deal.


However while I was doing my daily read of the MySQL Manual, namely:

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

I came across this statement:

SELECT * FROM table_reference INTO OUTFILE 'file_name'

It looked to be bit simpler/shorter than my code, so I tried it. But 
it reports:


Access denied for user 'me'@'localhost' (using password: YES).

I suspect that the "access being denied" is because MySQL doesn't 
have permission to create the output file. The MySQL manual reports: 
1) that a file cannot be present; 2) AND MySQL must have file 
privileges to create the file -- but I don't know how to set that up.


So, has anyone got this to work? If so, how did you do it? If at all 
possible, please provide code (MySQL/PHP) and not command-line 
statements.


Cheers,

tedd

--
---
http://sperling.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php