MariaDB -> MySQL 8 needed

MathematicalMedicine / diver-issues

Semipublic tracking of issues for the DIVER front end

0 stars 0 forks source link

MariaDB -> MySQL 8 needed #246

Open Viqsi opened 4 months ago

Viqsi commented 4 months ago

ISI does not have MariaDB available, so we have to port our routines to MySQL 8.

The majority of the code should be fine. Should be. But we know there are subtle incompatibilities - especially in JSON handling - so this could get a little "interesting".

Related question - do we want to try to support both? I could see this leading to much wailing and gnashing of teeth and loss of hair and so I'm hesitant.

WValenti commented 4 months ago

If it is dynamically conditional, then it's easy to check for and set a value to find out what we're dealing with. I'd prefer this so we don't have to uproot our dev environments. Especially the one for DMD, which would be a nightmare to redo. We should also show the SQL type/version along with the other on-page versions.

Viqsi commented 3 months ago

The differences between MariaDB 10.11 and MySQL 8 are pretty extensive: https://mariadb.com/kb/en/incompatibilities-and-feature-differences-between-mariadb-10-11-and-mysql-8/

Most notable concerns are:

JSON functions and JSON data type (mariadb LONGTEXT vs mysql binary JSON). That's the really really big one (mostly on me), and it's not easily conditionaled away becaues they have fundamentally different approaches and both claim theirs is faster, so we'd be "worst of both worlds"ing it.
Temporal data tables. Another notable, mostly in @WValenti's court, because it's of strong interest w/r/t implementation of longitudinal study data storage.

Other than that it's mostly small subtle things to trip over, some of which we've made a point of avoiding previously (such as "CREATE OR REPLACE TABLE").

Viqsi commented 3 months ago

It is possible because of the above that we might have to insist on MariaDB. That would, of course, lead to all sorts of Politics fun.

Viqsi commented 1 month ago

It might be done. I have yet to get a working MySQL 8 server operating well enough to let me actually check that, though. What I do know is that the changes I made that should allow for MySQL 8 compatibility didn't break MariaDB. So there's that. It's implemented but untested.

Viqsi commented 3 weeks ago

Per discussion/agreement on Skype, @WValenti is working on setting up a MySQL 8 server on his own workstation to test this.

Viqsi commented 3 weeks ago

Bonus complication: MySQL 9 was released in July!

ISI last told us they were on MySQL 8 during a meeting in August, though, so maybe they haven't updated yet. Maybe. Possibly. We don't know for certain. (aargh)