ZoneMinder Forums

Hi,

I've been using Zoneminder 1.30.4 on Ubuntu Server 16.04 for couple of days now (was on 1.30.0 for several months), slowly adding more features. I Installed zoneminder following these instructions https://wiki.zoneminder.com/Ubuntu_Serv ... _easy_way I'm now trying to set email alerts about events longer than 6 seconds occuring during night.

I've tried to put it like 6 SEC / 6 SECONDS / 6 SECOND and same with lower case, but every syntax gives an error to log:
Problem seems pretty clear, but after looking in MariaDB documentation I'm lost, syntax seems to be right but I'm clearly doing something wrong with the setup of the filter.

Can't execute filter '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 > 00:00:06 ) and ( E.Emailed = 0 ) order by E.StartTime asc', ignoring: 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 ':00:06 ) and ( E.Emailed = 0 ) order by E.StartTime asc' at line 25

Any advice?

Previously mentioned update problem solved.

Updates are done at a command line using apt-get. Try using lower case sec

That cleared the update dilemma, thanks bbunge. Though I'm curious why is the check update button even there if it's obsolete?

I've tried lower cases also (I edited the original post to make it more obvious) and also tried your suggestion again but in vain.

the filter will work when the time value is changed from 00:00:06 to "6 second"

Ok, now I've tried it with all these:

00:00:06 / "00:00:06" / 6 SEC / "6 SEC" / 6 sec / "6 sec" / 6 second / 6 SECOND / "6 SECOND" / " 6 second" / 6 seconds / "6 seconds" / 6 SECONDS / "6 SECONDS" /

But nothing works, same error on log complaining about syntax.

.1 minute ?

Not sure the problem has got to do with what is entered into the field....
I created a filter just like in the screenshot, and I can't duplicate the error. Every value I've tried works.... 6, 6 sec, 6 Sec, 6 seconds, 00:00:06, etc.

I tried greater than 999999 to see if the empty set was causing a problem, and that worked too.

Tested on:
zm 1.30.4, CentOS 7, php 5.4.16, MariaDB 5.5.52
zm 1.30.4, Fedora 25, PHP 7.0.18, MariaDB 10.1.21

Not sure you will get any more information than what is shown in the logs in this case, but try running your filter direct from the command line.
http://zoneminder.readthedocs.io/en/sta ... oting-tips

Thank you knnniggett for testing these filter configs.

I'm getting this error when trying to run filter from command line:

DBD::mysql::st execute failed: You have an error on your SQL syntax; check the manual for that corresponds to your Mariadb server version for the right syntax to use near ' seconds ) and ( E.Emailed = 0 ) order by E.StartTime asc' at line 25 at /usr/share/perl5/Zoneminder/Filter.pm line 164.

Viher wrote: ↑Fri May 12, 2017 6:50 am Thank you knnniggett for testing these filter configs.

I'm getting this error when trying to run filter from command line:

DBD::mysql::st execute failed: You have an error on your SQL syntax; check the manual for that corresponds to your Mariadb server version for the right syntax to use near ' seconds ) and ( E.Emailed = 0 ) order by E.StartTime asc' at line 25 at /usr/share/perl5/Zoneminder/Filter.pm line 164.

Something to check as you are running Mariadb:

Change Mariadb settings: Note: you may have to follow several symbolic links to get to the actual config file for Mariadb.

nano /etc/mysql/my.cnf

Make the following changes:

character-set-server = latin1
collation-server = latin1_swedish_ci

Note: The above settings are actually the Mariadb default. Changing back to default is necessary to avoid errors when logging into Zoneminder. This was added on 01APR17 but is not an April Fool! It may be necessary to align these settings with your regional language.

I did a fresh install of 1.30.04 to a fresh Ubuntu server 16.04.02 system, this time using Mysql database and still getting same error. Seems like I have to check that sql conf file as bbunge suggested.

EDIT: added character-set lines, but that did not help.

I'm always use english as OS language, also every piece of software is in english. Only thing that has anything related to nordic languages is my keyboard and locale. After changing to linux few years ago, all the problems I get are almost always related to these *#!#¤%"! character sets..

Totally stumped at this point.
The title of your post says you are on zoneminder 1.30.3, but your screenshot shows you are on 1.30.0.
Perhaps the screen shot is just old, but make sure the version at the top of the web console says 1.30.4 following your recent reinstall.


Additionally, to get a better idea of where exactly that sql statement is being built, turn on debug then restart zoneminder. Filter the log output to show only zmfilter from the dropdown. After executing the filter, show us the debug log output. Include log entries a little before you open the filter window and a little after the error message appears.

