RFC: Dropping support for MySQL

Development RFCs
1

We discussed again our journey to a single RDBM in another thread and I want to pull it to the top:

The ultimate goal is to have only a single RDBM for both development and production. This will simplify code, support and allow us to leverage specific feature of the picked system which is PostgreSQL. The proposal and schedule is:

  • announce on our blog that 1.23 will be the last release supporting MySQL
  • implement a warning UI bar that MySQL is ending soon
  • release 1.23 with tested documentation on how to migrate to MySQL
  • drop MySQL from CI
  • drop MySQL from docker image
  • remove all MySQL custom queries from the codebase
  • remove MySQL from the installer
  • release 1.24 with tested documentation on how to upgrade
  • solve technical challenge of generating apipie bindings without SQLite
  • drop SQLite
  • remove all custom code SQLite
  • start working on integrating modern PostgreSQL features, for example
    • macaddr, macaddr6, ip, ip6
    • JSON/JSONB
    • full text search
    • hash indexes
    • partitioning
    • stored procedures (for very specific updates - e.g. fact or report import)

Optionally:

  • push a warning bar in a 1.22.x update to inform users even more early

I am willing to be owner of this effort if we agree with the plan.

Now, this is possibly disturbing change for our users. Unfortunately we donā€™t have data on how many users have MySQL, I looked in Community Surveys 2016-2019 and we havenā€™t asked. So no idea how many users will be affected.

The goal for the initial phase is to gather feedback from the field. In the worst case, we end up with instructions how to migrate database (we already have something in our docs) and we can cease or postpone actual removal if it turns out too many users are affected.

3 Likes
2

I like the idea, as it allows the developers to support one DB really well, rather than all of them somehow and sets the proper expectations, rather then claiming we support something that just minority of developers would use in reality. It also goes in sync towards more opinionated setup, as the feedback weā€™re hearing is ā€œtoo many optionsā€.

It would be good to know the reasons why people are on MySQL. Possible options I can think of:

  1. I just know MySQL more than PostgreSQL, therefore itā€™s my default choice:
    seems like this itself should not be hard blocker: I would guess avoiding dealing with Postgres in one way or another is pretty hard in 2019
  2. I need to run DB in very specific setup, that is not possible in PostgreSQL:
    In this case, we should hear more about the specifics, consider options to achieve them in PostgreSQL and compare the negative impact in case we donā€™t support it with the positive impact of being able to use the PostgreSQL specific features.
  3. Iā€™m not allowed to use anything but MySQL in my organization:
    Iā€™m not sure it would even be realistic, just keeping it here just for completeness

Any other reasons to take into account?

3

I like the plan. I didnā€™t see droppping foreman-mysql2 and foreman-sqlite rpm packages, removing respective bundler groups in the list, but I suppose it didnā€™t aim to be complete list. Btw hereā€™s a blog post about migrating the DB Foreman :: Migrating Foreman from MySQL to Postgresql it would be good to verify it still works on some production DB, but most likely can be recycled :slight_smile:

4

In our case it was option no. 1. However this decision was made back in 2015 ā€¦

5

Would be interesting to hear from @Dirk and @Bernhard_Suttner- do you have customers using MySQL in production? If so, what are the considerations for it?
I know Red Hat Satellite customers are all using PostgreSQL as that is the only database supported by Katello. I assume that Orcharhino also only ships with PG for the same reason, but would be good to confirm.

6

We are also using PostgreSQL and happy to focus at one DBMS. Pretty sure that this improves the stability of foreman / katello.

3 Likes
7

No MySQL-based customers on our side. I had some asking for but all followed my (or Projects) recommendation for PostgreSQL.

3 Likes
8

Ok everyone, deal. Here is the final list of items to get MySQL removed, I am the owner, feel free to add comments if you miss an item on the list:

https://projects.theforeman.org/issues/27035

2 Likes
9

For the record, another MySQL problematic thing is that MySQL does not support transactions for DDL, so when migration fails it can easily get into bad state which can be only fixed by manual changes:

10

I guess, postgresql is then the only supported DB engine. In this case, we can think about using postgresql functions which are really fast.

11

TBH we fixed this kind of issues long time ago by using digests,
surprisingly, back then it was a PostgreSQL issue (see

)

1 Like
12

I support this effort. Foreman should be installed on its own dedicated machine, so it makes sense to focus on one dedicated path forward re: databases.

1 Like
13

And I have refactoring reports to a single blob column on my short-term plate now :slight_smile: Time flies! :wink:

Btw itā€™s not the index limit what I refer to but the fact that DDL is not secured by transactions. All migrations for MySQL can fail and get the DB into unrepairable state (or big effort to investigate).

14

Itā€™s happening!

15

in general it is a good idea. ā€¦ only Iā€™ve running mysql/mariadb and having issues to migrate to postgres. see this thread:problem-with-database-migration-mariadb-postgres

16

Letā€™s continue in the other thread, thanks.

17

Considering we are branching for 1.23 today and the blog post is not published yet, lets make 1.25 the PG-only version. This will give us more time to test and verify the migrations work and our users more time to prepare for the migration.

1 Like
18

Updating the RM issue, blogpost, PR, hopefully thatā€™s all.

19

This is in progress for 2.0, marking as resolved.

1 Like