Fleet database: MySQL => MariaDB migration

[pboushy]

• porpsect-interkosmos:
  • Gong call (full call): https://us-65885.app.gong.io/call?id=1026313349545804595&q=mariadb
  • Notes from prospect: https://docs.google.com/document/d/1RLeQt49gl2yqp6-F6RvV2BjQ2Vx8c-VH-DdVXeknF5w/edit?tab=t.0
• @noahtalerman: User requested this because many corporations prefer MariaDB over MySQL 8 due to its commitment to open source and concerns about Oracle's historic licensing changes. There are already 24 issues related to MariaDB compatibility in the GitHub repository, with requests dating back to 2021. For interkosmos there's an organization requirement for all self-managed, third-party tools to use MariaDB.
  • @noahtalerman: In the interim users must continue using MySQL 8 despite organizational preferences for MariaDB or PostgreSQL, potentially causing conflicts with corporate database standards and policies.
  • @noahtalerman: Eventually Fleet could update the server to fully support MariaDB 10 (ideally 10.11) and provide tested migration paths from MySQL to MariaDB.
• @allenhouchins: Typically large enterprises pick one database (DB) tool because of internal skills/knowledge and interoperability (play nice with other tools).

---

[phtardif1]

Updating with content on this:

https://us-65885.app.gong.io/call?id=1026313349545804595&highlights=%5B%7B%22type%22%3A%22SHARE%22%2C%22from%22%3A207%2C%22to%22%3A520%7D%5D

[noahtalerman]

Problem

FleetDM currently requires MySQL 8.

Many corporations prefer to use MariaDB because of it's commitment to open source, and Oracle's historic changes to licensing items. (e.g. JDK licensing changes) Or to use PostgreSQL for it's capability to handle larger databases.

Looking through Fleet's issues, there are a total of 24 issues related to MariaDB and a request back in 2021 for PostgreSQL

Most issues appear to relate to the JSON incompability between MySQL and MariaDB.

Potential solutions

Update Fleet server to support MariaDB 10 (ideally 10.11)

[iansltx]

Of note, MySQL builds are available from not-Oracle (Percona being the big player here). Additionally, AWS's Aurora MySQL-compatible edition is based on a non-Oracle MySQL fork that incorporates changes from upstream. Aurora is important here because our cloud environments, and our best-practice AWS deployment, use Aurora and Aurora does not have a MariaDB flavor.

Given that breaking compatibility with MySQL 8.0 isn't really an option (as it would break literally every current customer), one issue noted above is that MariaDB and MySQL have differing syntaxes for JSON-handling, so if there's no least common denominator way of expressing JSON functions (or shims to provide one) we have two options:

1. Wrap all usages of incompatible JSON functions in a layer that swaps in the compatible version of that function based on DB version detection. We could probably version-detect on server startup when it first connects to the database.
2. Drop back to lowest-common-denominator calls that don't use either DB's syntax (this could have significant performance implications, depending on how we do it).

To give an idea of impact, JSON_* functions alone, as well as their ->(>)("|')$. aliases are used ~130x in the codebase based on a quick search I just did. This is in addition to index-related issues; we'll want to mandate 16KiB InnoDB page size as that's the only way we get 3072-character index length to match MySQL 8's limitation.

We can get a better idea of what all is broken here by adding mariadb:10.11-ubi to our existing Go test matrix. This is of course simpler than swapping everything to Postgres (which from my experience would perform significantly poorer for our usage patterns), but will be a more significant lift than e.g. secondarily supporting MySQL 8.4/9.1 like we do now.

Not addressing a migration from MySQL to MariaDB in the above as on the surface that feels like "the easy part" but this may be better as something that gets tested and documented at a point in time and coordinated with customers at that time ("upgrade to X release, then migrate, then move on") rather than something that we maintain.

FWIW MariaDB 10.11 is not the most current release of MariaDB (11.x is), though MySQL 8.0.x was released well before it (~7 years vs. ~2.5 years).

[harrisonravazzolo]

Interkosmos does plan to support MariaDB 11.4 in the next couple months and agrees that it would make sense to support 11.x (instead of 10.4) and sustain support for future “long-term development release series” as documented in https://mariadb.com/kb/en/mariadb-server-release-dates/

They also think the proposal for a single-time migration from a given point release makes sense.

[iansltx]

Looks like MariaDB 11.4 adds a bunch of JSON functions (compared to 10.11) so hopefully that decreases the level of effort/amount of places where we can't use the same syntax for both DBs.

[lucasmrod]

@noahtalerman FYI: See https://github.com/fleetdm/fleet/issues/27400#issuecomment-2776130053. I will update the linked user story from the initially discussed 10.11 version to instead support version 11.4.

@harrisonravazzolo Double checking customer-interkosmos is on board?

[noahtalerman]

Hey @zayhanlon can you please own keeping track of ongoing conversations re Fleet Cloud v. MariaDB for interkosmos? I moved this request to #g-unicorns for now.

I removed the story from the drafting board.