search

Icinga / icingaweb2

A lightweight and extensible web interface to keep an eye on your environment. Analyse problems and act on them.
https://icinga.com/get-started/
GNU General Public License v2.0
809 stars 282 forks source link

Detailed Service view shows SQL grouping error SQLSTATE[42803] #3827

Open martinfaust opened 5 years ago

martinfaust commented 5 years ago

Describe the bug

After upgrading icinga2 to 2.10 and postgres from 9.4 to 9.6 the detailed service view shows the SQL Error:

SQLSTATE[42803]: Grouping error: 7 ERROR: column "sgo.name1" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT sgo.name1 AS servicegroup_name, sg.alias AS servicegr... ^, query was: SELECT sgo.name1 AS servicegroup_name, sg.alias AS servicegroup_alias FROM icinga_objects AS sgo INNER JOIN icinga_servicegroups AS sg ON sg.servicegroup_object_id = sgo.object_id AND sgo.objecttype_id = 4 AND sgo.is_active = 1 LEFT JOIN icinga_servicegroup_members AS sgm ON sgm.servicegroup_id = sg.servicegroup_id LEFT JOIN icinga_objects AS so ON so.object_id = sgm.service_object_id AND so.objecttype_id = 2 AND so.is_active = 1 WHERE (so.name1 = 'vs-deploy.XXXXXXX.yyy' AND so.name2 = 'deploy.XXXXXXX.zzz') GROUP BY sgo.object_id, sg.servicegroup_id ORDER BY LOWER(sg.alias) ASC

To Reproduce

I can privatly provide a postgres database dump.

Your Environment

Copyright (c) 2012-2019 Icinga GmbH (https://icinga.com/) License GPLv2+: GNU GPL version 2 or later http://gnu.org/licenses/gpl2.html This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law.

System information: Platform: Debian GNU/Linux Platform version: 9 (stretch) Kernel: Linux Kernel version: 4.9.0-9-amd64 Architecture: x86_64

Build information: Compiler: GNU 6.3.0 Build host: cb654124b660

Application information:

General paths: Config directory: /etc/icinga2 Data directory: /var/lib/icinga2 Log directory: /var/log/icinga2 Cache directory: /var/cache/icinga2 Spool directory: /var/spool/icinga2 Run directory: /run/icinga2

Old paths (deprecated): Installation root: /usr Sysconf directory: /etc Run directory (base): /run Local state directory: /var

Internal paths: Package data directory: /usr/share/icinga2 State path: /var/lib/icinga2/icinga2.state Modified attributes path: /var/lib/icinga2/modified-attributes.conf Objects path: /var/cache/icinga2/icinga2.debug Vars path: /var/cache/icinga2/icinga2.vars PID path: /run/icinga2/icinga2.pid`

Additional context

lippserd commented 5 years ago

Hi,

Thanks for the report. To be honest, I don't know why the query fails. This error indicates that we're selecting a column which is not covered by any of the group by columns. But we're grouping by the two primary keys of the two tables we're selecting from. Strange. Could you please share the output of \d+ icinga_objects.

Best, Eric

lippserd commented 5 years ago

@martinfaust Your help would be much appreciated.

martinfaust commented 5 years ago

Hi Eric, sorry for the delay. I've only the SQL Dump laying around, hope that helps:

CREATE TABLE public.icinga_objects ( object_id bigint NOT NULL, instance_id bigint DEFAULT 0, objecttype_id bigint DEFAULT 0, name1 text, name2 text, is_active integer DEFAULT 0 );

lippserd commented 5 years ago

Hi,

Unfortunately not. Could you run \d+ icinga_objects in your PostgreSQL Icinga 2 database?

Best, Eric

nilmerg commented 5 years ago

Hi Martin,

without the information requested by @lippserd we can't help you more, I'm afraid.

If you can please report back. Though, in the meantime I'll close the issue. We'll re-open it again once we heard from you again.

Best, Johannes

NetworkTest commented 3 years ago

Years go by...

I'm seeing the same issue on FreeBSD 13.0-RELEASE-p3 with postgresql 12.7 and icinga2 2.12, and icinga2web-php74 2.8.2.1.

Here's the output you requested. Please let me know if you need other info.

icinga=# \d+ icinga_objects Table "public.icinga_objects" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------------+---------+-----------+----------+---------------------------------------------------+----------+--------------+------------- object_id | bigint | | not null | nextval('icinga_objects_object_id_seq'::regclass) | plain | | instance_id | bigint | | | 0 | plain | | objecttype_id | bigint | | | 0 | plain | | name1 | text | | | | extended | | name2 | text | | | | extended | | is_active | integer | | | 0 | plain | | Indexes: "icinga_objects_i" btree (objecttype_id, name1, name2) "objects_inst_id_idx" btree (instance_id) "objects_name1_idx" btree (name1) "objects_name2_idx" btree (name2) "objects_objtype_id_idx" btree (objecttype_id) "sla_idx_obj" btree (objecttype_id, is_active, name1) Access method: heap