Alerts Not Triggering - SQLSTATE[42000]: Syntax error or access violation: 1064

Hi All,

We have a problem with a LibreNMS server where the alerts are not triggering for devices that are down. We recently ran alot of upgrades on the server as it wasn’t set to get the daily.sh

root@host2:/opt/librenms# ./validate.php

Component Version
LibreNMS 1.63-28-gfb6c0f42f
DB Schema 2020_04_19_010532_eventlog_sensor_reference_cleanup (165)
PHP 7.4.5
MySQL 10.1.44-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.10.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

We have alot of the following in the logs when it call the macros.device_down

[2020-05-04 18:24:02] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(devices.disabled = 0 && devices.ignore = 0)")") = 1' at line 1 (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current <= customoids.customoid_limit_low AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1) (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current <= customoids.customoid_limit_low AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(73): dbFetchRows()
#1 /opt/librenms/poller.php(148): LibreNMS\Alert\AlertRules->runRules()
#2 {main}
[2020-05-04 18:24:02] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(devices.disabled = 0 && devices.ignore = 0)")") = 1' at line 1 (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current >= customoids.customoid_limit_warn AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1) (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current >= customoids.customoid_limit_warn AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(73): dbFetchRows()
#1 /opt/librenms/poller.php(148): LibreNMS\Alert\AlertRules->runRules()
#2 {main}
[2020-05-04 18:24:02] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(devices.disabled = 0 && devices.ignore = 0)")") = 1' at line 1 (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current <= customoids.customoid_limit_low_warn AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1) (SQL: SELECT * FROM devices,customoids WHERE (devices.device_id = 691 AND devices.device_id = customoids.device_id) AND customoids.customoid_current <= customoids.customoid_limit_low_warn AND customoids.customoid_alert = 1 AND ("(devices.status = 1 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(73): dbFetchRows()
#1 /opt/librenms/poller.php(148): LibreNMS\Alert\AlertRules->runRules()
#2 {main}
/opt/librenms/poller.php 691 2020-05-04 18:24:02 - 1 devices polled in 35.71 secs

If I delete all the Alerts the errors stop populating the logs. If I re-create the rules from the collection we immidiatly get the same errors in the logs again.

I don’t understand why this would happen when we re-create the rules from the built in rules and using the built in macro.

We have now worked around this by using the devices.status != 1

However, the other rules created from the collection all use the macros.device_down = 1 . These are also causing the SQLSTATE[42000] error.

We could put the same workaround in but I am concerned that there is something else wrong with the SQL database. We have other LibreNMS instances that work fine using macros.device_down.

I have compared the rules created by the rule builder on the non working server with the working server.

LibreNMS - Alerts Work

This is the SQL statement created when the rule is built.

SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1

These are pulled from the /opt/librenms/misc/macros.json

root@working:/opt/librenms/misc# cat macros.json
...
"device_up": "(%devices.status = 1 && %macros.device)",
"device_down": "(%devices.status = 0 && %macros.device)",
...

LibreNMS - Alerts Fail

This is the SQL statement created when the rule is built. I assume the double quotyes are being inserted incorrectly and this is what the SQL error is complaing about.

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1

root@failing:/opt/librenms/misc# cat macros.json
"device_up": "(%devices.status = 1 && %macros.device)",
"device_down": "(%devices.status = 0 && %macros.device)",
...

Why would the JSON be interpreted differently when it is pulled into the rule?
Is the only option to re-install LibreNMS?
If so can we just reimport the devices and locations table into the new install?

I have looked at the Database Table librenms.alert_rules

The query field for the problem rule looks as follows;

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1

The correspsonding rule builder field looks as follows;

{"condition":"AND","rules":[{"id":"macros.device_down","field":"macros.device_down","type":"integer","input":"radio","operator":"equal","value":"1"}],"valid":true}

We manually removed the erroneous quotes the rule works correctly and the SQL Errors stop.

SELECT * FROM devices WHERE (devices.device_id = ?) AND ((devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0))) = 1

I then created a new rule from the collection;

With the standard settings

The following new entry was added to the librenms.alert_rules table

Query

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1

Builder

{"condition":"AND","rules":[{"id":"macros.device_down","field":"macros.device_down","type":"integer","input":"radio","operator":"equal","value":"1"}],"valid":true}

I have compared the builder field with our working instance and they are identical.

Can any body help with my questions;

Why would the JSON be interpreted differently when it is pulled into the rule?
What could be adding the extra double quotes?
Is the only option to re-install LibreNMS?
If so can we just reimport the devices and locations table into the new install?

So this became a much larger problem over the weekend. The rules reverted to their previous state with the extra quotes in the rules in the librenms.alert_rules table.

This caused the SQL Errors again meaning our alerts wern’t triggering. We are unsure how this happened, possibly when the daily update script ran.

Is anybody able to help us with this problem?

I cannot reproduce your issue:

Rule name: Down devices
Alert rule: macros.device_down = 1
Alert query: SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1
Rule match: matches

Reinstalling rarely helps. If validate.php is ok your install is fine, likely just something you are doing wrong.

Hi Murrant,

Can you elaborate on what you think I could be doing wrong?

Are you able to give any guidance on my other queries?

Why would the JSON be interpreted differently when it is pulled into the rule?
What could be adding the extra double quotes?

If I hav to re-build the server from scratch will it cause problems if we reimport the devices and locations table into the new install or does the whole DB need to be pulled in?

Thanks,

Duncan

Perhaps some odd setting in php.ini?

This is my builder rule:

{"condition":"AND","rules":[{"id":"macros.device_down","field":"macros.device_down","type":"integer","input":"radio","operator":"equal","value":"1"}],"valid":true}