Service set error SQLSTATE[42000]: Syntax error or access violation:1064 you have an error in your SQL syntax;

Expected Behavior

I expected to see what my service set was containing

Current Behavior

I got a sweet error in my sql syntax ...

Possible Solution

we found what file was generating the error : vi /usr/share/icingaweb2/modules/director/library/Director/Web/Table/IcingaServiceSetServiceTable.php We made adjustements :

LINE 120-134  we commented the blacklisted thing:

    public function renderRow($row)
    {
        $tr = $this::row([
            $this->getServiceLink($row)
        ]);

        if ($row->disabled === 'y') {
            $tr->getAttributes()->add('class', 'disabled');
        }

        /*if ($row->blacklisted === 'y') {
            $tr->getAttributes()->add('class', 'strike-links');
        }*/

        return $tr;
    }

and here too :

LINE 221-END we commented the else at the end :

  public function prepareQuery()
    {
        $db = $this->db();
        $query = $db->select()->from(
            ['s' => 'icinga_service'],
            [
                'id'             => 's.id',
                'service_set_id' => 's.service_set_id',
                'host_id'        => 'ss.host_id',
                'service_set'    => 'ss.object_name',
                'service'        => 's.object_name',
                'disabled'       => 's.disabled',
                'object_type'    => 's.object_type',
            ]
        )->joinLeft(
            ['ss' => 'icinga_service_set'],
            'ss.id = s.service_set_id',
            []
        )->where(
            's.service_set_id = ?',
            $this->set->get('id')
        )->order('s.object_name');

        if ($this->affectedHost) {
            $query->joinLeft(
                ['hsb' => 'icinga_host_service_blacklist'],
                $db->quoteInto(
                    's.id = hsb.service_id AND hsb.host_id = ?',
                    $this->affectedHost->get('id')
                ),
                []
            )->group('s.id')->columns([
                'blacklisted' => "CASE WHEN hsb.service_id IS NULL THEN 'n' ELSE 'y' END",
            ]);
        } else {
            //$query->columns(['blacklisted' => "'n'"]);
        }

        return $query;
    }

And now, i can see what is in my service set, no more error ! resolved error

Steps to Reproduce (for bugs)

create a service template
create a service set and assign the service template in it
try to see what's in the service set
get the error (see : title or screenshot few lines down there ☺ )

Context

I am using Director to manage all hosts, templates, services, checks, notifications and so on. After creating some SNMP services template, i wanted to add them into a service set to make them easier to assign. I created a service set, i added a service template "snmp-load" and then, i wanted to see what my recently created service set had in it. (the service template snmp-load !!)

The error occured when I try to see what service templates i assignated to the service set. In this screenshot, we can see the error : img1 For those who can't read here is a smaller screenshot : img2-zoom

Your Environment

* Version used (icinga2 --version): icinga2 - The Icinga 2 network monitoring daemon (version: r2.8.4-1)

Application information: Installation root: /usr Sysconf directory: /etc Run directory: /run Local state directory: /var Package data directory: /usr/share/icinga2 State path: /var/lib/icinga2/icinga2.state Modified attributes path: /var/lib/icinga2/modified-attributes.conf Objects path: /var/cache/icinga2/icinga2.debug Vars path: /var/cache/icinga2/icinga2.vars PID path: /run/icinga2/icinga2.pid

Build information: Compiler: GNU 5.3.1 Build host: 9c880c2f42f5

* Operating System and version: System information: Platform: Ubuntu Platform version: 16.04.4 LTS (Xenial Xerus) Kernel: Linux Kernel version: 4.4.0-128-generic Architecture: x86_64

* Enabled features (icinga2 feature list): Disabled features: elasticsearch gelf influxdb opentsdb Enabled features: api checker command compatlog debuglog graphite ido-mysql livestatus mainlog notification perfdata statusdata syslog

* Icinga Web 2 version and modules (System - About): Icinga Web 2 Version 2.5.3

* Config validation (icinga2 daemon -C):

information/cli: Icinga application loader (version: r2.8.4-1) information/cli: Loading configuration file(s). information/ConfigItem: Committing config item(s). information/ApiListener: My API identity: monitoring.in.ama.bzh warning/ApplyRule: Apply rule 'backup-downtime' (in /etc/icinga2/conf.d/downtimes.conf: 5:1-5:52) for type 'ScheduledDowntime' does not match anywhere! information/ConfigItem: Instantiated 1 ApiListener. information/ConfigItem: Instantiated 1 SyslogLogger. information/ConfigItem: Instantiated 21 Zones. information/ConfigItem: Instantiated 19 Endpoints. information/ConfigItem: Instantiated 2 FileLoggers. information/ConfigItem: Instantiated 1 ApiUser. information/ConfigItem: Instantiated 39 Notifications. information/ConfigItem: Instantiated 2 NotificationCommands. information/ConfigItem: Instantiated 215 CheckCommands. information/ConfigItem: Instantiated 3 HostGroups. information/ConfigItem: Instantiated 1 IcingaApplication. information/ConfigItem: Instantiated 56 Hosts. information/ConfigItem: Instantiated 1 UserGroup. information/ConfigItem: Instantiated 2 Users. information/ConfigItem: Instantiated 4 TimePeriods. information/ConfigItem: Instantiated 335 Services. information/ConfigItem: Instantiated 1 ServiceGroup. information/ConfigItem: Instantiated 1 CompatLogger. information/ConfigItem: Instantiated 1 StatusDataWriter. information/ConfigItem: Instantiated 1 ExternalCommandListener. information/ConfigItem: Instantiated 1 CheckerComponent. information/ConfigItem: Instantiated 1 GraphiteWriter. information/ConfigItem: Instantiated 1 PerfdataWriter. information/ConfigItem: Instantiated 1 IdoMysqlConnection. information/ConfigItem: Instantiated 1 LivestatusListener. information/ConfigItem: Instantiated 1 NotificationComponent. information/ScriptGlobal: Dumping variables to file '/var/cache/icinga2/icinga2.vars' information/cli: Finished validating the configuration file(s).

Icinga / icinga2