Hi, I can’t connect php 7.3 with cluster database mysql 8
I did tests with previous versions of php 5.7, 7.1 and 7.2 also

User native

# user, plugin, host
'doadmin', 'mysql_native_password', '%'

PHP

<?php
ini_set('display_errors', 1);
$servername = "db-mysql-xxxxx-do-user-xxxxxx.db.ondigitalocean.com";
$username = "doadmin";
$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";
?>

Error

Warning: mysqli::__construct(): (HY000/2003): Can't connect to MySQL server on 'db-mysql-xxxxx-do-user-xxxxxx.db.ondigitalocean.com' (110) in /home/151548/public_html/prueba.php on line 9
Connection failed: Can't connect to MySQL server on 'db-mysql-xxxxx-do-user-xxxxxx.db.ondigitalocean.com' (110)

....

I read several DigitalOcean tutorials but none has helped me, what can I do?

edited by bobbyiliev

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.

×
2 answers

Hello,

As the Managed MySQL databases do not use the standard 3306 port, you need to make sure that you specify the Managed database cluster port in your connection string. After that this should work as normal.

I think that with mysqli the syntax should be something like this:

$conn = new mysqli(host, username, password, dbname, port);

Hope that this helps!
Regards,
Bobby

  • Thanks for answering, I had tried but all the same

    <?php
    iniset(‘displayerrors’, 1);
    $servername = “db-mysql-xxxxx-do-user-xxxxx-0.db.ondigitalocean.com”;
    $username = “doadmin”;
    $password = “password”;
    $database = “defaultdb”;
    $port = “25060”;

    echo “$username”;
    // Create connection
    $conn = new mysqli($servername, $username, $password,$database,$port);

    // Check connection
    if ($conn->connecterror) {
    die(“Connection failed: ” . $conn->connect
    error);
    }
    echo “Connected successfully”;
    ?>

    *error *

    Warning: mysqli::_construct(): (HY000/2003): Can’t connect to MySQL server on 'db-mysql-xxxxx-do-user-xxxxx-0.db.ondigitalocean.com’ (111) in /home/xxxx/publichtml/prueba.php on line 11
    Connection failed: Can’t connect to MySQL server on 'db-mysql-xxxxx-do-user-xxxxx-0.db.ondigitalocean.com’ (111)

    • Hi @chaochavez,

      I’ve tested the this and it worked with the following syntax:

      <?php
      ini_set('display_errors', 1);
      $servername = "db-mysql-xxxxx-do-user-xxxxxx.db.ondigitalocean.com:25060";
      $username = "doadmin";
      $password = "password";
      
      echo "$username \n";
      // Create connection
      $conn = new mysqli($servername, $username, $password);
      
      // Check connection
      if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
      } 
      echo "Connected successfully \n";
      ?>
      

      Note that I’m appending the port to the MySQL cluster hostname.

      Let me know how it goes!
      Regards,
      Bobby

      • Hello @bobbyiliev

        thanks for your effort. It’s obvious that you have never been able to reproduce our connection issues as it just “works” for you.

        I really appreciate your time to reproduce issues but I really ask you to escalate that ongoing mysql8 connection issue in your team because this issue is not solved for the majority of us. It’s obvious that are still many open requests related to mysqlnativepassword/cachingsha2password that never have been marked as resolved by the questionnaires. I guess these people moved to other products instead of spending more time into this. The managed database should save us developers time. At the moment it’s doing exactly the opposite and it’s impossible for us to fix the black-boxed managed database. It needs to work or it’s useless.

        I am fighting with this issue for 2 days and believe me I tried everything that has been mentioned by you in multiple community questions, tutorials and so on.

        It would be very interesting to know more about your testing setup because it must be different from what we use.

        If I jump to the same env you use I should be able to fix it or at least to bypass the issue.

        So please let us know your linux distro, mysql-client, the PHP version and your installed PHP modules.

        I assume you also use the latest managed database and not some kind of developers’ internal edition, right?

        • Hi @ReneH,

          There are a few things that you need to keep in mind:

          • Make sure that you’ve either created a new user with mysql_native_password or that you’ve altered the existing doadmin user:
          ALTER USER myuser IDENTIFIED WITH mysql_native_password BY 'mypassword';
          

          Or:

          CREATE USER 'your_user'@'your_server_ip ' IDENTIFIED WITH mysql_native_password BY 'your_password';
          
          • Make sure that you are using PHP 7.2+

          • 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.

          Regarding my setup, I use a standard Ubuntu 18.04 Droplet with PHP 7.2 along with a standard MySQL Managed Database.

          Also note that this is a community forum, in case that you need to get in touch with the DigitalOcean support team, you could use this link here:

          https://www.digitalocean.com/company/contact/#support

          Hope that this helps!
          Regards,
          Bobby

          • Thanks, Bobby, I already tried all these steps, several times in multiple configurations. Starting with:

            mysql -u doadmin -pPASSWORD -h private-db-mysql-nyc1-XXXXX-do-user-198xxxx-0.db.ondigitalocean.com -P 25060 -D defaultdb
            
            • By using account ‘doadmin’
            • Creating new account via CREATE with GRANT to default database “defaultdb”
            • Creating new account via CREATE … with GRANT … to new database “newdb”
            • Changed plugin authentication to mysqlnativepassword via ALTER … command for the default and the new account.

            This all works. All the commands are accepted. I can connect to the database via the command line and can ALTER the plugin authentication for the users.

            I’ve even destroyed and created the managed DB instances and repeated all the steps. just to make sure there is no fancy connection cache running.

            I tried PHP 7.2.24 and even updated to PHP 7.4.2 to exclude this from the possible list of failure reasons.

            Result of:

            mysql> SELECT user,plugin,host FROM mysql.user;
            +-----------------------+-----------------------+-----------+
            | user                  | plugin                | host      |
            +-----------------------+-----------------------+-----------+
            | doadmin               | mysql_native_password | %         |
            | repluser              | caching_sha2_password | %         |
            | root                  | caching_sha2_password | %:%       |
            | metrics_user_datadog  | mysql_native_password | ::1       |
            | metrics_user_telegraf | caching_sha2_password | ::1       |
            | mysql.infoschema      | caching_sha2_password | localhost |
            | mysql.session         | caching_sha2_password | localhost |
            | mysql.sys             | caching_sha2_password | localhost |
            +-----------------------+-----------------------+-----------+
            8 rows in set (0.00 sec)
            
            • port 25060 is open and IP is accepted

            Telnet response from the host machine (DO droplet):

            Connected to db-mysql-nyc1-XXXXX-do-user-1983244-0.db.ondigitalocean.com.
            Escape character is '^]'.
            J
            8.0.18▒
            ^{Kh▒1bp|qld&9vcaching_sha2_password Connection closed by foreign host.
            

            Setup is WordPress 5.3. Error is:

            Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/domain.com/htdocs/wp-includes/wp-db.php on line 1531
            
            Warning: mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /var/www/domain.com/htdocs/wp-includes/wp-db.php on line 1531
            

            It’s obvious from the error message, that the MySQL server does not use mysqlnativepassword, isn’t it?

            I’ve also used a small test connection code:

            $conn = mysqli_connect('db-mysql-nyc1-04xxx-do-user-198xxxxx-0.db.ondigitalocean.com', 'doadmin', 'PASSWORD', 'defaultdb', 25060);
            
            var_dump($conn);
            

            This returns the same error:

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

            I can create a connection via MySQL workbench 8.0 from my local computer with both accounts (defaultdb and the new one) but mySQL refuses to connect to any PHP app on the droplet with an error message related to “cachingsha2password”.

            It looks like the plugin authentication is completely ignored and PHP and MySQL only accepts cachingsha2password.

            I wonder if there is some connection cache in the MySQL server or mysqli that causes this kind of trouble?

            I should mention that the droplet originally contained a local MySQL 5.7 server that has been updated to mysql(-client) 8 to be able to use the DO managed DB instance.

            So this is not a fresh install because it is a snapshot from a production system.

            The only thing I did not test is to create a new Ubuntu installation from scratch to figure out if this solves it.

            Edit @bobbyiliev

            This is not related to WordPress. Even a regular mysqli_connect() connection does not work.

          • Hi @ReneH,

            Thank you for providing me with the detailed information!

            I will try to reproduce this with a Wordpress Droplet and I’ll get back to you.

            In the meantime, have you tried following the steps from this official DigitalOcean tutorial on dev.to on how to use Wordpress with a managed database:

            https://dev.to/digitalocean/how-to-install-wordpress-with-a-managed-database-on-ubuntu-18-04-3knc

            Regards,
            Bobby

          • Hi @ReneH,

            I’ve tested this with a new Wordpress 1-click setup Droplet from the Marketplace and a new Managed MySQL cluster.

            I followed the steps as described in the tutorial here and it on the first try.

            There are a few things that I could suggest in your case:

            • Make sure to clear your Wordpress cache in case that you use a plugin like W3 Total Cache

            • Add a PHP info file and visit it via your browser to make sure that your website is actually using PHP 7.2+, the PHP info file should look something like this:

            <?php
            
            phpinfo();
            
            ?>
            
            • In case that you are using any caching like Varnish, or PHP Opcache, make sure to restart the required services to be sure that there is no cache which could be causing the problem for you.

            Also as another test what you could try doing is to set up a new Droplet, follow the steps from the tutorial here and then once it is up and running and connected to the Managed MySQL Cluster, copy your Wordpress files from your Droplet to the new one and see if it works from the new Droplet with your old Wordpress files.

            Let me know how it goes!
            Regards,
            Bobby

          • @bobbyiliev I need to reply here. (there is no reply button under your latest message probably due to the deeply nested comments)

            I’ve tested this with a new Wordpress 1-click setup Droplet from the Marketplace and a new Managed MySQL cluster.
            I followed the steps as described in the tutorial here and it on the first try.

            I will do the same now and will repeat all the steps on the marketplace WordPress instance.

            By the by, the same error happens with native php mysqli connection. So it is definitely not related to WordPress on my system. (There is also no cache running):

            $conn = mysqli_connect('db-mysql-nyc1-04xxx-do-user-198xxxxx-0.db.ondigitalocean.com', 'doadmin', 'PASSWORD', 'defaultdb', 25060);
            
            var_dump($conn);
            

            This returns the same error:

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

            If the marketplace WordPress droplet works I will compare both machines to find out what is different.

            I will keep you updated. Thanks so far.

          • @bobbyiliev
            Thanks for all your work. I think it’s solved. All your recommendations were right!

            It turned out that nginx still used php 7.0 and I did not notice it until I ran phpinfo(). (php -v already returned PHP 7.4) What an embarrassing errorblush

            At least I can confirm that we need PHP 7.2 to make this running:-)

          • Hi @ReneH,

            No problem at all!

            Happy to hear that you’ve got it working now and thank you for sharing the solution here with the community.

            Regards,
            Bobby

