search

Icinga / icingaweb2-module-cube

Drill-down view for Icinga web 2 based on custom variables
GNU General Public License v2.0
45 stars 12 forks source link

Grouping error: 7 ERROR: column "cv.customvariablestatus_id" #54

Closed mtdeguzis closed 4 years ago

mtdeguzis commented 4 years ago

Describe the bug

When enabling v1.1.0:

 Grouping error: 7 ERROR: column "cv.customvariablestatus_id" must appear in the GROUP BY clause or be used in an aggregate function

When using master, I get as far as this: image image

SQLSTATE[42883]: Undefined function: 7 ERROR: function rollup(text) does not exist
LINE 4: ...ROUP BY "c_cluster_env".varvalue) AS sub GROUP BY ROLLUP ("c...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts., query was: SELECT rollup."cluster_env", rollup."hosts_cnt", rollup."hosts_down", rollup."hosts_unhandled_down", rollup."hosts_unreachable", rollup."hosts_unhandled_unreachable" FROM (SELECT sub."cluster_env", SUM(hosts_cnt) AS hosts_cnt, SUM(hosts_down) AS hosts_down, SUM(hosts_unhandled_down) AS hosts_unhandled_down, SUM(hosts_unreachable) AS hosts_unreachable, SUM(hosts_unhandled_unreachable) AS hosts_unhandled_unreachable FROM (SELECT "c_cluster_env"."varvalue" AS "cluster_env", COUNT(*) AS "hosts_cnt", SUM(CASE WHEN hs.current_state = 1 THEN 1 ELSE 0 END) AS "hosts_down", SUM(CASE WHEN hs.current_state = 1 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS "hosts_unhandled_down", SUM(CASE WHEN hs.current_state = 2 THEN 1 ELSE 0 END) AS "hosts_unreachable", SUM(CASE WHEN hs.current_state = 2 AND hs.problem_has_been_acknowledged = 0 AND hs.scheduled_downtime_depth = 0 THEN 1 ELSE 0 END) AS "hosts_unhandled_unreachable" FROM icinga_objects AS ho
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id
LEFT JOIN icinga_customvariablestatus AS "c_cluster_env" ON "c_cluster_env".varname = 'cluster_env' AND "c_cluster_env".object_id = ho.object_id GROUP BY "c_cluster_env".varvalue) AS sub GROUP BY ROLLUP ("cluster_env")) AS rollup ORDER BY (rollup."cluster_env" IS NOT NULL) ASC, rollup."cluster_env" ASC, (rollup."hosts_cnt" IS NOT NULL) ASC, rollup."hosts_cnt" ASC, (rollup."hosts_down" IS NOT NULL) ASC, rollup."hosts_down" ASC, (rollup."hosts_unhandled_down" IS NOT NULL) ASC, rollup."hosts_unhandled_down" ASC, (rollup."hosts_unreachable" IS NOT NULL) ASC, rollup."hosts_unreachable" ASC, (rollup."hosts_unhandled_unreachable" IS NOT NULL) ASC, rollup."hosts_unhandled_unreachable" ASC

A clear and concise description of what the issue is.

To Reproduce

Provide a link to a live example, or an unambiguous set of steps to reproduce this issue. Include configuration, logs, etc. to reproduce, if relevant.

  1. Enable module on the below environment.
  2. Try to open Reporting (v1.1.0)> Cube or create a dimension (master)

Expected behavior

Module should open fine.

Screenshots

Your Environment

Include as many relevant details about the environment you experienced the problem in

Additional context

Using PostgreSQL 9.2.24 backend.

mtdeguzis commented 4 years ago

Ah.. I see from a prior ticket that this requires 9.5+

ROLLUP requires pgsql 9.5 or above.

mtdeguzis commented 4 years ago

Upgraded to PostgreSQL 10 and I still see this on v1.1.0. 

cd /usr/share/icingaweb2/modules/
git checkout v1.1.0

# Local
$ scl enable rh-postgresql10 "psql --version"
psql (PostgreSQL) 10.6

# Remove instance used:
$ scl enable rh-postgresql10  "psql -h ${DB_HOST} -U ${DB_USER}"
psql (10.6)
Type "help" for help.

postgres=# 

$ rpm -qa | grep postgresql
rh-postgresql10-postgresql-10.6-1.el7.x86_64
rh-postgresql10-postgresql-libs-10.6-1.el7.x86_64
postgresql-libs-9.2.24-2.el7_7.x86_64
rh-postgresql10-runtime-3.1-1.el7.x86_64
rh-postgresql10-postgresql-server-10.6-1.el7.x86_64

Icingaweb2 is functioning just fine. Am I doing something wrong?

mtdeguzis commented 4 years ago

This does work on master it seems. Is there any chance of a branch or tag I can pin to rather than master?

image

nbuchwitz commented 4 years ago

This was fixed with d80a251.

@lippserd is there anything which speaks against a release with the current state?

lippserd commented 4 years ago

This was fixed with d80a251.

@lippserd is there anything which speaks against a release with the current state?

@nbuchwitz Only the sorting issue because I'm quite sure that we don't ORDER BY anywhere. But anyway, this would be an easy fix and we should be good for a release during the next two weeks.

lippserd commented 4 years ago

I'll close here.

Just for the records, this duplicates #46.

Though, thanks for the report @mtdeguzis !