Bug with 'in set' filter matching and execution of commands
Posted: Wed Aug 24, 2011 8:43 pm
Hi all,
I first noticed this in v1.22.3 but for years now I've made do with a workaround and totally forgot about submitting it as a bug report. I could do with it working now so figured the least I could do was help diagnose it!
The issue is that when defining a filter using the 'in set' match criteria e.g. Monitor Name in set Conservatory,Lounge then it should match all those events where the monitor name is either Conservatory or Lounge.
This works correctly if you Submit the filter i.e. the events window appears with all the events where the monitor name matches (and where other filter criteria, if present, are also satisfied). However, if you Execute the filter - and an action such as Upload all matches is set then whilst the events window appears with the appropriate matches it does not go on to upload the events (or execute any other specified action).
If you restrict the in set criteria to only, say, Conservatory then the events are indeed uploaded. It is multi-item sets that fail.
I am confident that the cause of the problem is hinted at by the zmfilter logging output (this is from v1.22.3 in case that matters):
(Note that in this example filter criteria is unarchived events greater than 20s that are either from monitor name Conservatory or Lounge)
The key bit is M.Name in ('Conservatory,Lounge') which I believe should actually read M.Name in ('Conservatory','Lounge') in MySQL syntax i.e. each item in the set requires single quotes around it.
To demonstrate this within MySQL using a simple 'in set' query, firstly using ('Conservatory,Lounge'):
But the same query again using the syntax ('Conservatory','Lounge'):
Apologies for just highlighting the bug and not fixing the code - my MySQL knowledge is all Google-based and was non-existent until a few hours ago, and my programming knowledge is even worse! 
Regards,
Mathew
I first noticed this in v1.22.3 but for years now I've made do with a workaround and totally forgot about submitting it as a bug report. I could do with it working now so figured the least I could do was help diagnose it!
The issue is that when defining a filter using the 'in set' match criteria e.g. Monitor Name in set Conservatory,Lounge then it should match all those events where the monitor name is either Conservatory or Lounge.
This works correctly if you Submit the filter i.e. the events window appears with all the events where the monitor name matches (and where other filter criteria, if present, are also satisfied). However, if you Execute the filter - and an action such as Upload all matches is set then whilst the events window appears with the appropriate matches it does not go on to upload the events (or execute any other specified action).
If you restrict the in set criteria to only, say, Conservatory then the events are indeed uploaded. It is multi-item sets that fail.
I am confident that the cause of the problem is hinted at by the zmfilter logging output (this is from v1.22.3 in case that matters):
Code: Select all
08/24/11 20:24:41.275220 zmfilter[11245].DBG [SQL:select E.Id,E.MonitorId,M.Name as MonitorName,M.DefaultRate,M.DefaultScale,E.Name,E.Cause,E.Notes,E.StartTime,unix_timestamp(E.StartTime) as Time,E.Length,E.Frames,E.AlarmFrames,E.TotScore,E.AvgScore,E.MaxScore,E.Archived,E.Videoed,E.Uploaded,E.Emailed,E.Messaged,E.Executed from Events as E inner join Monitors as M on M.Id = E.MonitorId where not isnull(E.EndTime) and ( E.Length >= 20 and M.Name in ('Conservatory,Lounge') and E.Archived = 0 ) and ( E.Uploaded = 0 ) order by E.Id asc]The key bit is M.Name in ('Conservatory,Lounge') which I believe should actually read M.Name in ('Conservatory','Lounge') in MySQL syntax i.e. each item in the set requires single quotes around it.
To demonstrate this within MySQL using a simple 'in set' query, firstly using ('Conservatory,Lounge'):
Code: Select all
mysql> SELECT * FROM Monitors WHERE Name IN ('Conservatory,Lounge');
Empty set (0.00 sec)Code: Select all
mysql> SELECT * FROM Monitors WHERE Name IN ('Conservatory','Lounge');
+----+--------------+--------+----------+---------+----------------+----------+------------+---------+--------+----------+--------+---------------+------+---------+--------------------------------------------------+-------+--------+---------+-------------+------------+----------+-----+--------+-------------+---------------------------+--------+--------+------------------+-------------+---------------+----------------+--------------------+-----------------+---------------+-----------+--------+-------------+-------------------+--------------+--------------+-----------+---------------+----------------+-----------------+-------------+------------+----------------+-------------+-------------+-------------+--------------+-------------------+-----------+----------+
| Id | Name | Type | Function | Enabled | LinkedMonitors | Triggers | Device | Channel | Format | Protocol | Method | Host | Port | SubPath | Path | Width | Height | Palette | Orientation | Brightness | Contrast | Hue | Colour | EventPrefix | LabelFormat | LabelX | LabelY | ImageBufferCount | WarmupCount | PreEventCount | PostEventCount | StreamReplayBuffer | AlarmFrameCount | SectionLength | FrameSkip | MaxFPS | AlarmMaxFPS | FPSReportInterval | RefBlendPerc | Controllable | ControlId | ControlDevice | ControlAddress | AutoStopTimeout | TrackMotion | TrackDelay | ReturnLocation | ReturnDelay | DefaultView | DefaultRate | DefaultScale | SignalCheckColour | WebColour | Sequence |
+----+--------------+--------+----------+---------+----------------+----------+------------+---------+--------+----------+--------+---------------+------+---------+--------------------------------------------------+-------+--------+---------+-------------+------------+----------+-----+--------+-------------+---------------------------+--------+--------+------------------+-------------+---------------+----------------+--------------------+-----------------+---------------+-----------+--------+-------------+-------------------+--------------+--------------+-----------+---------------+----------------+-----------------+-------------+------------+----------------+-------------+-------------+-------------+--------------+-------------------+-----------+----------+
| 1 | Conservatory | Remote | Modect | 1 | | | /dev/video | 0 | 0 | http | simple | 192.168.1.201 | 80 | | nphmotionJpeg?Resolution=320x240&Quality=Clarity | 320 | 240 | 3 | 0 | -1 | -1 | -1 | -1 | Event- | %N - %y/%m/%d %H:%M:%S %T | 0 | 0 | 40 | 25 | 10 | 10 | 1000 | 1 | 600 | 0 | 0.00 | 0.00 | 1000 | 7 | 0 | 0 | NULL | NULL | NULL | 0 | 0 | -1 | 0 | Events | 100 | 100 | #0100BE | yellow | 1 |
| 2 | Lounge | Remote | Modect | 1 | | | /dev/video | 0 | 0 | http | simple | 192.168.1.202 | 80 | | nphmotionJpeg?Resolution=320x240&Quality=Clarity | 320 | 240 | 3 | 0 | -1 | -1 | -1 | -1 | Event- | %N - %y/%m/%d %H:%M:%S | 0 | 0 | 40 | 25 | 10 | 10 | 1000 | 1 | 600 | 0 | 4.00 | 4.00 | 1000 | 7 | 0 | 0 | NULL | NULL | NULL | 0 | 0 | -1 | 0 | Events | 100 | 100 | #0100BE | red | 2 |
+----+--------------+--------+----------+---------+----------------+----------+------------+---------+--------+----------+--------+---------------+------+---------+--------------------------------------------------+-------+--------+---------+-------------+------------+----------+-----+--------+-------------+---------------------------+--------+--------+------------------+-------------+---------------+----------------+--------------------+-----------------+---------------+-----------+--------+-------------+-------------------+--------------+--------------+-----------+---------------+----------------+-----------------+-------------+------------+----------------+-------------+-------------+-------------+--------------+-------------------+-----------+----------+
2 rows in set (0.00 sec)Regards,
Mathew