Upgrading passwords from old_passwords to “new passwords”

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.