Doesn't this go against the goal we set for moving towards being database agnostic?

(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.

I think we agreed (coding guidelines etc.) to not use MySQLisms as such... so this would need a broader discussion.

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 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)

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.

(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.

... 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.

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!)

I certainly feel this would still be a helpful addition.

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?

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.

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!!! >_<

(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.

(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.

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.)?

> 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