Description:
The purpose of the mysql_native_password and mysql_old_password authentication plugins is to authenticate against the password stored in the mysql.user table Password column. Yet in MySQL 5.5, if the plugin column is set to either of those plugins *and* the password is specified, the server complains about it by logging warnings to the error log. This makes no sense because those plugins are *designed* to use Password column contents.
How to repeat:
Example script:
SET old_passwords = 0;
CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD FOR 'x'@'localhost' = PASSWORD('x');
SELECT * FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'x'@'localhost';
SET old_passwords = 1;
CREATE USER 'y'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR 'y'@'localhost' = PASSWORD('y');
SELECT * FROM mysql.user WHERE (User, Host) = ('y', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'y'@'localhost';
Run the script in 5.5.37 (current 5.5 source tree), and no errors are seen:
mysql> CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'x'@'localhost' = PASSWORD('x');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
*************************** 1. row ***************************
Host: localhost
User: x
Password: *B69027D44F6E5EDC07F1AEAD1477967B16F28227
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> DROP USER 'x'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'y'@'localhost' IDENTIFIED WITH 'mysql_old_password';
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'y'@'localhost' = PASSWORD('y');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM mysql.user WHERE (User, Host) = ('y', 'localhost')\G
*************************** 1. row ***************************
Host: localhost
User: y
Password: 606706696665cdda
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_old_password
authentication_string:
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP USER 'y'@'localhost';
Query OK, 0 rows affected (0.00 sec)
And this is what the server writes to the error log:
140123 20:48:48 [Warning] 'user' entry 'x@localhost' has both a password and an authentication plugin specified. The password will be ignored.
140123 20:49:33 [Warning] 'user' entry 'y@localhost' has both a password and an authentication plugin specified. The password will be ignored.
Those warnings are spurious. They lead to the impression that something is wrong with the account when that is not the case.
These warnings do not appear if the script is run against MySQL 5.6 or 5.7, so this appears to be a 5.5-only problem.