Managed Databases Feedback: mySQL 8 was terrible product decision!
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 cachingsha2password 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 mysqlnativepassword.
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: mysqlirealconnect(): The server requested authentication method unknown to the client [cachingsha2password]
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.
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.
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.