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

[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

On Thu, Jul 27, 2017 at 3:18 AM, Berry A.W. van Halderen <berry at nlnetlabs.nl
> wrote:

> 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
>
>
> _______________________________________________
> Opendnssec-user mailing list
> Opendnssec-user at lists.opendnssec.org
> https://lists.opendnssec.org/mailman/listinfo/opendnssec-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opendnssec.org/pipermail/opendnssec-user/attachments/20170731/d82f4b00/attachment.htm>