Page 1 of 1
newbie: can't connect to MySQL database via PHP
Posted: Fri Oct 06, 2006 6:06 am
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!)
Posted: Fri Oct 06, 2006 6:38 am
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.
Posted: Fri Oct 06, 2006 6:48 am
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)
Posted: Fri Oct 06, 2006 7:03 am
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.
Posted: Fri Oct 06, 2006 7:22 am
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?
Posted: Fri Oct 06, 2006 7:28 am
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.
Posted: Fri Oct 06, 2006 7:37 am
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?
Posted: Fri Oct 06, 2006 7:48 am
by Flamie
dont hesitate.
PHP5 > PHP4

Posted: Fri Oct 06, 2006 7:51 am
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.
Posted: Fri Oct 06, 2006 8:00 am
by LiquidEyes
Wicked, I'll do it then.
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!)
Posted: Fri Oct 06, 2006 8:17 am
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).
Posted: Fri Oct 06, 2006 8:50 am
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

-- 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'.)
