Skip to content
This repository has been archived by the owner. It is now read-only.

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

Closed
icinga-migration opened this issue Oct 23, 2012 · 7 comments

Comments

Projects
None yet
1 participant
@icinga-migration
Copy link
Member

commented Oct 23, 2012

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

This comment has been minimized.

Copy link
Member Author

commented Oct 24, 2012

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

  • Target Version changed from 1.8 to 1.8.1
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Nov 7, 2012

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

This comment has been minimized.

Copy link
Member Author

commented Nov 7, 2012

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

  • File added icinga-web-groupby.patch
@icinga-migration

This comment has been minimized.

Copy link
Member Author

commented Nov 29, 2012

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

This comment has been minimized.

Copy link
Member Author

commented Dec 19, 2012

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

This comment has been minimized.

Copy link
Member Author

commented Dec 19, 2012

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

This comment has been minimized.

Copy link
Member Author

commented Feb 11, 2013

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

  • Done % changed from 0 to 100

@icinga-migration icinga-migration added this to the 1.8.2 milestone Jan 17, 2017

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.