Hi, I had already tried but not yet connected, when I write the port in the code it disconnects me immediately and sends me the error message
Unknown MySQL server host

Without port, the page tries to communicate with the mysql server, approximately 1 minute later it shows the error
Can’t connect to MySQL server

Port
Warning: mysqli::_construct(): (HY000/2005): Unknown MySQL server host ‘db-mysql-xxxx-do-user-2457458-0.db.ondigitalocean.com:25060’ (0) in /home/xxxx/publichtml/prueba.php on line 11
Connection failed: Unknown MySQL server host 'db-mysql-xxxx-do-user-2457458-0.db.ondigitalocean.com:25060’ (0)

no port

Warning: mysqli::_construct(): (HY000/2003): Can’t connect to MySQL server on 'db-mysql-xxxx-do-user-2457458-0.db.ondigitalocean.com’ (110) in /home/xxxx/publichtml/prueba.php on line 11
Connection failed: Can’t connect to MySQL server on 'db-mysql-xxxx-do-user-2457458-0.db.ondigitalocean.com’ (110)

Any other advice? I have followed all questions of digitalocean, I’ve been trying to connect for several days

  • Hi @chaochavez,

    What is the exact PHP version that you are using?

    I believe that I’ve seen this error before, have you tried to specify the port as a separate argument:

    $conn = mysqli_connect('db-mysql-xxxx-do-user-2457458-0.db.ondigitalocean.com', 'your_user', 'your_password', 'your_database', 25060);
    

    Regards,
    Bobby

    • Thanks, in my case I solved it, no previous answer helped me.
      I have another server in CPANEL outside of digitalocean, I had blocked port 25060 with the firewall.

      I recommend you try local “localhost 127.0.0.1” first to see if they have a connection.

      Thanks for the help

Submit an Answer