Question

How to change caching_sha2_password to mysql_native_password on a DigitalOcean's Managed MySQL Database?

Posted August 23, 2019 12k views
MySQLPHPWordPressDatabases

I want to use the new DigitalOcean MySQL Managed Databases with my PHP application but I’m getting this error here:

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Could anyone please let me know how can I change the authentication type for my *MySQL *user from caching_sha2_password to mysql_native_password?

Thank you!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
6 answers

Hello,

The easiest way to fix that would be to alter your existing user with the following:

ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';

Another thing that you could do is to create a new user with mysql_native_password. To do that you could use the following:

CREATE USER 'your_user'@'your_server_ip ' IDENTIFIED WITH mysql_native_password BY 'your_password';

Also as mentioned by @kavo13, try to upgrade PHP to 7.2 as well.

After that, it should all work as normal.

I’ve created a quick video demo on how to do that:

I’m running the test on an Ubuntu 18.04 Droplet.

Hope that this helps!
Regards,
Bobby

  • Thanks! That indeed fixed the problem.

  • Hi,

    i have the same problem. I’ve tried to create a new user or update an existent user, but i have no permitions using the “doadmin”. Can i acess with root?

    I can’t run “GRANT ALL” and the new user is created with no permitions.

    • Hello,

      You need to login with the doadmin MySQL user and then you will be able to use the following to grant privileges to the newly created DB user:

      GRANT ALL ON your_new_database.* TO 'your_new_user'@your_server_ip;
      

      With the managed databases you do not get root access.

      Hope that this helps!
      Regards,
      Bobby

      • Correct it must be:

        GRANT ALL ON your_new_database.* TO 'your_new_user'@'your_server_ip';
        

        Anyways, for me, this does not work at all. The mysql server still refuses to connect.

        • Hi @ReneH,

          I’ve replied to your other comment, but in case that you get a connection refused error, then it is most likely either the connection string or a firewall problem.

          • You need to also specify the non-standard 25060 MySQL port in your connection string

          • Make sure that you have port 25060 open for outgoing TCP traffic from your Droplet to the MySQL cluster

          • Add your Droplet’s IP address to the allow list for your Managed MySQL Cluster

          To test the connection from your Droplet to your MySQL Managed Database Cluster, you could SSH to your Droplet and use the telnet command:

          telnet db-mysql-xxxx.db.ondigitalocean.com 25060
          

          Feel free to share the exact error that you are getting and more details about your current setup.

          Regards,
          Bobby

          • Just for the sake of completeness for other people finding this question:

            With “refuse” I mean it throws the error:

            Warning: mysqli_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/example.com/htdocs/db.php on line 3
            
            Warning: mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in /var/www/example.com/htdocs/db.php on line 3
            bool(false)
            

            It’s refused because it does not accept mysqlnativepassword authentication of the database user that I’ve altered before.

            I will continue in the other thread to not pollute the thread here more: https://www.digitalocean.com/community/questions/problem-connect-php-7-3-and-mysql-8?comment=84864

Piggybacking on this here question.

I’ve tried the alter user command above, but then I get this error:

mysqli_real_connect(): Unexpected server respose while doing caching_sha2 auth: 109

For all who made all the changes and still not working, try to upgrade PHP to 7.2.

Works for me.

I have tried pretty much everything on this page. Still same error. Interestingly enough I even used a none existing user in the connection string to see if it was even making that far and it is not. It still says returns “The server requested authentication method unknown to the client”

I did try messing with the port, etc. just to make sure it was not on my end and got the errors that I expected.

I can connect fine from Navicat and verified everything…

mysql> select user,plugin,host from mysql.user where user='test_user';
+-----------+-----------------------+------+
| user      | plugin                | host |
+-----------+-----------------------+------+
| test_user | mysql_native_password | %    |
+-----------+-----------------------+------+
1 row in set (0.08 sec)

However when I try to connect with the following I still get the problem…

