Bug 23914

Summary: Hea - share the DBMS (name and version)
Product: Project Infrastructure Reporter: Martin Renvoize <martin.renvoize>
Component: HeaAssignee: Chris Cormack <chris>
Status: NEW --- QA Contact: Jonathan Druart <jonathan.druart+koha>
Severity: enhancement    
Priority: P5 - low CC: julian.maurice, katrin.fischer, victor
Version: unspecified   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23898
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=23849
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:

Description Martin Renvoize 2019-10-28 17:31:52 UTC
It would be really interesting to collect the sql server versions people are using in HEA.  I'm interested to see what the adoption of MariaDB is and whether we can start adopting more recently introduced features from either MySQL or MariaDB (like CHECK constraints).
Comment 1 Katrin Fischer 2019-10-29 07:08:10 UTC
Doesn't this go against the goal we set for moving towards being database agnostic?
Comment 2 Martin Renvoize 2019-10-29 09:44:43 UTC
(In reply to Katrin Fischer from comment #1)
> Doesn't this go against the goal we set for moving towards being database
> agnostic?

Hmmm, having an idea of adoption certainly doesn't go against it.  Right now I'd just be interested in the statistics.  We're certainly not agnostic at the moment and personally I'm not sure we should be striving for that goal in reality... my experience of such a goal is that you end up falling to such a low common denominator that there's no reason to choose one DB over another because we're not making the most of any particular systems advantages over the others.
Comment 3 Katrin Fischer 2019-10-29 10:02:41 UTC
I think we agreed (coding guidelines etc.) to not use MySQLisms as such... so this would need a broader discussion.
Comment 4 Martin Renvoize 2019-10-29 13:24:45 UTC
Also worth noting, CHECK constraints are not a mysqlism.. they're implemented in Postgres, MySQL, MariaDB, MSSQL to name but a few... the problem comes from how they're defined (or rather removed in this case).
Comment 5 Julian Maurice 2019-10-30 07:59:28 UTC
(In reply to Martin Renvoize from comment #4)
> Also worth noting, CHECK constraints are not a mysqlism.. they're
> implemented in Postgres, MySQL, MariaDB, MSSQL to name but a few... the
> problem comes from how they're defined (or rather removed in this case).

In this case, I think the question should not be "is it a mysqlism ?" but rather "is it supported by the RDBMS we support ?". And CHECK constraints support was added in Mysql 8.0.16 and MariaDB 10.2.1. Previous versions accept the syntax but ignore it.
I think we shouldn't rely on something that is ignored by a good part of RDBMS (if we support Debian oldstable, MySQL version is 5.5 and MariaDB is 10.1)
Comment 6 Martin Renvoize 2019-10-31 08:36:48 UTC
As a side note, as of today, we're moving from only testing against mysql5.5 on Jenkins to testing against mysql5.5 (D8), mariadb10.1 (D9, U18) and mariadb10.3 (D10).

I'm not suggesting we immediately pull the plug on support for other databases... I'm merely suggesting it would be helpful to understand what DB's people are using so we can accurately asses whether the added work to support different DB's is a worthwhile endeavour.

I am very keen to see a steady improvement of our database structure and that includes using SQL features where possible to improve consistency and reliability of the data.. for me, CHECK constraints are a high priority in that mix.
Comment 7 Katrin Fischer 2019-10-31 22:56:05 UTC
(In reply to Martin Renvoize from comment #6)
> As a side note, as of today, we're moving from only testing against mysql5.5
> on Jenkins to testing against mysql5.5 (D8), mariadb10.1 (D9, U18) and
> mariadb10.3 (D10).
> 
> I'm not suggesting we immediately pull the plug on support for other
> databases... I'm merely suggesting it would be helpful to understand what
> DB's people are using so we can accurately asses whether the added work to
> support different DB's is a worthwhile endeavour.
> 
> I am very keen to see a steady improvement of our database structure and
> that includes using SQL features where possible to improve consistency and
> reliability of the data.. for me, CHECK constraints are a high priority in
> that mix.

I am not against having Hea check this, but I think database support/database agnosticism or not (that I thought we had agreed on as a long term goal a long time ago) should not be discussed on bug reports.
Comment 8 Katrin Fischer 2019-10-31 23:03:25 UTC
... meaning I think we as a community agreed on working towards supporting other RDBMS like Postgres and if we want to change direction, we need to have a discussion as a community. But I think it was one of the main selling points of DBIx::Class (https://wiki.koha-community.org/wiki/RFC_for_using_DBIx_Class_in_Koha). And now I will stop derailing this bug.
Comment 9 Katrin Fischer 2020-03-15 15:06:32 UTC
Should we restart this? With all the problems we see with MySQL versions having better data on what people use would be good (which was your initial goal, I know!)
Comment 10 Martin Renvoize 2020-03-26 16:25:37 UTC
I certainly feel this would still be a helpful addition.
Comment 11 Victor Grousset/tuxayo 2020-04-27 00:21:06 UTC
Where is the right place to implement this?

There? https://gitlab.com/tuxayo/Koha/-/blob/b50d891c099d0f21f2528c5a6d5bbb586cf1612e/C4/UsageStats.pm#L81

I just checked on my dev env and no syspref contains even the DB name.

After looking at what about.pl does we can get it via
C4::Context::get_versions(){'mysqlVersion'};

I can submit a first patch based on that.
@Chris, you are assigned is that one your immediate to-do list?
Comment 12 Jonathan Druart 2020-04-27 09:48:31 UTC
I am not sure everybody will agree on sharing the DBMS (security issues), and it would be a shame to lose Hea adopters because of that.
Comment 13 Victor Grousset/tuxayo 2020-04-27 11:17:03 UTC
Indeed, if there is no trust on the Hea server, that's a way of sharing not being up to date on the system level. (when that's the case) Which one might not want by default.

On the other hand, isn't sharing the full version of Koha already more of a potential security issue? It doesn't directly speak about the system.
But can a link be rightly guessed for a number of cases? By monitoring if the Koha if evolving on a regular basis. I don't know how much of that makes sense.[1] If there another more reliable way to link the two things?

And the version of Koha can directly tell if vulnerable to remote code execution on an app listening to internet. As opposed to the DBMS.

[1] Hum, I don't think it does at least in my example. So that would be an additional valuable fingerprinting information.

Is a solution to share only the major and minor version of DBMS and the name?
Like
- MariaDB 10.4
- MySQL 5.7

Then no issues to do it by default right?

Hum, that can still be an issue when the version is not maintained anymore since a long time. Are these remaining cases relevant? Is Koha even updated at all on those systems?

To have an idea of the time to have an unmaintained DBMS version:
https://en.wikipedia.org/wiki/MariaDB#Versioning
https://en.wikipedia.org/wiki/MySQL#Release_history

Actually, that just mean having Debian 8 or Ubuntu 16.04 which unfortunately can still be the case with a regularly updated Koha. I know for sure 18.11 still work on those OSes. After, I'm not sure if Perl or DBMS version allow Koha > 18.11 to work.

Argh, that a complex topic!!! >_<
Comment 14 Jonathan Druart 2020-04-27 11:32:45 UTC
(In reply to Victor Grousset/tuxayo from comment #13)
> On the other hand, isn't sharing the full version of Koha already more of a
> potential security issue?

It's in the html source so you can consider it public already.
Comment 15 Victor Grousset/tuxayo 2020-04-27 19:27:04 UTC
(In reply to Jonathan Druart from comment #14)
> (In reply to Victor Grousset/tuxayo from comment #13)
> > On the other hand, isn't sharing the full version of Koha already more of a
> > potential security issue?
> 
> It's in the html source so you can consider it public already.

That was the point. Well sort off. I meant that it's already in Hea by default which means some existing trust on the Hea server.
But yes it's even available to whoever has access to the OPAC or intranet.
Comment 16 Katrin Fischer 2020-04-27 21:57:52 UTC
Let's not overthink this. What about increasing trust by making this an opt-in feature?

- Share Koha configuration information
- Share system configuration / versions etc.

While Hea indeed has some trust, it could always be hacked. We should encourage users to share data, but always give them a choice. I am not sure, but I think information is not stored completely anonymously in the HEA database? (we do store libary names etc.)?
Comment 17 Victor Grousset/tuxayo 2020-04-27 23:35:04 UTC
> Let's not overthink this. What about increasing trust by making this an opt-in feature?

Okay, so same as library name and location? On the same screen? Can we assume enough that the Koha admin will have the right knowledge about whether to share or not system version data?

> I am not sure, but I think information is not stored completely anonymously in the HEA database? (we do store libary names etc.)?

Are the whole reports stored? Or is the report content dispatched in the various data aggregates on reception? Making it not possible after reception to associate the system version data with library name. Which means a data leak won't really expose any additional[1] data other than the aggregates on the websites.

[1] and the random ids which don't have any value IIUC