Upgrading passwords from old_passwords to "new passwords"

February 28, 2011

You have old_passwords=1 in your my.cnf. I'm guessing this is because you used one of the my-small.cnf, my-large.cnf etc. templates provided with your MySQL distribution.

These files can easily win the "most outdated sample configuration file contest".

Usually it's no big deal: if some parameter isn't right, you just go and change it. Some variables, though, have a long-lasting effect, and are not easily reversed.

What's the deal with old_passwords?

No one should be using these anymore. This variable makes the password hashing algorithm compatible with that of MySQL 4.0. I'm pretty sure 4.0 was released 9 years ago. I don't know of anyone still using it (or 4.0 client libraries).

The deal is this: with old_passwords you get a 16 hexadecimal digits (64 bit) hashing of your passwords. With so called "new passwords" you get 40 hexadecimal digits (plus extra "*"). So this is about better encryption of your password. Read more on the manual.

How do I upgrade to new password format?

You can't just put a comment on the "old_passwords=1" entry in the configuration file. If you do so, the next client to connect will attempt to match a 41 characters hashed password to your existing 16 characters entry in the mysql.users table. So you need to make a simultaneous change: both remove the old_passwords entry and set a new password. You must know all accounts' passwords before you begin.

Interestingly, old_passwords is both a global and a session variable. To work out an example, let's assume the account 'webuser'@'localhost' enters with '123456'. Take a look at the following:

root@mysql-5.1.51> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SELECT PASSWORD('123456');
| PASSWORD('123456')                        |
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
1 row in set (0.00 sec)

