PHP and the DigitalOcean MySQL cluster

Written by Stefan Koopmanschap / Original link on Oct. 4, 2019

One of the weak points in our Rancher 1 cluster has always been the database. Because databases and docker (due to the persistence issues) has always been an issue, we solved that by installing a MySQL server on the same droplet that our Rancher server was running on. While it worked fine, it was always a bit of a weak spot: If that single MySQL server would crash, all our applications would go down. And since we work mostly on customer projects and don't have a dedicated ops person, we wouldn't always been able to immediately respond to such a crisis.

So when DigitalOcean announced their managed MySQL cluster, I was quickly excited about this new project. It took a while for them to launch it publicly and roll it out to "our" region (AMS3), but they did so just over a month ago, so I decided to go for a testdrive.

Creating my first database

Creating a database wasn't all that hard. DigitalOcean has an interface for this. It is literally filling in your database name and clicking a button to set up your database. The same goes for creating a user. This interface is quite limited though: You can not input any configurations (such as the authentication method for users), which can cause issues (as you will later see). But in all its simplicity, it does what it is supposed to do. Create a database. Create a user.

Migrating the database

Migrating the database was actually quite easy. Dump the database from the old MySQL server, import it on the new server. I used MySQL Workbench to do this, and that worked fine.

Mind you: The first application I did this one had a very simple schema. DigitalOcean managed MySQL uses MySQL 8, so in more complex databases I can imagine there might be issues due to backwards compatibility breaks. YMMV.

Configuring my application

I had a very simple issue to solve on my application: My configuration did not allow me to set the port for the DSN, and since DigitalOcean sets a custom MySQL port, this meant I had to make a very simple code change so I could configure the port that was used. A simple one line change solved that issue. No problem so far.

When I deployed a new version of my application with this fix however, I ran into a bigger issue:

Fatal error: Uncaught PDOException: PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/web/index.php

Oops! Yes, MySQL now defaults to a new authentication method, caching_sha2_password. This is not supported by PHP yet.

This is where I come back to my earlier mention of how the simplistic user management of DigitalOcean is causing problems: We can't switch to another authentication method! Fortunately, we can execute a query to actually switch back to the old authentication method. So let's do that:

ALTER USER 'myuser'@'%' IDENTIFIED WITH mysql_native_password BY 's3cr3t_p@ssw0rd';

After doing this, my application worked again. Yay, my application is now running on the new managed MySQL cluster!

About caching_sha2_password

Jaap and I did some digging to figure out why this new MySQL 8 authentication method is not yet supported by PHP. Because really, MySQL is the main database engine used by PHP developers, so why would it not be supported? It turns out, it was! If you use PHP 7.2.9 (at least the docker image for that), you can actually use the new caching_sha2_password method. Your application works fine. However, after that change, a bug was reported after which the support for the new authentication method was reverted. As it looks right now, the changes for the authentication method are back in the master branch, which would mean PHP 8 will support the new authentication method. Until then, we'll have to switch back to mysql_native_password for our PHP applications (or use the PHP 7.2.9 docker image, which I would advice against).

The verdict

Migrating to the new database cluster was easier than I expected and, with one minor work-around, it is easy to get your application up and running with the new DigitalOcean MySQL cluster. We will surely be migrating all application to this new platform. It's up to DigitalOcean to extend their application a bit more, for instance to be able to configure the authentication method for a user or default character set of the database. So far, I'm a happy user of this new DigitalOcean product.

Disclaimer: This is not a sponsored post. I am not affiliated with DigitalOcean other than that I am a happy customer.


« Solving OpenAPI and JSON Schema Divergence - Excluding generated files in your project »