Viher wrote: ↑Sat May 13, 2017 8:04 am I did a fresh install of 1.30.04 to a fresh Ubuntu server 16.04.02 system, this time using Mysql database and still getting same error. Seems like I have to check that sql conf file as bbunge suggested.

EDIT: added character-set lines, but that did not help.

I'm always use english as OS language, also every piece of software is in english. Only thing that has anything related to nordic languages is my keyboard and locale. After changing to linux few years ago, all the problems I get are almost always related to these *#!#¤%"! character sets..

MySQL and Mariadb each have changes that need to be made to the database config file (usually my.cnf). See the WIKI install instructions for Ubuntu 16.04:

https://wiki.zoneminder.com/Ubuntu_Serv ... e_easy_way

knnniggett wrote: ↑Sat May 13, 2017 12:51 pm Totally stumped at this point.
The title of your post says you are on zoneminder 1.30.3, but your screenshot shows you are on 1.30.0.
Perhaps the screen shot is just old, but make sure the version at the top of the web console says 1.30.4 following your recent reinstall.


Additionally, to get a better idea of where exactly that sql statement is being built, turn on debug then restart zoneminder. Filter the log output to show only zmfilter from the dropdown. After executing the filter, show us the debug log output. Include log entries a little before you open the filter window and a little after the error message appears.

It actually said "..and updating to 1.30.03" Edited now with current state of things. I'll do the debug & log tomorrow.

bbunge wrote: ↑Sat May 13, 2017 2:37 pm MySQL and Mariadb each have changes that need to be made to the database config file (usually my.cnf). See the WIKI install instructions for Ubuntu 16.04:

https://wiki.zoneminder.com/Ubuntu_Serv ... e_easy_way

I did follow those instructions meticulously, so all (though there seems to be only one, when using mysql db) suggested edits to config file has been made.

Debug log (zmfilter.pl part) doesn't give anything else than the same error I've posted previously.

I think I'm totally lost with this debug logging. Log fills up with web_php lines and occasional zmfilter.pl entry only states that same error.
When I browse /var/log/zm I see different logs from each component and numerous zm_debug.log.xxxx logs where xxxx is numbers. None of them include more usable info.

EDIT:

2017-05-17 12:21:44.115770 zmfilter 9624 DBG Sleeping for 60 seconds zmfilter.pl
2017-05-17 12:21:44.051210 zmfilter 9624 DBG Checking filter 'PurgeWhenFull', delete zmfilter.pl
2017-05-17 12:21:43.994240 zmfilter 9624 ERR Can't execute filter 'SELECT E.*, unix_timestamp(E.StartTime) as Time, M.Name as MonitorName, M.DefaultRate, M.DefaultScale FROM Events as E INNER JOIN Monitors as M on M.Id = E.MonitorId where not isnull(E.EndTime) and ( E.Length < 6 sec ) order by E.StartTime asc', ignoring: 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 'sec ) order by E.StartTime asc' at line 8 zmfilter.pl
2017-05-17 12:21:43.938090 zmfilter 9624 DBG Checking filter 'delete_short', delete zmfilter.pl
2017-05-17 12:21:43.882450 zmfilter 9624 DBG Got 2 filters zmfilter.pl
2017-05-17 12:21:43.826870 zmfilter 9624 DBG SQL:SELECT E.*, unix_timestamp(E.StartTime) as Time, M.Name as MonitorName, M.DefaultRate, M.DefaultScale FROM Events as E INNER JOIN Monitors as M on M.Id = E.MonitorId where not isnull(E.EndTime) and ( E.Archived = 0 and zmDiskPercent >= 95 ) order by E.Id asc limit 0,100 zmfilter.pl
2017-05-17 12:21:43.770960 zmfilter 9624 DBG Found filter 'PurgeWhenFull' zmfilter.pl
2017-05-17 12:21:43.710030 zmfilter 9624 DBG SQL:SELECT E.*, unix_timestamp(E.StartTime) as Time, M.Name as MonitorName, M.DefaultRate, M.DefaultScale FROM Events as E INNER JOIN Monitors as M on M.Id = E.MonitorId where not isnull(E.EndTime) and ( E.Length < 6 sec ) order by E.StartTime asc zmfilter.pl
2017-05-17 12:21:43.637610 zmfilter 9624 DBG Found filter 'delete_short' zmfilter.pl
2017-05-17 12:21:43.588620 zmfilter 9624 DBG Reloading filters zmfilter.pl
2017-05-17 12:21:38.483730 zmfilter 9624 INF Scanning for events