I do not understand your decision to go with MySQL 8 instead of using the well established and widely used 5.7 branch when you created the product managed databases. I don’t get it. The general idea of a high availability managed databases is great but to only offer mySQL8 was - sorry to say - a stupid idea. Instead of creating a helpful product that saves us, developers, time your product just creates additional work we need to spend into our application to make it work with the latest MySQL version.
At the time point of writing, even the latest PHP version is not compatible with the new default authentication plugin caching_sha2_password of mySQL8.
I just wanted to save some time by using your product by replacing it with the local hosted MySQL 5.7 server of my existing droplet.
My first try was with Amazon RDS because I already expected issues with mySQL8 on digitalocean when I’ve noticed that limitation. So instead of that I went with Amazon RDS and was able to create a MariaDB cluster within a few minutes even though I did not have much experience with the amazon web services.
I started the MariaDB instance and could immediately connect it with my WordPress site in the DO droplet without doing any changes. Ok, the performance was bad due to the network latency but at least it worked immediately.
So I thought I could simply do the same with digitalocean, fire up a DO managed DB located in the same datacenter where my droplet is to bypass the latency problem.
So, the odyssey starts:
First of all, I noticed that I needed to update the MySQL client version on my droplet. Otherwise, I would not be able at all to connect to the DO managed DB at all. The MySQL 5.7 client is not compatible with MySQL 8.
Oh yeah funny, update the live database to a new major MySQL version! You know what this means: It takes tremendous time for testing the update procedure and everything that could possibly go wrong. We also need to make sure that the downtime would be minimum.
So I created a test server from a current snapshot and played through all the sceneries. I thought it would be worth the effort.
At the end after hours of additional extra work, I am still not able to connect WordPress to the managed DB and will need to invest further action into this.
After reading a lot of text it turned out I need to alter the plugin authentication of the DB user to mysql_native_password. But even doing this did not fix it. WordPress still refuses to connect to the managed DB instance. Even though I am able to connect to the server via mysql-client from and from my local system via MySQL workbench.
Everything works except WordPress in conjunction with your managed DB product and it still says:
Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
Yes, I’ve checked the db user via SELECT user,plugin,host FROM mysql.user WHERE user=‘USER’; and it has the correct authentication plugin and yes, I will probably find the solution when I invest more hours into this weird issue but honestly guys: My plan was to save some time and not to run from one issue into another by using a “managed” service.
Ubuntu 18.04 WordPress 5.3 PHP 7.2.24 MySQL 8.0.19
I love your other products and I am a big fan for years but for now, I can not recommend the managed database product to any of my colleagues due to your decision to use mySQL8 only.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
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.
MySQL support for 5.7 ends in a few months (Oct 2020), it would be bad decision to release a product that comes to EOL in less than 1 year. I would guess that this is the main reason why DigitalOcean decided to go with MySQL 8.
I had a similar problem with my PHP site, and I noticed that my webserver was using PHP 7.0 rather than 7.2 which was the PHP version of my PHP CLI. So I had to update the PHP-FPM version to 7.2 and it started working as normal.