Should Sakai Evolve Towards MySQL 8.0 or Maria DB 10?

[Charles Severance]

Hi all,

With the recent release of Sakai 21.0 (Thanks to all involved). We need to do some thinking about our direction forward w.r.t. how we approach our MySQL support in the Sakai community.

We have been supporting MySQL and Oracle since 2002 (19 years). In the old days, MySQL was the 100% open alternative for open source applications. But then in 2010, Oracle gained control of MySQL by buying Sun Microsystems[1]. At that point in time MariaDB was forked from the last non-Oracle version of MySQL. Some in the Sakai Community experimented with MariaDB from time to time - in the early 2010's if things went wonky we ran back to MySQL.

Oracle has released MySQL 5.6 and 5.7 and MariaDB kept pace[2] - even to the point of binary interoperability. We along with the rest of the open source world found this equivalence comforting and felt it was OK to just keep using MySQL and let the "other folks" use MariaDB.

But support for MySQL 5.6 just expired (February 2021) and support for MySQL 5.7 expires in October 2023. So we have just over two years...

The version of MySQL after 5.7 is MySQL 8.0. MySQL 8 is the first version that is heavily influenced by Oracle and it feels to me like MySQL 8 and MariaDB may begin to diverge. Hibernate (as of 5.3) supports the org.hibernate.dialect.MariaDBDialect separately from its MySQL dialects. Hibernate also supports the org.hibernate.dialect.MySQL8Dialect.

The reasonably high likelyhood is that Sakai will be able to simultaneously support the various versions of MySQL and MariaDB without too much effort going forward. Half of the SQL in Sakai is hand-constructed and half of the SQL is constructed by Hibernate. Our hand-constructed SQL does not make use of intricate features of MySQL so it is likely to keep working in MySQL 8 and MariaDB 10. And you can pick your Hibernate dialect and support all the databases.

So the question is less about *not* supporting MySQL 8 that it is about deciding what our "preferred" / "first choice" database will be. For this preferred MySQL compatible database we can encourage developers to test using the preferred database and then run our nightly servers using that preferred database.

For example for Sakai 21 on our nightly page[3], we have eight MySQL 5.7 servers and one Oracle server.

For Sakai 22, we could keep things the way they are or switch to any the following:

* Run eight MariaDB 10 servers, 1 MySQL 5.7 server, and 1 Oracle Server
* Run eight MySQL 5.7 servers, 1 MariaDB server, 1 MySQL 8 server, and 1 Oracle Server
* Run eight MariaDB 10 servers, 1 MySQL 5.7 server, 1 MySQL 8 server, and 1 Oracle Server
* Run eight MySQL 8 servers, 1 MariaDB server, 1 MySQL 5.7 server, and 1 Oracle Server

You get the picture. By leaning towards either MySQL 8 or MariaDB 10 - we are signaling our "preference" / "first choice".

We of course would fix problems that emerged in any of those databases. We would not want to break Sakai and MySQL 8 just to do something cool in MariaDB 10 or vice versa. We need to write conservative SQL and accept fixes if some hand-written SQL breaks 5.7, 8.0 or MariaDB 10.

The question is where the majority of us are going to go so we can stick together and protect each other's flanks by using a common approach wherever practical.

We can delay this decision until we release Sakai 22 - but the decision is easier and safer now. We get a year to experiment with the different databases and then based on what we learn this year - we can make a more informed decision next year. Also by next year we might get some signals from the market and other open source projects as to where they will be going w.r.t. the "MySQL 8 - to be or not to be" question.

My Opinion

At a minimum, I would like a trunk master running on MySQL 5.7, MySQL 8.0, MariaDB 10, and Oracle. I will run my Smoker process against all four every night, to probe for really bad regressions. If we could somehow make them have identical sites and data - I could compare them click for click with smoker.

Once we chat a bit about this on the lists, I will do a survey to poll the community.

Comments welcome.

/Chuck

References

[1] https://en.wikipedia.org/wiki/MySQL
[2] https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/
[3] http://nightly2.sakaiproject.org/

[Earle Nietzel]

Thanks for starting this convo Dr Chuck,