root@mysql-5.1.51> SET SESSION old_passwords=1;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SELECT PASSWORD('123456');
| PASSWORD('123456') |
| 565491d704013245   |
1 row in set (0.00 sec

So, the PASSWORD() function consults the old_passwords session variable.

To upgrade 'webuser'@'localhost''s password we do:

root@mysql-5.1.51> SET SESSION old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

root@mysql-5.1.51> SET PASSWORD FOR 'webuser'@'localhost' = PASSWORD('123456')

Go ahead and see the password entry on the mysql.users table.

What we've just done is to set a 41 characters password hash for that account. Now, the next time the client wishes to connect, it must know in advance it is to expect a new password, otherwise it will encode a 16 characters hash, and try to match it with our new 41 characters hash. It is now time to perform:

root@mysql-5.1.51> SET GLOBAL old_passwords=0;
Query OK, 0 rows affected (0.00 sec

This will apply to all new connections made from that moment on (not affecting any existing connections). So, make sure you have updated passwords for all accounts.

To wrap it up, don't forget to set old_passwords=0 in the my.cnf file, or, better yet, completely remove the entry.

  • It would be great to see some more up-to-date sample my.cnf files including smaller scaled options for innodb?

  • I have this old MySQL parameters & configuration sample file post.
    It is outdated in itself. It relates to 5.0, and some things I know today - I didn't know back then 🙂

    I may publish a more up to date sample configuration file.

    "including smaller scaled options for innodb" -- don't you mean "larger"? The defaults are so very small! (better in 5.5)

  • 'old_passwords' was first of all interesting when MySQL 4.1 was introduced and PHP applications were in use. It took some time before PHP 'learned' the new password hash system. So at that time 'old_passwords' was simply necessary for applications to be able to connect.

    Actually we (Webyog) from time to time still encounter that people have an old Linux OS/distro (Fedora3, RHEL4 etc) with a non-updated Apache (1.x) and PHP (4.x) - even if MySQL has been upgraded to 5.x (Not to mention quite a lot of FreeBSD users of which some think that 'the older the more stable and better'. Or they are simply afraid to upgrade software on the *ultimately stable and best server OS* (sic!) because they don't know what to do if they bump into some dependency or conflict.

    I use to tell them if you cannot manage Linux/Unix, then use Windows instead (where *AMP softwares have no dependencies on the system itself)!

  • 😀

  • I believe this like should read.

    "You can just put a comment on the “old_passwords=1” entry in the configuration file. "

    You CAN'T just....

  • @Mark,
    indeed! Fixed

  • Does anyone know where to get a copy of the MySQL323 rainbow tables to reverse the old passwords?

  • Eric

    I had old_passwords=1 in my my.cnf. I am using MySQL 5.5. I commented it out and my clients are still able to connect. It DOES NOT appear that you have to make a simultaneous change as you stated in your post. My user's password hash is still the shorter version and my client can connect just fine. I believe the client can handle both types of hashing so it just uses the appropriate one. Though I do realize that if I want my existing users to have the longer password hash that I will have to update them.

  • @Eric,
    This is very interesting. Perhaps a now-new-backwards-compatibility?
    I have a very vivid recollection of clients failing upon login. I haven't tested this in a while now.

  • Graham Evans

    Worked perfectly for me. Thanks! Was upgrading a php-based BB system, installed php 5.4, and was having the hash length error problem on attempted connection. Your careful and thorough step-through of what to do helped considerably.

    Not sure why Mark said you can't just comment out the “old_passwords=1” line in my.cnf. You can comment out anything (including this line), if we simply restart MySQL. I did as much, and it works fine.

  • Pingback: mysql old_password 更新 | blog.ouyang.me()

  • Looking at the documentation for 4.0 - 5.6, old_passwords can't be set from the configuration file The only way I can find to set old_passwords is via SET.

    It may be specific to Percona Server though?

  • @Alfie,I'm afraid you're wrong on this. I've just set "old_passwords = 1" in my 5.5 server's /etc/my.cnf and it applies.

  • @shlomi I would like to be wrong, but I'm just not seeing the value change! I just tried doing the same thing on 5.0 and it was stuck on OFF.

    So what I found is that in Percona Server 5.1 I've got old_passwords = 1 by default. Are you able to change your 5.5 to 0 and verify that "SELECT @@old_passwords" is 0?

  • Got it!

    So it looks like @@old_passwords changes depending on how you logged in!

    If the account you logged in as has the old password format, @@oldpasswords = 1. If you're using the new password format, @@oldpasswords = 0.

    I only figured this out after migrating all passwords to the new format.

  • @Alfie, good insights, thanks for sharing!

  • Pingback: Some MySQL security tips | code.openark.org()

  • k srinivasan

    Hi I am using LDAP , There is no password is stored in mysql , I have include old_passwords=1 in my.cnf file also , Still i am getting the below error -- Error No:2059 , Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled , how to solve the problem ?

  • This has nothing to do with old_passwords. Are you trying to work with MySQL authentication plugin? Did you issue INSTALL PLUGIN? Just follow the manual on how to install plugins.

  • k srinivasan

    Hi , thanks for your replay , I have installed Percona PAM authentication plugin. We connect through LDAP. It is showing mysql_clear_password' cannot be loaded error only. Kindly some more ideas.

  • It could be a client side problem. 'clear text plugin' needs to be supported in the client. Only clients built with a recent connnector from Oracle do (not older Oracle connectors and not any connector from MariaDB).

    What client are you using?

  • k srinivasan

    it is not connecting WorkBench in the client system.

  • Ok .. you are using Workbench.

    Then think you should ask in the MySQL forms why WB does not connect to a server configured with LDAP. or even possible file a bug to bugs.mysql.com (if you think it is a bug).

    But before doing anyting like this, first ensure that your WB version is updated. There is no idea in reporting bugs with older versions.

    What about command-line client? Does it connect?

  • k srinivasan

    Workbench Version is 6.1 , It is connecting the command-line , But connect through WB or .net connectors.

  • k srinivasan

    Workbench Version is 6.1 , It is connecting the command-line , But connect through WB or .net connectors it is showing Error Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

  • Pingback: How to fix “Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password]” authentication error | JOZEF JAROSCIAK BLOG()

Powered by Wordpress and MySQL. Theme by openark.org