SOLVED MySQL errors - password and plugin authentication specified

ronaldst

Well-Known Member
Feb 22, 2016
85
16
8
Norway
cPanel Access Level
Root Administrator
My MySQL error log keeps filling up with these errors. I'm running MariaDB 10.2.

Code:
[Warning] 'user' entry '[email protected]' has both a password and an authentication plugin specified. The password will be ignored.

[Warning] 'user' entry '[email protected]' has both a password and an authentication plugin specified. The password will be ignored.

[Warning] 'user' entry '[email protected]' has both a password and an authentication plugin specified. The password will be ignored.
I'm trying to figure out what exactly is the cause of this and a solution.

All 3 errors appear with the exact same timestamp, and they appear 1-2 times every 13 minutes.

Thank you.
 

cPanelLauren

Forums Analyst II
Staff member
Nov 14, 2017
9,002
761
263
Houston
cPanel Access Level
DataCenter Provider
Hi @ronaldst


From the times I've seen this before it's usually indicative of corruption of the mysql database or the mysql_native_password plugin. Can you run the following and let us know the output?

Code:
mysqlcheck mysql
Code:
mysql -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c
 

ronaldst

Well-Known Member
Feb 22, 2016
85
16
8
Norway
cPanel Access Level
Root Administrator
Thank you.

mysqlcheck mysql
Code:
[[email protected] ~]# mysqlcheck mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_executed                                OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

mysql -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c
Code:
[[email protected] ~]# mysql -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c
    125
      3 mysql_native_password
 

cPanelLauren

Forums Analyst II
Staff member
Nov 14, 2017
9,002
761
263
Houston
cPanel Access Level
DataCenter Provider
Hi @ronaldst


The mysqlcheck came back without an issue. Can you run the following to update the users with mysql_native_password being used?


Code:
UPDATE mysql.user SET plugin = '' WHERE plugin = 'mysql_native_password';
FLUSH PRIVILEGES;
You'd have to run this from the mysql command line and it'd look similar to this:

Code:
mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 491
Server version: 10.2.18-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET plugin = '' WHERE plugin = 'mysql_native_password';
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11  Changed: 11  Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Then please let me know if the issue persists
 

ronaldst

Well-Known Member
Feb 22, 2016
85
16
8
Norway
cPanel Access Level
Root Administrator
I've followed your guidance, here's the log.

Code:
MariaDB [(none)]> UPDATE mysql.user SET plugin = '' WHERE plugin = 'mysql_native_password';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
I have not seen any errors/notices in logs since.

Thank you.