nfsen error: MySQL: You have an error in your SQL syntax;

Question

I'm trying to set up the mailing plugin in nfsen (it's written in perl).
At one point the plugin generates the following sql query:
SELECT event_id FROM events WHERE Level="alarm" AND Type="botlist1" WHERE event_id NOT IN ( SELECT event_id FROM attributes WHERE Name="Notified")
which prompts the error:

Events: SQL ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE event_id NOT IN ( SELECT event_id FROM attributes WHERE Name="Notified")' at line 1

I'm running nfsen 1.3.7, mysql server 5.5 and the table layouts of events and attributes are:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| event_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| starttime  | int(10)          | NO   |     | NULL    |                |
| stoptime   | int(10)          | YES  | MUL | NULL    |                |
| updatetime | int(10)          | NO   |     | NULL    |                |
| level      | varchar(32)      | NO   |     | NULL    |                |
| profile    | varchar(100)     | NO   |     | NULL    |                |
| type       | varchar(32)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| attribute_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| event_id     | int(10) unsigned | YES  | MUL | NULL    |                |
| name         | varchar(32)      | YES  |     | NULL    |                |
| value        | varchar(64)      | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Edit: no idea why this was marked as duplicate, the issue in the linked question doesn't seem to be related at all, since none of the names here appear to be reserved keywords.

Only one `where` is needed. Presumably the second should be `AND`. — Gordon Linoff, Aug 11 '15 at 12:29
nevermind, just realized you weren't talking about the nested query. — wrinkl3, Aug 11 '15 at 12:49
@GordonLinoff: You should post that as an answer so that the OP can accept it — Borodin, Aug 11 '15 at 12:54
@Borodin . . . Actually, the subject of duplicate where clauses comes up every so often. I consider it a typographical error and vote to close instead. — Gordon Linoff, Aug 11 '15 at 14:24
@GordonLinoff: A vote to close may well never be actuated. If you can find a duplicate question then you should close this one one those grounds — Borodin, Aug 11 '15 at 16:41

nfsen error: MySQL: You have an error in your SQL syntax;

0 Answers0