Containerized MySQL Backup Regimen: Update?

OpenEMR Development
, ,
#1

Right now a lot of my containerized work is relying on MySQL and Percona XtraBackup for internal hot backups. I’m considering migrating to MariaDB (like basically everyone else uses) and Mariabackup, but the biggest excuse holding me back is that it will represent an uncrossable line – older backups from one will be unreadable in the new regime.

To my mind, that’s acceptable – we keep older versions of the containers accessible on Docker Hub – I just need to make sure not to make an upgrade such that a new container can still use the older database (so I don’t rupture a working environment when they upgrade OpenEMR and it suddenly wants to only use Maria tools on a MariaDB database it can’t find.)

Opinions?

#2

I’m having trouble understanding the scenario you’re describing.

“older backups from one will be unreadable in the new regime”

and

“when they upgrade OpenEMR and it suddenly wants to only use Maria tools on a MariaDB database it can’t find”

…seem like completely different circumstances to me. Expecting/requiring a backup to only be restored to the same version of OpenEMR/container that created it seems logical. Of course, that presumes that your new MariaDB stack will facilitate an upgrade to it from the prior MySQL-based one somehow.

#3

Well, that’s the thing. This won’t really upgrade MySQL at all. The OpenEMR container is separate from the databases it uses. Nobody currently using a MySQL server will switch over to a MariaDB server. So we’ll have (and excuse the made-up versions, please)…

Version 7 + MySQL/XtraBackup (guy who sets up AWS packages today)
Version 7 + MariaDB (guy who sets up container today with their own choice of DB server)

And then we’ll have…

Version 8 + MariaDB/MariaBackup (guy who sets up new AWS packages with new tech)
Version 8 + MariaDB (guy who sets up container under their own terms)
Version 8 + MySQL (this guy just doesn’t like MariaDB and that’s okay I guess)

All five of those circumstances are fine. But let’s talk about that first guy again. If he upgrades to the new OpenEMR, if he pulls in a new container image but leaves the rest of his system alone, which is allowable, he’ll be on “Version 8 + MySQL/Xtrabackup”. And that’s fine as long as I allow for it, as long as I make sure all the scripts keep working (so I guess I’d probably make new backup and recovery scripts with new names for MariaDB and change how they’re installed on the host systems).

Again, all fine as long as I understand I have to step around it.

But one thing that does make me mad is that we break the expectations of export and recovery – there is no ingestion path from the upgraded instance (that uses MySQL/XtraBackup tables and export files) into a fresh instance that uses MariaDB/Mariabackup. You’d have to either rely on the OpenEMR import process, hack something very particular together from my raw deployment objects, or just freehand it with mysqldump.

Worse yet, if I pursue this further and we choose to standardize on MariaDB instead of insist on compatibility, we’ll close the upgrade path to everybody currently using MySQL unless they solve exactly the problem I just discussed, upgrading their database engine right under the application and without the usual level of assistance I prefer to offer.

#4

I see your point. My naive snap reaction was just “well, Version 8 + MySQL/XtraBackup just shouldn’t be a supported thing” and there should be a defined migration/upgrade script to take a db dump from “Version 7 + MySQL/XtraBackup” and ingest into “Version 8 + MariaDB/MariaBackup”.

However, that means that the issue is more fundamental and probably not yours to fix:

I don’t think that MySQL is actually supported in the latest versions of OpenEMR. It’s not running on any of the demos, and is not included in any of the suggested compose files for the latest official docker images. Given that, how is it being tested? Is it part of someone’s CI/CD pipeline?

I already identified one issue (thankfully mitigated) related to MariaDB/MySQL divergence. The likelihood of there being more undiscovered has prevented me from upgrading my AWS instances, and it’s a problem that will only get worse as the two DBMS continue to evolve.

#5

Currently on the download page it states OpenEMR is compatible with "MariaDB versions 10.3 - 10.11, MySQL versions 5.7 - 8.0 (note that for MySQL 8 will need default-authentication-plugin=mysql_native_password setting). If possible, the project does recommend use of MariaDB instead of MySQL. "

We haven’t been forced to decide which way to go, although we do recommend MariaDB if have the choice. Note that MySQL still undergoes ci testing on github in OpenEMR’s PR’s and commits and is used in all the aws packages. Also can be tested in the insane dev environment, but i think i am the only one insane enough to use that environment. That being said, easy to change the engine in the docker-compose for easy dev environment if somebody wanted to test it. Don’t have it on the demos since the demo farm only supports one database for all the demos (could add support for another but that would take time and then it would lead to more demos; and at this point the demo farm is at its critical mass on its current aws instance).

Would consider it very acceptable to not touch the database vehicles (ie. let mysql instances keep doing their thing but slowly move towards mariadb on new official packages to prepare for that dark day). Bugs will present themselves and be fixed, which is currently manageable. And we will continue to avoid more advanced features such as JSON fields. At some point in the future, it may become no longer feasible and at that point, then may be time to enforce mariadb (with the upgrade for mysql users likely requiring a mysqldump step to migrate).

#6

On the ci forefront we did discuss that the project is in serious need of better ci testing (especially functional testing). For example, it appears the most recent adodb library that calls the databases decided to no longer work when there was no spacing between a placeholder/binder ? and a keyword in the query, which simply broke several arbitrary queries that were not noted until after the release. More broad ci testing, especially functional testing will help to prevent these issues (whether it be new packages, different database engines, etc.).

#7

Fair enough. But the lack of a communal staging environment for identification and replication of OpenEMR + MySQL functional issues is a large operational risk, today, for anyone with or considering cloud deployment. That’s what leads me to question whether the configuration is actually supported right now. If an expert hand needs to spin up a customized instance to investigate an issue, that really limits the potential for resolution.