[Opendnssec-user] ods-ksmutil key ds-seen fails

[Berry A.W. van Halderen]

On 07/23/2017 10:21 PM, Emil Natan wrote:
> Hello,
>
> opendnssec version 1.4.14 with OpenHSM 2.2.0.
>
> OpenDNSSEC manages few zones and it seems it all works well, but:
>
> # ods-ksmutil key ds-seen -z 1715.test.net <http://1715.test.net> -k 18199
> ERROR: error executing SQL - Expression #3 of SELECT list is not in
> GROUP BY clause and contains nonaggregated column 'k.algorithm' which
> is not functionally dependent on columns in GROUP BY clause; this is
> incompatible with sql_mode=only_full_group_by
> Error: failed to count keys
>

I guess you have recently upgraded MySQL/MariaDB and/or its global
configuration.  Your *nix distribution package have
apparently configured the enforced syntax to be more strict then used to
be.  That'll break backwards compatibility, and for that reason I'm not
agreeing with their policy in this matter.
In general, they're correct in the statement that all columns in selects
should be aggregates or in group-by clauses, however enforcing this
brings out far more problems while most queries do not have bugs here
(although incorrect, many SQL engines
accept this).  It should have been a warning.

It is far safer to modify your MySQL configration to do away with this
error.  The following thread describes the issue:
https://stackoverflow.com/questions/23921117/disable-only-full-group-by

The setting you are looking for is probably not directly in /etc/my.cnf,
but in one of the /etc/my.cnf,d/* files or something similar.
But this all depends too much on your distribution. You would be looking
for a setting named "sql_mode" in in the [mysqld] section.
I think you need to remove the ONLY_FULL_GROUP_BY item from that list
and restart the mysql daemon/service.

\Berry