Consider moving from MySQL to MariaDB for database backend

[jniles]

This is a non-urgent issue.

MariaDB is a nearly drop-in replacement for MySQL. It ships by default on Debian, Fedora, and their derivatives.

Why switch?

Truthfully, MySQL has become a bit of a pain to manage. I've grown tired of dealing with signing key expirations, installers that are being an Oracle terms and conditions page, and reports that MySQL 8 is slowing down. Several production sites have broken their apt upgrades due to broken package managers not finding the correct signing keys for mysql-community-server and it is easy to accidentally install 8.0 (a rolling release) instead of 8.4 (the LTS), meaning we don't fully know how our production environments may perform.

MySQL also has a convoluted installation process. On Debian-based systems, you need to first download the mysql-config-apt tool, configure your preferred version of MySQL (8.0 vs. 8.4), disable connectors, and then do another apt-get install for the community server. For automated/headless installs, this is a pain.

For these reasons, running MySQL feels riskier and is more annoying to manage.

Why not switch?

Despite all the above, MySQL has been solid in practice; it is the installation/upgrades that are a pain. I assume that MariaDB will also be rock solid, but jumping from a known quantity to an unknown is always a risk.

Miscellaneous Considerations

• Migrating production sites from MySQL -> MariaDB will likely be a huge pain. At each site, we'll likely need to dump the database to a text file, remove any MySQL-isms from the .sql file, and remove mysql, install MariaDB, and restore the database from the dump file. We'll probably want to do this on a test server before going with the real thing.
• If we do switch, we could migrate from the mysql2 adapter to the official MariaDB node adapter. It's supposedly faster in practice, though most likely not noticeable for our use case. The mariadb adapter seems to support both MySQL and MariaDB.
• The mydumper project, which I have been exploring to reduce the size of backups through incremental backups, supports both MySQL and MariaDB.
• MariaDB supports a native UUID datatype which may allow us to remove our silly HEX/UNHEX functions and transition some of the UUID logic from NodeJS to MySQL. We may consider migrating from UUIDv4 to UUIDv7 as well to improve temporal ordering.
• MariaDB and MySQL treat JSON columns differently, and MariaDB does not support the -> syntax that MySQL does for searching in JSON columns, preferring its own format. I believe some of our history tables (inventory, posting_journal) use JSON for storing data - we'll need to rewrite those interfaces. I don't anticipate this posing an issue, but it will require a breaking BHIMA change.

[jniles]

Interestingly, MariaDB also supports setting the language of the error messages, which would address #3367 without any other other programming to be done.