I do think it's time to start more _formallly_ adding MariaDB to the mix, however I think our database setup on nightly should reflect that which is being used by our users meaning:

For example:

80% of sakai installs use MySQL (where 20% of installs make up 10% Oracle and 10% MariaDB).

For the other DB variants that make up the minority lets simply have a version of master representing that DB.

So an infrastructure might look like this:

master - 1 mysql / 1 mariadb / 1 oracle
experimental master - 1 mysql
21.x - 1 mysql
20.x -1 mysql
19.x - 1 mysql

Also the sql ratio according to tables is better represented as: ~300 total tables of which 75% are Hibernate managed and the other 25% represent SqlService and Spring jdbc template

-earle

--
You received this message because you are subscribed to the Google Groups "Sakai Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-user+...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-user/126B83E4-4047-4958-A18F-06F674997639%40umich.edu.

[Matthew Jones]

I don't know if that accurately represents the community or not. I feel many use AWS Aurora, another fork that is not compatible with MySQL 8, and only goes as far as 5.7, similar to MariaDB. So do we need an Aurora instance too? Probably not as much as if we had MariaDB. 

I like the idea of going MariaDB first as it's the most open, 5.7 compatible and it's also the easiest for localhost. Though MySQL 8 isn't too big of a problem to continue supporting as long as we don't do much MySQL 8 specific stuff, which we likely won't do. If we're running MariaDB, I don't see any point to leaving a separate MySQL 5.7 around, especially since it's going to be EOL in 2023.

I think the hard decision about supporting multiple databases is if we want to take advantage of any special features of a database, we can't while still supporting the others. For instance MariaDB supports a number of storage engines that aren't available on the other databases and features like system versioned tables, that could be "nice to have'' if we were able to use them. We're basically stuck forever at the lowest common denominator, which is going to be MySQL 5.7 in this case. 

As an aside, we're also running Oracle 12c which is close to being unsupported, looks like July 2022. We can go up to 19c (it doesn't *look* like 21c is available yet) however it seems like it requires us to go to a tb.t3.small instance instead of a db.t3.micro which will double our costs to keep running that.

We have been running these as RDS instances, so each additional one is going to increase the costs. (Approx $200/yr each) And if we run additional instances of Sakai that might also require us to get a larger server. There's potential ways to save money and run more, like using containers and spot instances, but the human cost of setting up that infrastructure will possibly negate the savings.

I'd say in an ideal setting if we had a little more QA and time spent on nightly we'd have:

trunk AND trunk-1 running on Oracle, MySQL (latest ver) AND MariaDB (latest ver) (6 instances)

trunk-2, trunk-3 only on MariaDB (2 instances)

(maybe trunk experimental on just MariaDB) 

Then we'd also do full smoke tests before releasing trunk-1 across all 3 databases before release (at least for tools considered major), since that only took about a week with current QA effort.

Also, I noticed the reserved instances just expired a few weeks ago, so making a decision on this in the short term would be beneficial cost-wise, at least what to keep around. 

To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-user/CAMddi324T2_-uuqtLy0j6-tseVopLn1si1YOwzy4nW744cpYkA%40mail.gmail.com.

[Sam Ottenhoff]

Longsight uses Aurora so our QA is always going to make sure that Aurora works well with Sakai. So I would recommend focusing on MySQL 8 and MariaDB 10. I agree with Matt that MySQL 5.7 can be dropped as MariaDB 10 will cover it plus many devs will continue using it locally because of default OS installs.

You received this message because you are subscribed to the Google Groups "Sakai Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/sakai-dev/CACqbowvU1-oiq3PmDUp8mL%3Dry8dWerz4k8msVzwW%3D1YKWjuQ8g%40mail.gmail.com.

[Earle Nietzel]

Just to clarify I wasn't saying that is the actual representation of our user base! It was just a hypothetical and what our db support might look like in relation to that!!!

-earle

[Earle Nietzel]

Another thing to be thinking about with all these instances of Sakai is whether or not we have the resources to test on them.

I am not in favor of expanding instances if we don't have resources to even test on them. This is the reason I suggest having only master instances for all of the databases we support and other instances are the db that is used most by users of Sakai.

-earle