MYSQL - Unable to change 'old_passwords' variable

Question

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?

Answer 1

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

Answer 2

I forgot to return earlier but none of the answers provided worked and after a few days of playing around I eventually bit the bullet and upgraded the MySQL server to the latest version.

So in the end I never found a workable solution but the problem is gone.

Answer 3

If you have set old_passwords=1 lower down in your my.cnf file, the last setting takes priority. So check the full length of your my.cnf for another line that sets this variable.

Answer 4

In my case, there was an error in log:

190122 4:39:09 [Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

The mysql_fix_privilege_tables was replaced with mysql_upgrade in MySQL 5.1.
For my Mariadb 5.5 I ran to fix it:

mysql_upgrade --force

And restarted the service.