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.

tags: ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

16 Comments to "Upgrading passwords from old_passwords to "new passwords""

  1. MySQL Boy wrote:

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

  2. shlomi wrote:

    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)

  3. Peter Laursen wrote:

    '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)!

  4. shlomi wrote:

    :D

  5. Mark Grennan wrote:

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

  6. shlomi wrote:

    @Mark,
    indeed! Fixed

  7. Mark Grennan wrote:

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

  8. Eric wrote:

    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.

  9. shlomi wrote:

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

  10. Graham Evans wrote:

    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.

  11. mysql old_password 更新 | blog.ouyang.me wrote:

    [...] 参考 参考 This entry was posted in system administration. Bookmark the [...]

  12. Alfie John wrote:

    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?

  13. shlomi wrote:

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

  14. Alfie John wrote:

    @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?

  15. Alfie John wrote:

    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.

  16. shlomi wrote:

    @Alfie, good insights, thanks for sharing!

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org