MySQL Errors - You must SET PASSWORD/Password hash should be a 41-digit hexadecimal number

Question

All, this is probably a simple fix but I can't seem to get it working...

I'm trying to set up a MySQL database (on RHEL) but getting the following errors:

mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD('new_pass');
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number
mysql> SELECT PASSWORD('new_pass');
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

I've tried every possible command/query and I cannot bypass these two error messages. Maybe something's up with my permissions? Any thoughts? Thanks!

Answer 1

use this SET old_passwords = 0;

The old_passwords system variable value determines the hashing method used by PASSWORD(). If SET PASSWORD rejects the password as not being in the correct format, it may be necessary to change old_passwords to change the hashing method. For example, if the account uses the mysql_native_password plugin, the old_passwords value must be 0:

SET old_passwords = 0; SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass'); If the old_passwords value differs from that required by the authentication plugin, the hashed password value returned by PASSWORD() is not acceptable for that plugin, and attempts to set the password produce an error. For example:

mysql> SET old_passwords = 1; mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass'); ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number Permitted old_passwords values are described later in this section.

Using the OLD_PASSWORD() function (permitted before MySQL 5.7.5 only):