MYSQL – Unable to change ‘old_passwords’ variable

MySQLpassword

I have full access to a 'MYSQL 5.0.27-community-nt-log' database one of our new updates for a core app requires PHP 5.4, after installing and configuring I'm stuck at the error regarding old vs. new passwords.

I've followed every thread I can find and I'm pretty sure I understand the process:

  1. Disable 'old_passwords'
  2. Reset the password to the new 41 encryption

But I cannot seem to get 'old_passwords' to be set to 'OFF'. Logging in as root I've tried the following:

  • I've removed it from the my.cnf file (I've also tried setting it to '0', 'False' etc)
  • Manually setting it in the query browser both globally and session
  • Turning the option on/off in MYSQL administrator

and restarted the service but 'show variables like 'old_passwords' always reports as 'ON'.

Changing the password still remains at a 16 length.

I'm sure once I'm able to turn this variable off I should be fine, Any advice?

Best Answer

I have 3 additional suggestions (choose one):

  • Downgrade the PHP drivers (which understand the 16 character encrypted password)
  • Upgrade to the latest MySQL (where you can set old_password=1 from inception)
  • Change all the passwords in mysql.user to 41 character encrypted password format

Whatever you do, do not use the MD5 function to make new passwords. Use the PASSWORD function. It is very different from MD5:

The password function PASSWORD function is the equivalent of

SET @my_new_password = 'WhateverIWantAsThePassword';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_password))))) MySQLPWDHASH;

I learned that like 2 years ago from this PalominoDB Blog Post.

You can QA the PASSWORD function with OLD_PASSWORD function. Compare the output of SELECT PASSWORD(@my_new_password); WITH SELECT OLD_PASSWORD(@my_new_password);.

Related Question