MySQL errors - password and plugin authentication specified

ronaldst

• November 07, 2018 18:01

My MySQL error log keeps filling up with these errors. I'm running MariaDB 10.2. [Warning] 'user' entry 'mysql.session@localhost' has both a password and an authentication plugin specified. The password will be ignored. [Warning] 'user' entry 'mysql.sys@localhost' has both a password and an authentication plugin specified. The password will be ignored. [Warning] 'user' entry 'root@localhost' 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

  • November 08, 2018 18:07

  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? mysqlcheck mysql
mysql -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c
  

  0
• 

  ronaldst

  • November 08, 2018 20:05

  Thank you. mysqlcheck mysql [root@hostname ~]# 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 [root@hostname ~]# mysql -sse "SELECT user, host, plugin FROM mysql.user;" | awk '{ print $3 }' | sort | uniq -c 125 3 mysql_native_password
  

  0
• 

  cPanelLauren

  • November 09, 2018 12:13

  Hi @ronaldst The mysqlcheck came back without an issue. Can you run the following to update the users with mysql_native_password being used? 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: 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

  0
• 

  ronaldst

  • November 09, 2018 16:12

  I've followed your guidance, here's the log. 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.

  0
• 

  cPanelLauren

  • November 09, 2018 19:25

  Hi @ronaldst Awesome! Do let us know if you run into anything like that again but I think it's safe to say it's resolved as of now. Thanks!

  0

Please sign in to leave a comment.