Hi All,
While investigating another probelm with our server we were checking the following log.
/opt/librenms/logs/librenms.log
The log is full of the following error
[2019-01-28 14:06:17] 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 '~ “^cisco.*State$” && sensors.sensor_alert = “1”)
This relates to a Macro - macros.device_component_down_cisco
We are currently using the macro in a rule that monitors Cisco switch for components that have failed.
We have checked our config.php and this isn’t a Macro we have written.
The macro in question is in
/opt/librenms/misc/macros.json
"device_component_down_cisco": "%sensors.sensor_current != \"1\" && %sensors.sensor_current != \"5\" && %sensors.sensor_type ~ \"^cisco.*State$\" && %sensors.sensor_alert = \"1\"",
To stop this issue we have replaced the Tilde with an equals. Can anyone advise if this can be changed as this will likely get overwritten the next time we update.
Regards,
Duncan
Interesting we still get the error after another polling run;
/opt/librenms/poller.php 611 2019-02-01 15:33:41 - 1 devices polled in 6.541 secs
[2019-02-01 15:33:41] 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 '~ "^cisco.*State$" && sensors.sensor_alert = "1") = 1 AND (devices.status = 1
&&' at line 1 (SQL: SELECT * FROM devices,sensors WHERE (devices.device_id = 665 AND
devices.device_id = sensors.device_id) AND (sensors.sensor_current != "1" && sensors.sensor_current !=
"5" && sensors.sensor_type ~ "^cisco.*State$" && sensors.sensor_alert = "1") = 1 AND (devices.status = 1
&& (devices.disabled = 0 && devices.ignore = 0)) = 1) (SQL: SELECT * FROM devices,sensors WHERE
(devices.device_id = 665 AND devices.device_id = sensors.device_id) AND (sensors.sensor_current != "1"
&& sensors.sensor_current != "5" && sensors.sensor_type ~ "^cisco.*State$" && sensors.sensor_alert =
"1") = 1 AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1)
The tilde should beLIKE
Please submit a fix to GitHub if that fixes it.
Old topic, I know, but I just had this same issue and thought I’d put some notes in about what I saw happening and what I did to fix it.
So, my logs were full of the same SQL query error as above for the same rule (macros.device_component_down_cisco). I looked at the rule in question and it was pointing at said macro with no SQL override, checked the misc/macros.json file and the rule is updated with a regexp instead of a tilde there, but the logs showed the tilde was still used.
Edited the rule, set SQL override to yes, changed ~ to LIKE and saved.
Logs stopped filling with errors.
Edited the rule again and turned off override and logs were OK for a while but then the error came back and the ~ had returned to the SQL line again somehow.
Where could it be getting this from if macros.json is the updated version using REGEXP?
I’ve changed it back to override and replaced ~ with LIKE again for now to suppress the error but I’m left wondering where it’s getting the old macro code from. This is an old-ish install (c2017) so I don’t know if a change in functionality has left something behind.
Also, librenms.log shows the issue started at midnight on 2019-10-18:
/opt/librenms/poller.php 577 2019-10-18 00:17:07 - 1 devices polled in 5.585 secs
/opt/librenms/poller.php 378 2019-10-18 00:17:08 - 1 devices polled in 5.462 secs
/opt/librenms/poller.php 260 2019-10-18 00:17:09 - 1 devices polled in 5.598 secs
/opt/librenms/poller.php 603 2019-10-18 00:17:09 - 1 devices polled in 5.487 secs
/opt/librenms/poller.php 598 2019-10-18 00:17:09 - 1 devices polled in 5.554 secs
[2019-10-18 00:17:10] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL synt
ax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘~ “^cisco.*State$” && senso
rs.sensor_alert = “1”) = 1’ at line 1 (SQL: SELECT * FROM devices,sensors WHERE (devices.device_id = 494 AND devices.device_id = s
ensors.device_id) AND (sensors.sensor_current != “1” && sensors.sensor_current != “5” && sensors.sensor_type ~ “^cisco.*State$” &&
sensors.sensor_alert = “1”) = 1) (SQL: SELECT * FROM devices,sensors WHERE (devices.device_id = 494 AND devices.device_id = senso
rs.device_id) AND (sensors.sensor_current != “1” && sensors.sensor_current != “5” && sensors.sensor_type ~ “^cisco.*State$” && sen
sors.sensor_alert = “1”) = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(64): dbFetchRows(‘SELECT * FROM d…’, Array)
/opt/librenms/poller.php 494 2019-10-18 00:17:10 - 1 devices polled in 5.671 secs