MySQL: 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:
email@example.com> SET SESSION old_passwords=0; Query OK, 0 rows affected (0.00 sec) firstname.lastname@example.org> SELECT PASSWORD('123456'); +-------------------------------------------+ | PASSWORD('123456') %