[dev.icinga.com #2978] Postgresql: Datetime field overflow on host and servicestatus cronks

[icinga-migration]

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

Created by mfrosch on 2012-08-13 13:24:24 +00:00

Assignee: (none)
Status: Resolved (closed on 2012-08-13 14:54:08 +00:00)
Target Version: (none)
Last Update: 2012-08-13 14:54:08 +00:00 (in Redmine)

Icinga Version: 1.7.1-2 (Debian)
Icinga Web Version: 1.7.2 (Debian package devel)
IDO Version: 1.7.1-2 (Debian)
OS Version: Debian sid
DB Type: PostgreSQL
DB Version: 9.1.4-3
Browser Version: Google Chrome dev

---

I have some sql errors with cronks that use the hoststatus or servicestatus table.

Here are 2 examples:
http://mf-deb-sid/icinga-web/modules/cronks/viewproc/icinga-service-template/json

SQLSTATE[22008]: Datetime field overflow: 7 FEHLER: Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »0000-00-00 00:00:00« LINE 1: ..._state AS i3__10, (CASE WHEN i3.last_state_change='0000-00-0... ^. Failing Query: "SELECT i.icon_image AS i__0, i2.instance_name AS i2__1, i4.host_object_id AS i4__2, i.service_object_id AS i__3, i5.name1 AS i5__4, i4.alias AS i4__5, i4.display_name AS i4__6, i6.name2 AS i6__7, i.display_name AS i__8, i3.process_performance_data AS i3__9, i3.current_state AS i3__10, (CASE WHEN i3.last_state_change='0000-00-00 00:00:00' THEN i7.program_start_time ELSE i3.last_state_change END) AS i__11, i7.program_start_time AS i7__12, i3.last_check AS i3__13, i3.next_check AS i3__14, i3.output AS i3__15, i3.current_check_attempt AS i3__16, i3.max_check_attempts AS i3__17, i2.instance_id AS i2__18, i.service_id AS i__19, i3.notifications_enabled AS i3__20, i3.problem_has_been_acknowledged AS i3__21, i3.scheduled_downtime_depth AS i3__22, i3.has_been_checked AS i3__23 FROM icinga_services i INNER JOIN icinga_instances i2 ON i.instance_id = i2.instance_id INNER JOIN icinga_servicestatus i3 ON i.service_object_id = i3.service_object_id INNER JOIN icinga_hosts i4 ON i.host_object_id = i4.host_object_id INNER JOIN icinga_objects i5 ON i4.host_object_id = i5.object_id INNER JOIN icinga_objects i6 ON i.service_object_id = i6.object_id INNER JOIN icinga_programstatus i7 ON i2.instance_id = i7.instance_id WHERE (i.config_type = '1') LIMIT 30"

http://mf-deb-sid/icinga-web/modules/cronks/viewproc/icinga-host-template/json

SQLSTATE[22008]: Datetime field overflow: 7 FEHLER: Datum/Zeit-Feldwert ist außerhalb des gültigen Bereichs: »0000-00-00 00:00:00« LINE 1: ...t_state AS i3__6, (CASE WHEN i3.last_state_change='0000-00-0... ^. Failing Query: "SELECT 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='0000-00-00 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, i2.instance_id AS i2__16, i.host_id AS i__17, (i3.has_been_checked-1)*-1 AS i__18, i3.notifications_enabled AS i3__19, i3.problem_has_been_acknowledged AS i3__20, i3.scheduled_downtime_depth AS i3__21, i3.has_been_checked AS i3__22 FROM icinga_hosts i INNER JOIN icinga_instances i2 ON i.instance_id = i2.instance_id INNER JOIN icinga_hoststatus i3 ON i.host_object_id = i3.host_object_id INNER JOIN icinga_objects i4 ON i.host_object_id = i4.object_id INNER JOIN icinga_programstatus i5 ON i2.instance_id = i5.instance_id WHERE (i.config_type = '1') LIMIT 30"

Changesets

2012-08-13 14:51:20 +00:00 by mhein 1a5552c

Postgresql fix - patch applied - thanks lazyfrosch (fixes #2978)

2012-08-13 14:52:12 +00:00 by mhein 7940ba4

Postgresql fix - patch applied - thanks lazyfrosch (fixes #2978)

[icinga-migration]

Updated by mfrosch on 2012-08-13 14:28:34 +00:00

This bug is introduced by the changes in #2736

Commits:
r1.7 ae4fb8e
master 2faab91

Problem: '0000-00-00 00:00:00' is a not supported timestamp in Postgres. The database schema sets '1970-01-01 00:00:00' as default in pgsql.

I suggest the following changes:

diff --git a/app/modules/Api/config/views/host.xml b/app/modules/Api/config/views/host.xml
index b084532..953ad7d 100644
--- a/app/modules/Api/config/views/host.xml
+++ b/app/modules/Api/config/views/host.xml
@@ -14,7 +14,7 @@
                 h.alias AS HOST_ALIAS,
                 h.display_name AS HOST_DISPLAY_NAME,
                 hs.current_state AS HOST_CURRENT_STATE,
-                (CASE WHEN hs.last_state_change='0000-00-00 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
+                (CASE WHEN hs.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
                 hs.last_check AS HOST_LAST_CHECK,
                 hs.next_check AS HOST_NEXT_CHECK,
                 ps.program_start_time AS HOST_PROGRAM_START_TIME,
@@ -106,7 +106,7 @@
                 h.display_name AS HOST_DISPLAY_NAME,
                 hs.current_state AS HOST_CURRENT_STATE,
                 hs.process_performance_data AS HOST_PROCESS_PERFORMANCE_DATA,
-                (CASE WHEN hs.last_state_change='0000-00-00 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
+                (CASE WHEN hs.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE hs.last_state_change END) as DURATION_START,
                 hs.last_check AS HOST_LAST_CHECK,
                 hs.next_check AS HOST_NEXT_CHECK,
                 ps.program_start_time AS HOST_PROGRAM_START_TIME,
diff --git a/app/modules/Api/config/views/service.xml b/app/modules/Api/config/views/service.xml
index 4915fea..2066e98 100644
--- a/app/modules/Api/config/views/service.xml
+++ b/app/modules/Api/config/views/service.xml
@@ -18,7 +18,7 @@
                 s.display_name AS SERVICE_DISPLAY_NAME,
                 ss.process_performance_data AS SERVICE_PROCESS_PERFORMANCE_DATA,
                 ss.current_state AS SERVICE_CURRENT_STATE,
-                (CASE WHEN ss.last_state_change='0000-00-00 00:00:00' THEN ps.program_start_time ELSE ss.last_state_change END) as DURATION_START,
+                (CASE WHEN ss.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE ss.last_state_change END) as DURATION_START,
                 ps.program_start_time AS SERVICE_PROGRAM_START_TIME,
                 ss.last_check AS SERVICE_LAST_CHECK,
                 ss.next_check AS SERVICE_NEXT_CHECK,
@@ -133,7 +133,7 @@
                 os.name2 AS SERVICE_NAME,
                 s.display_name AS SERVICE_DISPLAY_NAME,
                 ss.current_state AS SERVICE_CURRENT_STATE,
-                (CASE WHEN ss.last_state_change='0000-00-00 00:00:00' THEN ps.program_start_time ELSE ss.last_state_change END) as DURATION_START,
+                (CASE WHEN ss.last_state_change<='1970-01-01 00:00:00' THEN ps.program_start_time ELSE ss.last_state_change END) as DURATION_START,
                 ss.process_performance_data AS SERVICE_PROCESS_PERFORMANCE_DATA,
                 ss.last_check AS SERVICE_LAST_CHECK,
                 ss.next_check AS SERVICE_NEXT_CHECK,

This works with MySQL and PgSQL, tested that.

The patch is required for Icinga Web 1.7.2 to work with a Pgsql IDO.

[icinga-migration]

Updated by mhein on 2012-08-13 14:54:08 +00:00

• Status changed from New to Resolved
• Done % changed from 0 to 100

Applied in changeset 7940ba4.