can no longer access MySQL via PHP

Question

I have a mySQL / PHP setup that I've used on several systems. I just recently upgraded my system to Max OSX 10.6, and now it cannot connect. I get the error:

mysqlnd cannot connect to MySQL 4.1+ using old authentication

I've done the obvious google searches, but most of them seem to be all about how to get mysql to use the old password style, which seems like it will no longer work. In any case, I want to find a better solution that allows me to use the newer password style.

I create my password with:

mysql> UPDATE mysql.user SET Password=PASSWORD('mypassword') WHERE User='root';

(or OLD_PASSWORD, it doesn't seem to matter)

and I log into the database with:

@mysql_connect($mysqlserver,$username,$password)

where $username is root and $password="mypassword"

What is the CORRECT solution to this problem, as forcing it keep using the old password technology is clearly NOT the right solution...

Answer 1

D'oh, I'm an idiot. when I manually re-set the root password using PASSWORD("password") I didn't re-set the USERS password the same way, so the users password was still OLD_PASSWORD("password"), and our PHP is logging in as user, not as root (which I suppose is a good thing.)

I manually re-set the users password with PASWORD("password") and everything seems to work...

Answer 2

When you reset the password are you running mysql in safe mode? I know sometimes mysql is very finnicky about resetting root's password. Try this post if you haven't already... how to forge

Answer 3

Try enabling old_passwords = true in my.cnf. Should help ;)

Answer 4

After manually playing with theses tables make sure to execute FLUSH PRIVILEGES so MySQL reads the new values.

In general it is better to use SET PASSWORD to change the password as this verifies the table values are valid. With the manual way you easily get invalid data into the user tables.