Getting SQLSTATE[HY000] [2054] The server requested authentication method unknow

[Siv]

Hi,
I am just setting up Roundcube on an Ubuntu 19.10 Server and have downloaded the roundcube files and set up everything and am running through the webmail installer and everything is coming up OK except the    Check DB config section which says:

Code Select Expand

SN (write):  NOT OK(SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client)

Make sure that the configured database exists and that the user has write privileges
DSN: mysql://roundcubeuser:Lynn%3F1958@localhost/roundcube

I have checked that the user has write privileges and they do:

Code Select Expand


mysql> SELECT * FROM mysql.user WHERE user = 'roundcubeuser'\G
*************************** 1. row ***************************
                    Host: localhost
                    User: roundcubeuser
             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: caching_sha2_password
   authentication_string: $A$005$1/s<J:zIf+nl68imY8xlIvvylJD/3yaT9ydelWOcgP.FAT3QE0MLj1
        password_expired: N
   password_last_changed: 2019-11-17 01:19:45
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL
1 row in set (0.01 sec)

mysql> SELECT * FROM mysql.db WHERE user = 'roundcubeuser'\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: roundcube
                 User: roundcubeuser
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)


Reading up various things in these forums and elsewhere it seems to be something to do with the password hashing system using the wrong number of digits, I have tried everything I can find and nothing seems to work. Could someone point me to what I need to change so that I can get Roundcube running.

Siv

[JohnDoh]

What versions of PHP and MySQL are you running? Did you see this in the PHP manual?

MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

[Siv]

JohnDoh,

SQL Version is: 8.0.17-0ubuntu2
PHP Version returned by php -v is:

Code Select Expand

php -v
PHP 7.3.11-0ubuntu0.19.10.1 (cli) (built: Oct 24 2019 11:38:49) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.3.11-0ubuntu0.19.10.1, Copyright (c) 1999-2018, by Zend Technologies


I didn't look at the PHP side of things as I am just following a procedure to set up Roundcube webmail on a server.  I must admit to thinking the issue was more to do with MySQL than PHP.

Siv

[Siv]

I finally fixed the issue, I had to do the following:

1.    Edit the /etc/mysql/my.cnf file and add this section:

   

Code Select Expand

[mysqld]
default-authentication-plugin=mysql_native_password
bind-address            = 127.0.0.1


2.    Remove the user and re-create it as follows:
       Log into the MySQL shell with:
       

Code Select Expand

sudo mysql -u root

       Drop the existing user:
       

Code Select Expand

DROP USER 'roundcubeuser'@'localhost';

       Recreate the user with slightly different parameters:
       

Code Select Expand

CREATE USER 'roundcubeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EnterYourPasswordHere';

       Grant that newly created user permissions on the database in my case that's called "roundcube":
       

Code Select Expand

GRANT ALL PRIVILEGES ON roundcube.* TO roundcubeuser@localhost;

       Then flush the privileges table to make the changes stick:
       

Code Select Expand

flush privileges;

       Finally Exit the MySQL Shell:
       

Code Select Expand

exit;

       Then restart MySQL:
       

Code Select Expand

sudo systemctl restart mysql

       Then Restart Apache:
       

Code Select Expand

sudo systemctl restart apache2

       After that I was able to open Roundcube! Hurrah!