$vars['db_host']    = 'cyco-test-mysql-do-user-2169087-0.db.ondigitalocean.com:25060';
$vars['db_user']    = 'test_user';
$vars['db_pass']    = 'password';

$conn = new mysqli($vars['db_host'], $vars['db_user'], $vars['db_pass'] );

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

I am actually a little surprised that DO decided to go with the “new” default from MySQL 8, that very little actually supports yet, versus the standard that most already use. At the very least they should have an option at the server level versus the user level. Tons of people having the problem, after searching. (not exclusive to DO)

  • Hi @Clintre,

    As you already have the correct authentication type set, have you tried upgrading your PHP version to PHP 7.2 or higher? This should fix the error.

    Regards,
    Bobby

  • Yes setting the “authentication plugin” to the older one at the user level is not sufficient if the “default authentication plugin” for the server is set to the newer plugin.
    Because the old system cannot negotiate with the new plugin.
    You must therefore change the server’s “default authentication plugin” with: defaultauthenticationplugin = mysqlnativepassword in the my.ini configuration file. (%ProgramData%\MySQL\MySQL Server 8.0\my.ini on Windows). And then restart the server.

    • Interesting. That would explain why I can not make it work with my existing php app. You are not the only one who says this.

      How can we bypass that on digital ocean managed databases? We do not have access to the my.cnf there. I wonder why it works for some people and for others not.

I’ve got the same error:
mysqli::realconnect(): The server requested authentication method unknown to the client [**cachingsha2_password**]

My user has been created using mysqlnativepassword and granted “GRANT ALL” to the database i’m using.

When i try to flush privileges via doadmin i get this response:
Error Code: 1227. Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

