mySQL Error message: SQLSTATE[42000]: Syntax error or access violation: 1064

[alfie002]

Hello all,

We are experiencing the following error message when alerts run. Consequently, the NMS is monitoring but not alerting. We spotted this error message recently. on one instance of the NMS. Other instances of NMS’s are behaving correctly.

I have used the nySQL logging features to record the SQL statements presented to the database. I am unable to spot any “syntax” errors in the code. In fact, I can copy and paste and using an SQL client, I can run the code without errors. Where there is matching data, it is returned to the client, where there isn’t, clearly no data will be returned.

I am going to investigate “access violation” part of the error message. The may reveal something. I have pasted a sample into this subject.

Anyone with any ideas, please update this subject.

[2019-10-28 13:43:47] 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 AND ((devices.sysObjectID R’ at line 1 (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1) (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(64): dbFetchRows(‘SELECT * FROM d…’, Array)

[cjwbath]

Looks like an issue with quotation marks that have been inserted in odd places in the query - certainly it’s a syntax error and not any issue with access.

Is this a custom SQL query alert rule, or one you built in the UI? If it’s the latter can you share a screenshot or (probably better) copy and paste the summary from the Rule column in the Alert Rules table in the GUI.

Also I’d watch Alerts Blank - macros.device_down Broken - SQL Error syntax error or access violation: 1064 which seems to be the same issue actually…

[alfie002]

Hello cjwbath,

This rule was built using the rule builder. Please find details as requested.

Best regards,

Alf.

[alfie002]

Screen shot attached.

[superchunk2000]

One of outr installs stopped generating alerts recently after an update. This was around the 21st of Oct.

Component	Version
LibreNMS	1.56-81-g259c5294a
DB Schema	2019_02_05_140857_remove_config_definition_from_db (146)
PHP	7.2.19-0ubuntu0.18.04.1
MySQL	10.1.41-MariaDB-0ubuntu0.18.04.1
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3
====================================

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

We found the log full of the following error

2019-10-28 13:43:47] 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 AND ((devices.sysObjectID R’ at line 1 (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1) (SQL: SELECT * FROM devices WHERE (devices.device_id = 335) AND ("(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)") = 1 AND ((devices.sysObjectID REGEXP “(.1.3.6.1.4.1.43.)”) && (devices.os LIKE “%3Com%”)) = 1)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(64): dbFetchRows(‘SELECT * FROM d…’, Array)

On investigation there appears to be a problem with the macros.device_down on one of our LibreNMS Installs;

On the non working system the Rule Builder has the following;

And adds the following SQL statement. The section added by the macros.device_down has double quotes it shouldn’t have e.g. (“(devices.status = 0 && “(devices.disabled = 0 && devices.ignore = 0)”)”)

SELECT * FROM devices WHERE (devices.device_id = ?) AND ("(devices.status = 0 && "(devices.disabled = 0 && devices.ignore = 0)")") = 1 AND ((devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.43\.)") && (devices.os LIKE "%3Com%")) = 1 AND devices.type LIKE '%Network%'

On a working system we have the same rule but there are no spurious double quotes being added?

SELECT * FROM devices WHERE (devices.device_id = ?) AND (devices.status = 0 && (devices.disabled = 0 && devices.ignore = 0)) = 1 AND ((devices.sysObjectID REGEXP "(\.1\.3\.6\.1\.4\.1\.43\.)") && (devices.os LIKE "%3Com%")) = 1 AND devices.type LIKE '%Network%'

The macros are built in and I think they are being pulled from /opt/librenms/misc/macros.json

I compared the file on each system and they are the same, they both have the following line.

non-working
1577351 -rw-r--r-- 1 librenms librenms 3313 Feb 15 2019 macros.json

"device_down": "(%devices.status = 0 && %macros.device)",

Working
793166 -rw-rw-r-- 1 librenms librenms 3313 Feb 14 2019 macros.json

"device_down": "(%devices.status = 0 && %macros.device)",

Why would the extra double quotes be added? How do I stop them adding it?

[superchunk2000]

To add to this I have tested by removing the macros.devices_down statment from the rule and re-adding it.
The double quotes are still added incorrectly.

If I create a brand new rule I get the same result;

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

[murrant]

Likely your database contains an incorrect definition for this macro. If you remove it, it will fall back to the one in macros.json

[superchunk2000]

But when I create a new rule in the rule builder that uses this macro does it not get the definition for it from the macros.json file?

I have tested this as you can see and the double quotes are still there.

If I am incorrect in this assumption how do I clear the incorrect definition from the database? I use this macro in alot of different rules and they are all exhibiting the same problem.

[superchunk2000]

I have created a new rule with only the devices_down macro.

The SQL rule created when making this rule is as follows;

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

If I then override the rule builder rule;

and change the rule as follows;

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

I am now getting alerts for the devices that are currently down.

However, I have many other rules which rely on the the built in macros which are not currently working.

Can anyone advise on why the rule includes the double quotes from the macros.json file when it shouldn’t?

Is there an intermediate step which I am missing which is where the quotes are being added?

[superchunk2000]

I have come up with a workaround to the issue.

I have created my own device down macro which compiles the SQL rule correctly.

///IPP Device Down Rule
$config['alert']['macros']['rule']['IPP_Device'] = '(devices.disabled = 0 AND devices.ignore = 0)';
$config['alert']['macros']['rule']['IPP_Device_Down'] = '(devices.status = 0 AND %macros.IPP_Device)';
$config['alert']['macros']['rule']['IPP_Device_Up'] = '(devices.status = 1 AND %macros.IPP_Device)';

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

However, I am still at a loss as to how this could be happening. I would really appreciate any input into how these extra charaters are being added from the macros.json file.