0

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

techraf
  • 4,243
  • 8
  • 29
  • 44
Brian Postow
  • 192
  • 1
  • 10

4 Answers4

2

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

Brian Postow
  • 192
  • 1
  • 10
  • I find it somewhat disconcerting that an answer starting with "D'oh I'm an idiot" has gotten two upvotes... and you guys don't even know me! hehe. – Brian Postow Nov 04 '09 at 16:03
0

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

lilott8
  • 496
  • 5
  • 14
  • Yes, I am running mysql in safe mode is that a problem?when I re-set the root password, it DOES change it. I think that the problem is with the PHP mysql_connect call maybe? – Brian Postow Nov 03 '09 at 21:50
  • No the mysql_connect is the correct syntax and you have supplied the correct parameters. Though you might try just: mysql_connect('localhost', 'user', 'password'); so in effect you, instead of giving the variables you are giving the string literals. Just as a test. – lilott8 Nov 03 '09 at 22:19
0

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

kolypto
  • 11,058
  • 12
  • 54
  • 66
0

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.

johannes
  • 583
  • 2
  • 10