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

[Emil Natan]

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

Any other command I tried succeeds (listing keys, zsk rollover,
adding/deleting zones etc). I tried with all domains managed on that
signer, using different policies with the same result.
Any help highly appreciated.

Emil

[Emil Natan]

Hi Berry,

Thank you for your response. The problem is solved, here are few details.

The problem appeared on a fresh install based on Ubuntu 16.04 and MySQL
version 5.7.19 installed from package. ONLY_FULL_GROUP_BY is not set in any
of the configuration files, but it's hardcoded somewhere.
The solution that worked for me which I found here (
https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html)
was:

Create file /etc/mysql/conf.d/disable_strict_mode.cnf and add the following
2 lines:

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then restart the MySQL service:

# service mysql restart

Hope that helps others.
Thank you.

Emil

Post by Emil Natan
Hello,
opendnssec version 1.4.14 with OpenHSM 2.2.0.
# ods-ksmutil key ds-seen -z 1715.test.net <http://1715.test.net> -k

18199

Post by Emil Natan
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
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
_______________________________________________
Opendnssec-user mailing list
https://lists.opendnssec.org/mailman/listinfo/opendnssec-user