[dev.icinga.com #3330] wrong selects not working with other databases than mysql

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/3330

Created by sanchopanza on 2012-10-23 16:01:09 +00:00

Assignee: (none)
Status: Resolved (closed on 2012-12-19 15:28:42 +00:00)
Target Version: 1.8.2
Last Update: 2013-02-11 19:18:10 +00:00 (in Redmine)

Icinga Version: 1.8.0
Icinga Web Version: 1.8.0
IDO Version: 1.8.0
OS Version: Linux Redhat 5 64-bit
DB Type: PostgreSQL
DB Version: 9.1
Browser Version: any

---

when you expand the submenu for a host, hostgroup, servicegroup and want to jump to a hostgroup, servicegroup etc. you get a blank screen. The reason for this is a sql statement with a wrong "group by". For reasons I do not understand mysql accepts this statement, but to my knowledge no other database does. Since we run icinga on postgres this is quite a problem.

This is an example for these statements:

SELECT DISTINCT i.icon_image AS i__0,
i2.instance_name AS i2__1,
i.host_object_id AS i__2,
i4.name1 AS i4__3,
i.alias AS i__4,
i.display_name AS i__5,
i3.current_state AS i3__6,
(CASE WHEN i3.last_state_change<='1970-01-01 00:00:00' THEN i5.program_start_time ELSE i3.last_state_change END) AS i__7,
i3.last_check AS i3__8,
i3.next_check AS i3_9, i5.program_start_time AS i5_10,
i3.output AS i3_11, i3.current_check_attempt AS i3_12,
i3.max_check_attempts AS i3_13, i3.process_performance_data AS i3_14,
i3.max_check_attempts AS i3_15, i3.perfdata AS i3_16,
i2.instance_id AS i2_17, i.host_id AS i18, i.action_url AS i_19,
i.notes AS i_20, i.notes_url AS i21, (i3.has_been_checked-1)*-1 AS i_22,
i3.notifications_enabled AS i3_23, i3.problem_has_been_acknowledged AS i3_24,
i3.scheduled_downtime_depth AS i3__25,
i3.has_been_checked AS i3__26,
i8.name1 AS i8__27
FROM icinga_hosts i
LEFT JOIN icinga_instances i2 ON i.instance_id = i2.instance_id
LEFT JOIN icinga_hoststatus i3 ON i.host_object_id = i3.host_object_id
LEFT JOIN icinga_objects i4 ON i.host_object_id = i4.object_id
LEFT JOIN icinga_programstatus i5 ON i2.instance_id = i5.instance_id
INNER JOIN icinga_hostgroup_members i7 ON (i.host_object_id = i7.host_object_id)
INNER JOIN icinga_hostgroups i6 ON i6.hostgroup_id = i7.hostgroup_id
INNER JOIN icinga_objects i8 ON i6.hostgroup_object_id = i8.object_id
WHERE (i.config_type = '1' AND i8.name1 = 'hg-aix-server'
AND i3.current_state = '0')
GROUP BY i.host_object_id
ORDER BY i.host_object_id ASC LIMIT 250

Attachments

• icinga-web-groupby.patch abraxas - 2012-11-07 19:37:09 +00:00
• icinga-web-groupby.patch abraxas - 2012-11-07 20:25:08 +00:00

[icinga-migration]

Updated by mfriedrich on 2012-10-24 16:34:57 +00:00

• Target Version changed from 1.8 to 1.8.1

[icinga-migration]

Updated by abraxas on 2012-11-07 19:37:09 +00:00

• File added icinga-web-groupby.patch

Attached is a patch that eliminated these "flawed" groupby statements entirely that works good for our setup.

We couldn't really figure out why these group by statements were in place at all.
If there is a reason that these group by statements are necessary for a special purpose removing them might break that purpose. However icinga-web runs fine with postgresql backend for us.

[icinga-migration]

Updated by abraxas on 2012-11-07 20:25:08 +00:00

• File added icinga-web-groupby.patch

[icinga-migration]

Updated by mfriedrich on 2012-11-29 16:22:22 +00:00

• Target Version changed from 1.8.1 to 1.8.2

[icinga-migration]

Updated by jmosshammer on 2012-12-19 13:35:18 +00:00

Those group by statements are there to avoid duplicated results when a host is in more than one hostgroups.
Could you provide the sql error that you receive, please?

[icinga-migration]

Updated by jmosshammer on 2012-12-19 15:28:42 +00:00

• Status changed from New to Resolved

changed it back to the old behaviour (causing duplicates) in my jmosshammer/performance branch.
Problem is that postgres doesn't support sql 2003's group by distinct (http://www.postgresql.org/docs/9.2/static/unsupported-features-sql-standard.html). We'll have to use distinct of here, which is non-standard sql and not supported by doctrine.

[icinga-migration]

Updated by mfrosch on 2013-02-11 19:18:10 +00:00

• Done % changed from 0 to 100