Password and encrypt problem

I have recently upgraded my Linux from Mandriva to CENTOS and have encountered a problem with 'password' and 'encrypt' which worked previously. The current version of MySQL is 14.14. I have a table that includes both an 'encrypt'ed and 'password' password. So I do the following:

UPDATE mytable SET pwd=ENCRYPT('somepw'),pwdp=PASSWORD('somepw') WHERE username='myuser';

The result is that 1 row was successfully updated. Next I do the following:

SELECT username FROM mytable WHERE pwd=ENCRYPT('somepw');

Result is 0 rows returned. I also tried:

SELECT username FROM mytable WHERE pwdp=PASSWORD('somepw');

Again the result is 0 rows returned.

Now comes the interesting part. If I rerun the same update then look at the resultant strings in the table, 'pwd' is the same each time while 'pwdp' is different each time. First it does not make sense to me that 'pwdp' would change but explains why no rows were returned. Could that be some config problem? Second, while 'pwd' is consistent as expected, it makes no sense that it is not found in the table. Can someone help me make sense of all this and fix it? TIA.

Per the MySQL manual, you shouldn't use ENCRYPT or PASSWORD for storing application passwords in a MySQL database; the reason behind this is the fact that MySQL may log them into plaintext log files.

ENCRYPT will substitute a random salt if you don't pass one in the second argument, so the fact that it returns no results isn't surprising; however, I am surprised that it would appear the same in the result.

Some modes for PASSWORD will use a random salt too, I'm guessing that's probably what's happening here. I'm not too familiar with how it works and the manual is light on details since it is really only intended to be used for storing MySQL user account passwords. There is a system variable called "old_passwords" that you can set to change the behavior of PASSWORD.


MySQL is only up to version 5.6