Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED MySQL errors - password and plugin authentication specified

Discussion in 'Database Discussion' started by ronaldst, Nov 7, 2018.

  1. ronaldst

    ronaldst Well-Known Member

    Joined:
    Feb 22, 2016
    Messages:
    83
    Likes Received:
    13
    Trophy Points:
    8
    Location:
    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.
     
  2. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. ronaldst

    ronaldst Well-Known Member

    Joined:
    Feb 22, 2016
    Messages:
    83
    Likes Received:
    13
    Trophy Points:
    8
    Location:
    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
     
  4. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. ronaldst

    ronaldst Well-Known Member

    Joined:
    Feb 22, 2016
    Messages:
    83
    Likes Received:
    13
    Trophy Points:
    8
    Location:
    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.
     
  6. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice