mysql 1827_ERROR 1827 (HY000): The password hash doesn't have the expected format

As of MySQL 5.6.5, pre-4.1 passwords and the mysql_old_password authentication plugin are deprecated. Passwords stored in the older hash format used before MySQL 4.1 are less secure than passwords that use the native password hashing method and should be avoided. To prevent connections using accounts that have pre-4.1 password hashes, the secure_auth system variable is now enabled by default. (To permit connections for accounts that have such password hashes, start the server with --secure_auth=0.)

DBAs are advised to convert accounts that use the mysql_old_password authentication plugin to usemysql_native_password instead. For account upgrade instructions, see Section 6.3.8.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

Known issue: In some early development versions of MySQL 5.6 (5.6.6 to 5.6.10), the server could create accounts with a mismatched password hash and authentication plugin. For example, if the default authentication plugin is mysql_native_password, this sequence of statements results in an account with a plugin ofmysql_native_password but a pre-4.1 password hash (the format used by mysql_old_password):

SET old_passwords = 1;

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

The mismatch produces symptoms such as being unable to connect to the MySQL server and being unable to useSET PASSWORD with OLD_PASSWORD() or with old_passwords=1.

As of MySQL 5.6.11, this mismatch no longer occurs. Instead, the server produces an error:

mysql> SET old_passwords = 1; mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; ERROR 1827 (HY000): The password hash doesn't have the expected

format. Check if the correct password algorithm is being used with

the PASSWORD() function.

To deal with an account affected by a mismatch, the DBA can modify either the plugin or Password column in the account's mysql.user table row to be consistent with the other column:

Set old_passwords to 0, then assign a new password to the account using SET PASSWORD and PASSWORD(). This sets the Password column to have a 4.1 password hash, consistent with the mysql_native_passwordplugin. This is the preferred method of fixing the account.

Alternatively, the DBA can change the plugin to mysql_old_password to make the plugin match the password hash format, then flush the privileges. This is not recommended because the mysql_old_password plugin and pre-4.1 password hashing are deprecated and support for them will be removed in a future version of MySQL.