skip to Main Content

After updating (through -apt update and apt upgrade) access to the installation of MantisBT has become useless. The databases and users are intact.
Access to the server (VPS) through SSH is available and intact.

The connection to the database has failed. The error returned by the
database was # 2054: The server requested authentication method
unknown to the client Use the «Back» button in your web browser to
return to the previous page. There you can correct the problems that
have been identified in this error notification or select another
action. You can also click on an option in the menu bar to go directly
to a new section.

Error in phpmyadmin:

2054 – The server requested authentication method unknown to the client

mysqli_real_connect(): The server requested authentication method
unknown to the client [caching_sha2_password]

mysqli_real_connect(): (HY000/2054): The server requested
authentication method unknown to the client

Ubuntu 18.04.2 LTS

Release: 18.04

PHP 7.2.15-0ubuntu0.18.04.1 (cli) (built: Feb 8 2019 14:54:22) ( NTS )

mysql Ver 8.0.15 for Linux on x86_64 (MySQL Community Server – GPL)

Server version: Apache/2.4.29 (Ubuntu)

I modified the MySQL configuration:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpassword'

but I get the following error:

ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 51, found 50. Created with MySQL 80013, now running 80015. Please use mysql_upgrade to fix this error.

When trying to update the MySQL instance:

mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) while connecting to the MySQL server

Upgrade process encountered error and will not continue.

Error in access page MantisBT

Error in phpmyadmin

2

Answers


  1. Chosen as BEST ANSWER

    I updated the structure of tables with the command:

    mysql_upgrade -u root -p --force
    

    And the result is:

    Checking server version.
    Running queries to upgrade MySQL server.
    Upgrading system table data.
    Checking system database.
    mysql.columns_priv                                 Table is already up to date
    mysql.component                                    Table is already up to date
    mysql.db                                           Table is already up to date
    mysql.default_roles                                Table is already up to date
    mysql.engine_cost                                  Table is already up to date
    mysql.func                                         Table is already up to date
    mysql.general_log                                  Table is already up to date
    mysql.global_grants                                Table is already up to date
    mysql.gtid_executed                                Table is already up to date
    mysql.help_category                                Table is already up to date
    mysql.help_keyword                                 Table is already up to date
    mysql.help_relation                                Table is already up to date
    mysql.help_topic                                   Table is already up to date
    mysql.innodb_index_stats                           Table is already up to date
    mysql.innodb_table_stats                           Table is already up to date
    mysql.password_history                             Table is already up to date
    mysql.plugin                                       Table is already up to date
    mysql.procs_priv                                   Table is already up to date
    mysql.proxies_priv                                 Table is already up to date
    mysql.role_edges                                   Table is already up to date
    mysql.server_cost                                  Table is already up to date
    mysql.servers                                      Table is already up to date
    mysql.slave_master_info                            Table is already up to date
    mysql.slave_relay_log_info                         Table is already up to date
    mysql.slave_worker_info                            Table is already up to date
    mysql.slow_log                                     Table is already up to date
    mysql.tables_priv                                  Table is already up to date
    mysql.time_zone                                    Table is already up to date
    mysql.time_zone_leap_second                        Table is already up to date
    mysql.time_zone_name                               Table is already up to date
    mysql.time_zone_transition                         Table is already up to date
    mysql.time_zone_transition_type                    Table is already up to date
    mysql.user                                         Table is already up to date
    The sys schema is already up to date (version 2.0.0).
    Checking databases.
    

    When trying again to change the root password:

    ALTER USER root IDENTIFIED WITH mysql_native_password BY 'newpassword';
    

    The answer is:

    ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' 
    

    Inquiring about the error, I found several solutions, one of interest is the following:

    I know this is quite old issue, but seems still not solved fully. I met the same problem, and I solved this in following way.

    In my.ini file of MySQL, I changed default_authentication_plugin to mysql_native_password

    ' default_authentication_plugin=caching_sha2_password ' comment out default_authentication_plugin=mysql_native_password And then, at the MySQL Workbench, I set the password type of schema to standard, not sha2-something. Then it works.

    MySQL is version 8.0, and PHP is 7.26 running on Window 7.

    In summary, I think we should make the following adjustment in the MySQL configuration file:

    ' default_authentication_plugin=caching_sha2_password  ' comment out
    default_authentication_plugin=mysql_native_password
    

    The configuration file in WAMP is my.ini, the file in LAMP is unknown and the location (for me) is when I ask the whole community for help.

    Thanks in advance.


  2. Log in to MySQL console with root user:

    mysql -u root -p<password>

    Run the following code:

    ALTER USER root IDENTIFIED WITH mysql_native_password BY 'NEW PASSWORD';
    

    You must change ‘NEW PASSWORD’ to your new password and then try to login.

    Hope this will help.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search