I also tried to alter another user getting the same error.

  • Hello,

    The error that you are getting means that the user is still not using mysql_native_password. When you try to alter the doadmin user, do you get any specific errors?

    You don’t have to run flush privileges when using ALTER.

    Regards,
    Bobby

  • I too am facing the same issue.

    I created a new user from the control panel and used the ALTER query to use the mysql_native_password plugin.

    When I run query select user,authentication_string,plugin,host from mysql.user; it shows that the user is using mysql_native_password plugin.

    I get the following error when I try to connect with db(I am using PHP)
    mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password].

    • Yes, exactly the same issue/error. I double checked and run the select as well, and my users have the mysqlnativepassword plugin setup.

    • Hello,

      My guess would be that you have a few entries for this specific user.

      Here’s an example, I’ve created a new user called test_user:

      mysql> SELECT user,plugin,host FROM mysql.user WHEREuser='test_user' \G
      *************************** 1. row ***************************
        user: test_user
      plugin: mysql_native_password
        host: 1.1.1.1
      *************************** 2. row ***************************
        user: test_user
      plugin: caching_sha2_password
        host: 2.2.2.2
      

      As you can see one of the entries for host 2.2.2.2 is still using the caching_sha2_password plugin. So if I try to connect from host 2.2.2.2 my user would be using caching_sha2_password.

      Can you try running the query above and see if my guess is correct? Feel free to share the output here as well.

      Regards,
      Bobby

      • Hi, thanks for the response. I tried the query and this is the response.

        mysql> select user,plugin,host from mysql.user where user='tempuser';
        +----------+-----------------------+------+
        | user     | plugin                | host |
        +----------+-----------------------+------+
        | tempuser | mysql_native_password | %    |
        +----------+-----------------------+------+
        
        
        • Hello @surajjagtap

          Indeed this looks absolutely correct. Would you mind sharing the PHP code snippet that you are testing the MySQL connection with? Of course, make sure to hide your password and the hostname.

          I would like to test this at my end as well in order to be able to advise you further.

          Regards,
          Bobby

          • I have this same problem. This is my user table.

            +---------+-----------------------+------+
            | user    | plugin                | host |
            +---------+-----------------------+------+
            | doadmin | mysql_native_password | %    |
            +---------+-----------------------+------+
            

            I am able to connect with a Rails application, but not Wordpress.

            I am also able to log in to the managed database server with Mariadb’s client, which originally gave me the caching_sha2_password error message before changing the plugin to mysql_native_password.

            The wp-config settings are as follows.

                  define('DB_USER', 'doadmin');
                  define('DB_PASSWORD', 'VERY_GOOD_PASSWORD');
                  define('DB_HOST', 'bestestimatepro-do-user-user-id-0.db.ondigitalocean.com:25060');
            
          • Hi @cuddlyogre

            What is the error that you get when you try to with Wordpress?

            I’ve just tested this and it went through very smoothly.

            One thing that is coming to my mind is the fact that the 25060 port might be closed on your Wordpress droplet. Make sure that allow the outgoing tcp traffic on port 25060.

            To test the connectivity try running this telnet command:

            telnet your.managed.mysql.server.here 25060
            

            If the connection fails then it is definitely due to your firewall.

            Regards,
            Bobby

          • This is the telnet response I receive.

            J
            8.0.16ATU=/    ]�V;~tZ= nu^caching_sha2_passwordConnection closed by foreign host.
            

            This is the Wordpress error I receive

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

            doadmin is using mysql_native_password and I’m using it as the database credentials for Wordpress. I have created other users that use mysql_native_password with no success as well.

            select Host,User,plugin from mysql.user where user = 'doadmin';
            +------+---------+-----------------------+
            | Host | User    | plugin                |
            +------+---------+-----------------------+
            | %    | doadmin | mysql_native_password |
            +------+---------+-----------------------+
            
          • Hi, @bobbyiliev
            I am trying the following sample code to connect to my managed database Mysql:

            <?php
            ini_set('display_errors', 1);
            $servername = "<host_name>:25060";
            $username = "<username>";
            $password = "<password>";
            
            echo "$username";
            // Create connection
            $conn = new mysqli($servername, $username, $password);
            
            // Check connection
            if ($conn->connect_error) {
                die("Connection failed: " . $conn->connect_error);
            } 
            echo "Connected successfully";
            ?>
            
          • Hi @surajjagtap

            Thanks for sharing the snippet! I’ve tested this at my end and it seems to be working like a charm:

            • First I created a new cluster and tested it with the default details and as expected I got the expected error:
            php mysql-test.php
            
            Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /root/mysql.php on line 9
            
            • Then I ran the alter query:
            ALTER USER doadmin IDENTIFIED WITH mysql_native_password BY 'my_pass_here';
            
            • After that, I ran another test and it worked perfectly:
            php mysql-test.php
            
            doadmin
            Connected successfully
            

            Are you running this from your local PC? I might suggest creating a small new droplet and running the test from there just to make sure that there’s nothing on your current environment that is causing this strange behavior.

            Regards,
            Bobby

    • Hi, thanks for the response. I tried the query and this is the response.

      mysql> select user,plugin,host from mysql.user where user='tempuser';
      +----------+-----------------------+------+
      | user     | plugin                | host |
      +----------+-----------------------+------+
      | tempuser | mysql_native_password | %    |
      +----------+-----------------------+------+
      
      

I just want to contribute to this thread by stating that I too was seeing the same error message after switching my WordPress (PHP) droplet from a local db to a DO managed db. I had no trouble connecting to the managed db using mysql at the command line, but WordPress could not connect. I tried the ALTER USER command mentioned by @bobbyiliev, but it didn’t help. I noticed a couple of people in this thread suggested upgrading to PHP 7.2. My droplet was running Ubuntu 16.04 (PHP 7.0) so I decided to upgrade to Ubuntu 18.04 (PHP 7.2). That solved the problem! Note that it is still necessary to use the ALTER USER command @bobbyiliev mentioned, but once you do that on PHP 7.2 the managed db connection works fine.

Bottom line: It appears that only PHP 7.2 and above is compatible with MySQL 8.0 (the version currently used by DO’s managed MySQL dbs).

Submit an Answer