Minimum RDBM requirements

Hello,

during triage of a bug (Bug #26939: ActiveRecord::ValueTooLong: PG::StringDataRightTruncation: ERROR: value too long for type character varying(255) - Foreman) I realized that to fix the issue MySQL version 5.0 or newer must be installer. However we do not say which minimum SQL server versions Foreman require. I think until now our strategy was to assume that users are installing embedded databases which ship with OS which we support. That’s probably not always the case, some users might even configure Foreman with external databases which can often be provided as a service.

Do we want to specify minimum RDBM requirements? Once we do that, it is probably worth looking on what these new versions can offer and optimize our schema a bit (new index types, larger values or limits).

Or do we want to open the “let’s drop MySQL and SQLite” topic again?

1 Like

I grew tired of the RoR’s “we work on any database” as it is mostly more pain than gain in the long term. So I would be in favor of dropping the support for everything but PostgreSQL.

I would say MySQL is not such painpoint as SQLite is though, as for MySQL we always can find a workaround, just need an if statement, but the SQLite is just missing a lot of capabilities.

Having the RDBM requirements stated is a good first step though! We can always change it there to state dropped MySQL support :yum:

2 Likes

For reference, the dropping sqlite discussion was last discussed at:

I thought there was also discussion about dropping mysql at some point but couldn’t find it.

Couldn’t find an exact list for Rails 5, but Rails 6 requires:

  • MySQL >= 5.5.8
  • SQLite >= 3.8
  • PostgreSQL >= 9.3

(yes, this will be an issue on EL7)

1 Like

I also recall to see that somewhere but can’t find it. I’d personally be pro dropping sqlite and mysql support. If we decide that way, it will make @Martin_Bacovsky1 life easier with foreman-maintain I believe. It would make patches like https://github.com/theforeman/foreman/pull/5623 easier.

So @lzap, if you open the topic again, you have +1 from me.

The conclusion from the discussion is that we rely on a database to generate metadata (API docs, caches). Currently we use SQLite because that’s available during build. Since this is only for table structures and not actual data, we could drop consider dropping user installations with those databases.

Personally I strongly dislike just using the table structures that are present rather than a static model, but I doubt that’s going to change.

I would be in favor of dropping user support for Sqlite but keep it in the test matrix to ensure asset compilation and Apipie cache generation continue to work inside packaging.

I would love to see an investigation into how we could de-couple these tasks from needing a database connected to entirely drop sqlite and reduce build overhead.

2 Likes

Is it a big change to install postgres shipped with OS in order to generate apipie cache?

We can keep sqlite in jenkins but it’s already bad, since we discourage users to use it in production. But if that helps taking some decision, we can split decision on sqlite and mysql.

For sqlite there are infrastructure implications that needs to be considered.

For mysql, from developer perspective it’s just a burden. Before we say users are using it, I’d like to hear from them if it would be a big problem to start using postgres instead. Of course the migration can be painful, but it’s one-time effort. While maintaining the code is continuous effort. The process seems to be working and documented at Foreman :: Migrating Foreman from MySQL to Postgresql.

There would be performance benefits already for users with multiple dynflow executors.

Here’s the short list of what I recall mysql support caused us

I suppose I could find more if it’s not long enough list

It is a big issue. Currently we generate the cache during the build process. That means inside of koji. I don’t think we can run PostgreSQL there. That’s why we suggested to use sqlite during build but don’t support it at runtime.

Agree on dropping everything but pg

1 Like

As it was mentioned dropping support for everything but Pg would make things much easier for foreman-maintain so +1 from me. I like the idea of keeping Sqlite for builds to make the transition easier. Would dropping Sqlite have any performance impact on running the tests during development?

After reading all the comments, my conclusion is:

  • Let’s drop MySQL
  • Later we can drop SQLite

If we want to get rid of MySQL we need to:

  • Announce in a release (1.22) that the next version (1.23) will be the last one supporting MySQL
  • Come up with a good documentation + blog post + tested worflow of migration
  • Release 1.23 with a warning this is the last release
  • After branching 1.23 we just remove all hacks, remove MySQL from installer and CI
  • 1.24 will come out without MySQL
1 Like

Sounds like a good plan to me. I’m not sure if we need formal poll or give it more time, thread has 3 days. Meanwhile the most important question - who will take care of it? :slight_smile: It does not look like too much of work.

I can volunteer to do upgrade testing and coming up with documentation for both 1.22 and 1.23. Please give me :heart: 's if you support this plan. Will need assistance from @tbrisker to do the release notes (new PR?) and if there is a volunteer to do a warning strip in the UI “This is the last version with MySQL support, go here to read more…”

1 Like

I’m afraid we missed the chance to deprecate this in 1.22 which was just released. What this means essentially is that the whole proposed schedule should be shifted by one release.

1 Like

Right, let me create a new thread for better visibility and update the instructions for the new schedule.

Note we can still implement a warning in 1.22.1 that in 1.24 it will be dropped as an additional heads up.