newbie: can't connect to MySQL database via PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

newbie: can't connect to MySQL database via PHP

Post by LiquidEyes »

I apologise in advance if this is something really really dumb! :)

The following code fails for some reason: (lifted almost verbatim from a text book)

Code: Select all

$myconnection= @mysql_connect('localhost', 'testuser', 'mypassword');
if (!$myconnection) {
  exit('<p>Unable to connect to the ' .
      'database server at this time.</p>');
}
yet I have no problems connecting to my database via the command-line interface:

Code: Select all

C:\Program Files\MySQL Server 4.1\bin> mysql -h localhost -u testuser -p
Enter password: **********
Welcome to the MySQL monitor.
...
I've definitely got the password right, and my PHP scripts are working fine until I try to connect to MySQL. I've also tried connecting as root, with my root password, to make sure it's not a problem with database permissions. Disabling my firewall made no difference. Finally I've tried explictly specifying the port ('localhost:3306') as indicated in MySQL's my.ini file.

Any ideas what I might be doing wrong?
(Thanks in advance!)
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Remove that @ sign in front of mysql_connect, and paste the error here. If you set up PHP/MySQL yourself, you may have some configuration problems.
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

Post by LiquidEyes »

Aha! I think you're right...

Code: Select all

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client
So what 'authentication protocol' do I need?
And is that error ultimately coming from MySQL or PHP?
I.e. which is the 'client' and which is the 'server'? (from my perspective, PHP and MySQL are *both* server-side but I guess PHP is MySQL's client)
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

If you recently upgraded to MySQL version >= 4.1, have a look at http://www.digitalpeer.com/id/mysql. Otherwise, look through some of these.
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

Post by LiquidEyes »

Ah, great. :)

It says:
Because upgrading the client can sometimes be a pain, it's often easier to just update the passwords to the old format on the server.
Why is it a "pain" to upgrade the client?
Presumably I just need a newer version of PHP that supports the improved password protocol?
Or is it Apache itself I need to upgrade?
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

It's actually the mysql extension in PHP that needs the upgrading. From http://dev.mysql.com/doc/refman/5.0/en/old-client.html:
Note: In older versions of PHP, the mysql extension does not support the authentication protocol in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library. For more information about the mysqli extension, see http://php.net/mysqli.
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

Post by LiquidEyes »

So in a nutshell, the easiest thing would be simply to install PHP5?

Are there any gotchas I need to know about? (I'm trying to figure out why I didn't install PHP5 in the first place!) :?

Is PHP5 less commonplace/stable/etc? Or should I not hesitate to upgrade?
Flamie
Forum Contributor
Posts: 166
Joined: Mon Mar 01, 2004 3:19 pm

Post by Flamie »

dont hesitate.
PHP5 > PHP4 :)
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

Well, the easiest thing would be to do that password flush thing at http://www.digitalpeer.com/id/mysql, but there are a lot of cool things that come with migrating to PHP5 -- the manual can explain it better than I can. If you're interested at all in OOP functionality, definately upgrade. I've been a very happy programmer since I did it.
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

Post by LiquidEyes »

Wicked, I'll do it then. :D

Yeah I am aware of the language improvements (OOP etc). I just wanted to make sure there was no downside to upgrading. E.g. I've read various stuff about Apache 1.3 being more stable than Apache 2, so I wondered if there was something similar with PHP. Also I've found a list of backwards incompatibilities so basically I'm relying on the expert opinion of others as to whether these are serious, and how 'industry standard' PHP5 is (given that I'm learning from scratch, and I don't want to pick up any bad or non-standard habits).

I'm puzzled that I never installed PHP5 to begin with, so I can only think that I read something, somewhere, that convinced me to go with PHP4...? (I wish I could remember!)
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

I think the backwards incompatibilities are mostly just function deprecations/argument changes. Other than that, nothing major that I've come across yet. PHP4 is still widely used (e.g., you'll usually have to make a special request to most web hosting companies for a PHP5 server), but I think that's mostly because developers feel no pressing need to switch (even though there aren't many reasons not to upgrade, and a lot of reasons to upgrade).
LiquidEyes
Forum Newbie
Posts: 12
Joined: Fri Oct 06, 2006 6:02 am

Post by LiquidEyes »

Cool -- thanks for the advice Aaron.

I tried the 'old password' bodge but the instructions here didn't work for me. However I have managed to get it working now :D -- here's exactly what I did:

Code: Select all

UPDATE mysql.user
SET password=OLD_PASSWORD('mypassword')
WHERE user='testuser';
I don't seem to have a 'host' column in my user table -- is that unusual?
Does this mean that specifying 'localhost' (or whatever) is redundant when I log in to MySQL?

(If I enter any old made-up hostname, it fails to log in, as you might expect; however it doesn't seem to care which of my valid hostnames I enter. I have Apache configured to 'answer' to a few hostnames, e.g. localhost, phpexample123, bugzilladb, etc. But I'm a bit confused as to the role of the hostname when logging into MySQL, which doesn't seem to be tied to any one 'alias'.) :?
Post Reply