Question

Since a WordPress URL change I can't connect to DO Managed MySQL database

Posted December 21, 2021 165 views
NginxMySQLWordPressDigitalOcean Managed MySQL DatabaseDigitalOcean Droplets

So we have a WordPress 5.7 site running on an Ubuntu 16.04.3 x64 droplet, which uses a separate DigitalOcean managed MySQL 8 database. All was fine but I needed to change the site’s url. I did this and suddenly I got the dreaded ‘Error establishing a database connection’ and I can’t access it, or the wp-admin at all.

The WordPress site is a Trellis LEMP stack site. PHP version 7.3.19-1.

Things I have checked/done.

Added the DNS to point to the droplet for the new domain name.

Initially I altered all occurrences of the old domain name with the new domain name, in the database wpoptions table, and with a 'wp search-replace 'example.dev’ 'example.com’ –skip-columns=guid’, and updated the etc/nginx/sites-available/mysite_name.conf to match the new domain.

In the .env file DBNAME, DBUSER, DBPASSWORD and DBHOST are correct - I haven’t altered them and have compared with the DO DB connection details page.

I can connect to the DB okay using MySQL Workbench.

If I SSH into the droplet I can run a 'wp db check’ and everything passes with the credentials it has saved.

I tried a PHP file to check DB connection using mysqli($DBHOST, $DBUSER, $DBPASSWORD, $DBNAME) and this reports (Warning: mysqli::_construct(): The server requested authentication method unknown to the client [cachingsha2_password]).

The user used to connect is set to 'Legacy MySQL 5.x’ in DO DB control panel.

I tried to create a new user from scratch.

Droplet is added as a trusted source to the DB (I changed nothing in the control panel since it worked)

I rebooted the droplet, and asked DO to reboot the DB instance.

I can SSH into my WP droplet and telnet into the managed db instance fine.

The site is not secured with SSL, but it wasn’t before the name change so I figured that wasn’t the problem.

This is a staging setup so no one is screaming yet, but I need to get this running ASAP!

Any ideas how to troubleshoot this?

Many thanks!

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.

×
Submit an Answer
1 answer

This turned out to be an issue with php7.3-fpm.service, which was required to be running, but wasn’t as php7.1-fpm.service was running instead. No idea how or why, but stopping one and starting the other fixed the issue immediately.