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

August 23, 2019 415 views
Databases MySQL PHP WordPress

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!

3 Answers
bobbyiliev MOD August 23, 2019
Accepted Answer

Hello,

The easiest way to fix that would be to alter you 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';

After that it should all work as normal.

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

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
  • Hello,

    How are you executing the query exactly?

    I would recommend running the query via your terminal for example.

    Regards,
    Bobby

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, 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

        If you use the tempuser user in your MySQL connection string, there would be no way to get the caching_sha2_password error as the user is not using that plugin.

        The only thing that is coming to my mind is that you might be using some caching modules like APCu or OpCache that could have cached the old information, but this is quite unlikely. Or if you are using a framework like Laravel, make sure to clear your application caches as well.

        Regards,
        Bobby

Have another answer? Share your knowledge.