sql malformed where clause

[joernott]

I have defined the following yaml file:

name: Spirit
children:
- name: dev-sys0
  children:
  - name: db2
    children:
    - host:    'dev-sys0-db2-v01.sf-rz.de'
      type:    host
    - service: 'db2_health_tablespace-usage'
      host:    'dev-sys0-db2-v01.sf-rz.de'
      type:    service

When trying to view this, I am getting this error message;

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "("
LINE 8: AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1
^, query was: SELECT so.name1 AS host_name, so.name2 AS service_description, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 99 ELSE CASE WHEN ss.state_type = 1 THEN ss.current_state ELSE ss.last_hard_state END END AS service_hard_state, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, CASE WHEN ss.problem_has_been_acknowledged > 0 THEN 1 ELSE 0 END AS service_handled_wo_host, ss.notifications_enabled AS service_notifications_enabled, ntpo.name1 AS service_notification_period, ss.is_flapping AS service_is_flapping, CASE WHEN (ss.scheduled_downtime_depth = 0 OR ss.scheduled_downtime_depth IS NULL) THEN 0 ELSE 1 END AS service_in_downtime FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id
LEFT JOIN icinga_timeperiods AS ntp ON ntp.timeperiod_object_id = s.notification_timeperiod_object_id AND ntp.config_type = 1 AND ntp.instance_id = s.instance_id
LEFT JOIN icinga_objects AS ntpo ON ntpo.object_id = s.notification_timeperiod_object_id
LEFT JOIN icinga_timeperiod_timeranges AS ntpr ON ntpr.timeperiod_id = ntp.timeperiod_id
AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1
AND ntpr.start_sec < UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
AND ntpr.end_sec > UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
WHERE (so.name1 IN ('dev-sys0-db2-v01.sf-rz.de')) ORDER BY LOWER(s.display_name) ASC

It look slike the WHERE clause is generated in a wrong order, the lines starting with "AND" are listed before the line starting with "WHERE".

This happens when I define a service. When I remove the service object from the yaml file, the tile is displayed, but it is in light purple (despite the hostalive for that host being green), but that is another issue.

[ulyszk]

It seems I have same issue. Could we somehow fix it?

[KAMI911]

I have exactly same issue.

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "(" LINE 8: AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1 ^, query was: SELECT so.name1 AS host_name, so.name2 AS service_description, CASE WHEN ss.has_been_checked = 0 OR ss.has_been_checked IS NULL THEN 99 ELSE CASE WHEN ss.state_type = 1 THEN ss.current_state ELSE ss.last_hard_state END END AS service_hard_state, CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 1 ELSE 0 END AS service_handled, CASE WHEN ss.problem_has_been_acknowledged > 0 THEN 1 ELSE 0 END AS service_handled_wo_host, ss.notifications_enabled AS service_notifications_enabled, ntpo.name1 AS service_notification_period, ss.is_flapping AS service_is_flapping, CASE WHEN (ss.scheduled_downtime_depth = 0 OR ss.scheduled_downtime_depth IS NULL) THEN 0 ELSE 1 END AS service_in_downtime FROM icinga_objects AS so INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = 1 AND so.objecttype_id = 2 INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id LEFT JOIN icinga_timeperiods AS ntp ON ntp.timeperiod_object_id = s.notification_timeperiod_object_id AND ntp.config_type = 1 AND ntp.instance_id = s.instance_id LEFT JOIN icinga_objects AS ntpo ON ntpo.object_id = s.notification_timeperiod_object_id LEFT JOIN icinga_timeperiod_timeranges AS ntpr ON ntpr.timeperiod_id = ntp.timeperiod_id AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1 AND ntpr.start_sec < UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE()) AND ntpr.end_sec > UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE()) WHERE ((so.name1 IN ('her-new.example.com', 'sys-mysql.example.com', 'haproxy01.example.com', 'haproxy02.example.com', 'her-test.si.net'))) ORDER BY LOWER(s.display_name) ASC

[ulyszk]

It seems the problem is caused by the static parts of SQL queries, which is matches with MySQL but incompatible with PostgreSQL. @joernott are you using PostgreSQL with Icinga2?

[KAMI911]

I tried to patch it to work with PostgreSQL:

diff --git a/library/Toplevelview/Monitoring/HostgroupQuery.php b/library/Toplevelview/Monitoring/HostgroupQuery.php
index 62cc014..b363b42 100644
--- a/library/Toplevelview/Monitoring/HostgroupQuery.php
+++ b/library/Toplevelview/Monitoring/HostgroupQuery.php
@@ -122,9 +122,9 @@ class HostgroupQuery extends IcingaHostgroupQuery
         $this->select->joinLeft(
             ['ntpr' => $this->prefix . 'timeperiod_timeranges'],
             "ntpr.timeperiod_id = ntp.timeperiod_id
-                AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1
-                AND ntpr.start_sec <= UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
-                AND ntpr.end_sec >= UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
+                AND ntpr.day = EXTRACT(DOW FROM CURRENT_DATE - 1)
+                -- AND ntpr.start_sec < EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM CURRENT_DATE)
+                -- AND ntpr.end_sec > EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM CURRENT_DATE)
             ",
             []
         );
diff --git a/library/Toplevelview/Monitoring/ServicestatusQuery.php b/library/Toplevelview/Monitoring/ServicestatusQuery.php
index addee2f..1725a1e 100644
--- a/library/Toplevelview/Monitoring/ServicestatusQuery.php
+++ b/library/Toplevelview/Monitoring/ServicestatusQuery.php
@@ -77,9 +77,9 @@ class ServicestatusQuery extends IcingaServicestatusQuery
         $this->select->joinLeft(
             ['ntpr' => $this->prefix . 'timeperiod_timeranges'],
             'ntpr.timeperiod_id = ntp.timeperiod_id
-                AND ntpr.day = DAYOFWEEK(CURRENT_DATE()) - 1
-                AND ntpr.start_sec < UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
-                AND ntpr.end_sec > UNIX_TIMESTAMP() - UNIX_TIMESTAMP(CURRENT_DATE())
+                AND ntpr.day = EXTRACT(DOW FROM CURRENT_DATE - 1)
+                -- AND ntpr.start_sec < EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM CURRENT_DATE)
+                -- AND ntpr.end_sec > EXTRACT(EPOCH FROM NOW()) - EXTRACT(EPOCH FROM CURRENT_DATE)
             ',
             []
         );

Now it is not break on PostgreSQL, might not work on MySQL/MariaDB. or not work at all. Try to figure out how can be DB independent.

[joernott]

Yes, we are working with Postgresql

[martialblog]

Should be fixed in #54 which now uses Icingadb\Common\Database