[Fixed] SQL-ERR when accessing timeline view - events missing

[c128]

Hi,
Running the latest build dev build on Ubuntu 22.04: 1.37.45~20231023170647-jammy, Mysql 8.0.34-0ubuntu0.22.04.1.
Whenever I access the events view, I get the following in the logs:

Code: Select all

Oct 25 06:53:30 cctv web_php[795]: 10/25/23, 6:53:30 AM GMT+1.284832 web_php[795].ERR [10.10.20.1] [SQL-ERR 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'Events 
Oct 25 06:53:30 cctv web_php[795]:   AS E 
Oct 25 06:53:30 cctv web_php[795]: INNER JOIN Monitors 
Oct 25 06:53:30 cctv web_php[795]:   AS M 
Oct 25 06:53:30 cctv web_php[795]:   ON (E.MonitorId = M.Id) 
Oct 25 06:53:30 cctv web_php[795]: LEFT JOI' at line 5', statement was 'SELECT EventId,FrameId,Delta,Score FROM Frames WHERE EventId IN(
Oct 25 06:53:30 cctv web_php[795]: SELECT 
Oct 25 06:53:30 cctv web_php[795]:   E.Id, 
Oct 25 06:53:30 cctv web_php[795]:   GROUP_CONCAT(T.Name SEPARATOR ", ")
Oct 25 06:53:30 cctv web_php[795]:     AS TagsFROM Events 
Oct 25 06:53:30 cctv web_php[795]:   AS E 
Oct 25 06:53:30 cctv web_php[795]: INNER JOIN Monitors 
Oct 25 06:53:30 cctv web_php[795]:   AS M 
Oct 25 06:53:30 cctv web_php[795]:   ON (E.MonitorId = M.Id) 
Oct 25 06:53:30 cctv web_php[795]: LEFT JOIN Events_Tags 
Oct 25 06:53:30 cctv web_php[795]:   AS ET 
Oct 25 06:53:30 cctv web_php[795]:   ON E.Id = ET.EventId 
Oct 25 06:53:30 cctv web_php[795]: LEFT JOIN Tags 
Oct 25 06:53:30 cctv web_php[795]:   AS T 
Oct 25 06:53:30 cctv web_php[795]:   ON T.Id = ET.TagId 
Oct 25 06:53:30 cctv web_php[795]: WHERE NOT isnull(StartDateTime) AND   E.StartDateTime >= '2023-10-24 06:53:30'
Oct 25 06:53:30 cctv web_php[795]:  and E.MonitorId = '1'
Oct 25 06:53:30 cctv web_php[795]:  AND EndDateTime >= '2023-10-24 07:00:00' AND StartDateTime <= '2023-10-24 22:59:59') AND Score > 0 ORDER BY Score DESC' params:] at /usr/share/zoneminder/www/includes/database.php line 161

There's the odd event in the view itself, but most are missing.

Database seems to be updated:

Code: Select all

# zmupdate.pl 

Database already at version 1.37.45, update skipped.

[dougmccrary]

Hmm. I thought I was already at that, but I just got it and don't get that error.
Perhaps you're doing something I'm not?
Anyway, I'm fairly certain that

LEFT JOI' at line 5', statement was

should be a JOIN
Maybe just try a reinstall...sudo apt remove zoneminder / sudo apt install zoneminder

[c128]

Hmm. I thought I was already at that, but I just got it and don't get that error.
Perhaps you're doing something I'm not?
Anyway, I'm fairly certain that

LEFT JOI' at line 5', statement was

should be a JOIN
Maybe just try a reinstall...sudo apt remove zoneminder / sudo apt install zoneminder

That `JOI` is just truncated output of the full statement that follows after.
I realise I can reinstall, but would rather find out what the issue is .

[c128]

So,the full query causing the issue is this:

Code: Select all

SELECT EventId,FrameId,Delta,Score FROM Frames WHERE EventId IN(
SELECT 
     E.Id, 
     GROUP_CONCAT(T.Name SEPARATOR ", ")
       AS TagsFROM Events 
     AS E 
    INNER JOIN Monitors 
     AS M 
     ON (E.MonitorId = M.Id) 
    LEFT JOIN Events_Tags 
     AS ET 
     ON E.Id = ET.EventId 
    LEFT JOIN Tags 
     AS T 
     ON T.Id = ET.TagId 
    WHERE NOT isnull(StartDateTime) AND   E.StartDateTime >= '2023-10-24 06:53:30'
    and E.MonitorId = '1'
    AND EndDateTime >= '2023-10-24 07:00:00' AND StartDateTime <= '2023-10-24 22:59:59'
) AND Score > 0 ORDER BY Score DESC

The TagsFROM Events looks wrong in the query...

[iconnor]

Yeah there should be a new line between Tags and FROM. I've looked at the code and it looks like this:
$col_str = '
E.*,
UNIX_TIMESTAMP(E.StartDateTime)
AS StartTimeSecs,
CASE WHEN E.EndDateTime
IS NULL
THEN (SELECT NOW())
ELSE E.EndDateTime END
AS EndDateTime,
CASE WHEN E.EndDateTime
IS NULL
THEN (SELECT UNIX_TIMESTAMP(NOW()))
ELSE UNIX_TIMESTAMP(EndDateTime) END
AS EndTimeSecs,
M.Name
AS Monitor,
GROUP_CONCAT(T.Name SEPARATOR ", ")
AS Tags';
$sql = '
SELECT
' .$col_str. '
FROM `Events`
AS E
INNER JOIN Monitors
AS M
ON E.MonitorId = M.Id
LEFT JOIN Events_Tags
AS ET
ON E.Id = ET.EventId
LEFT JOIN Tags
AS T
ON T.Id = ET.TagId
'.$where.'
GROUP BY E.Id
'.($sort?' ORDER BY '.$sort.' '.$order:'');


So there should be a newline. Where did it go? I can add more white space..but why is this happening? (It isn't happening here or on any of the 50+ servers I maintain).

[c128]

Yeah, super weird.

This is my /usr/share/zoneminder/www/skins/classic/views/timeline.php :

Code: Select all

$eventIdsSql = '
SELECT 
  E.Id, 
  GROUP_CONCAT(T.Name SEPARATOR ", ")
    AS TagsFROM Events 
  AS E 
INNER JOIN Monitors 
  AS M 
  ON (E.MonitorId = M.Id) 
LEFT JOIN Events_Tags 
  AS ET 
  ON E.Id = ET.EventId 
LEFT JOIN Tags 
  AS T 
  ON T.Id = ET.TagId 
WHERE NOT isnull(StartDateTime)';

Has the same timestamp as all the files around it, so it hasn't been manually edited/patched by something else:

Code: Select all

-rw-r--r-- 1 root root  4356 Oct 23 22:06 snapshots.php
-rw-r--r-- 1 root root  1953 Oct 23 22:06 stats.php
-rw-r--r-- 1 root root  1802 Oct 23 22:06 status.php
-rw-r--r-- 1 root root 31009 Oct 23 22:06 timeline.php
-rw-r--r-- 1 root root 12627 Oct 23 22:06 user.php
-rw-r--r-- 1 root root  9776 Oct 23 22:06 video.php
-rw-r--r-- 1 root root 17726 Oct 23 22:06 watch.php
-rw-r--r-- 1 root root 14327 Oct 23 22:06 zone.php

[c128]

Right, so, I manually fixed that error in the file, restarted and got this error instead:

Code: Select all

Oct 25 20:37:25 cctv web_php[801]: 10/25/23, 8:37:25 PM GMT+1.211368 web_php[801].ERR [10.10.20.1] [SQL-ERR 'SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)', statement was 'SELECT EventId,FrameId,Delta,Score FROM Frames WHERE EventId IN(
Oct 25 20:37:25 cctv web_php[801]: SELECT 
Oct 25 20:37:25 cctv web_php[801]:   E.Id, 
Oct 25 20:37:25 cctv web_php[801]:   GROUP_CONCAT(T.Name SEPARATOR ", ")
Oct 25 20:37:25 cctv web_php[801]:     AS Tags
Oct 25 20:37:25 cctv web_php[801]:     FROM Events 
Oct 25 20:37:25 cctv web_php[801]:   AS E 
Oct 25 20:37:25 cctv web_php[801]: INNER JOIN Monitors 
Oct 25 20:37:25 cctv web_php[801]:   AS M 
Oct 25 20:37:25 cctv web_php[801]:   ON (E.MonitorId = M.Id) 
Oct 25 20:37:25 cctv web_php[801]: LEFT JOIN Events_Tags 
Oct 25 20:37:25 cctv web_php[801]:   AS ET 
Oct 25 20:37:25 cctv web_php[801]:   ON E.Id = ET.EventId 
Oct 25 20:37:25 cctv web_php[801]: LEFT JOIN Tags 
Oct 25 20:37:25 cctv web_php[801]:   AS T 
Oct 25 20:37:25 cctv web_php[801]:   ON T.Id = ET.TagId 
Oct 25 20:37:25 cctv web_php[801]: WHERE NOT isnull(StartDateTime) AND   E.StartDateTime >= '2023-10-24 20:37:25'
Oct 25 20:37:25 cctv web_php[801]:  and E.MonitorId = '1'
Oct 25 20:37:25 cctv web_php[801]:  AND EndDateTime >= '2023-10-24 21:00:00' AND StartDateTime <= '2023-10-25 20:37:25') AND Score > 0 ORDER BY Score DESC' params:] at /usr/share/zoneminder/www/includes/database.php line 161

Thinking there might be other errors, I reinstalled - apt-get install --reinstall zoneminder, and that reverted the change I'd manually made to /usr/share/zoneminder/www/skins/classic/views/timeline.php to give me the AS TagsFROM back.

Here's my install:

Code: Select all

zoneminder:
  Installed: 1.37.45~20231023170647-jammy
  Candidate: 1.37.45~20231023170647-jammy
  Version table:
 *** 1.37.45~20231023170647-jammy 500
        500 http://ppa.launchpad.net/iconnor/zoneminder-master/ubuntu jammy/main amd64 Packages
        100 /var/lib/dpkg/status
     1.36.12+dfsg1-1 500
        500 http://gb.archive.ubuntu.com/ubuntu jammy/universe amd64 Packages

Looks like it's in source https://github.com/ZoneMinder/zoneminde ... 7C7-L177C7.

[iconnor]

Oh timeline! You said events view.. ok now i know where to look

[c128]

Oh timeline! You said events view.. ok now i know where to look

Ah, apologies - it's the only view I use for viewing events, guess it's what I've always called it as a result (at least in my head ).

[iconnor]

Fixed committed, packages building.

[c128]

Thanks! Picked up that change today.

However, I'm now getting the second error that I mentioned above (when I tried the manual tweak for the fix now in source/packaged):

Code: Select all

Oct 27 10:28:37 cctv web_php[573322]: 10/27/23, 10:28:37 AM GMT+1.454289 web_php[573322].ERR [10.10.20.1] [SQL-ERR 'SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)', statement was 'SELECT EventId,FrameId,Delta,Score FROM Frames WHERE EventId IN(
Oct 27 10:28:37 cctv web_php[573322]: SELECT 
Oct 27 10:28:37 cctv web_php[573322]:   E.Id, 
Oct 27 10:28:37 cctv web_php[573322]:   GROUP_CONCAT(T.Name SEPARATOR ", ")
Oct 27 10:28:37 cctv web_php[573322]:     AS Tags
Oct 27 10:28:37 cctv web_php[573322]: FROM Events 
Oct 27 10:28:37 cctv web_php[573322]:   AS E 
Oct 27 10:28:37 cctv web_php[573322]: INNER JOIN Monitors 
Oct 27 10:28:37 cctv web_php[573322]:   AS M 
Oct 27 10:28:37 cctv web_php[573322]:   ON (E.MonitorId = M.Id) 
Oct 27 10:28:37 cctv web_php[573322]: LEFT JOIN Events_Tags 
Oct 27 10:28:37 cctv web_php[573322]:   AS ET 
Oct 27 10:28:37 cctv web_php[573322]:   ON E.Id = ET.EventId 
Oct 27 10:28:37 cctv web_php[573322]: LEFT JOIN Tags 
Oct 27 10:28:37 cctv web_php[573322]:   AS T 
Oct 27 10:28:37 cctv web_php[573322]:   ON T.Id = ET.TagId 
Oct 27 10:28:37 cctv web_php[573322]: WHERE NOT isnull(StartDateTime) AND   E.StartDateTime >= '2023-10-26 10:28:37'
Oct 27 10:28:37 cctv web_php[573322]:  and E.MonitorId = '1'
Oct 27 10:28:37 cctv web_php[573322]:  AND EndDateTime >= '2023-10-26 13:00:00' AND StartDateTime <= '2023-10-27 08:59:59') AND Score > 0 ORDER BY Score DESC' params:] at /usr/share/zoneminder/www/includes/database.php line 161

Code: Select all

root@cctv:~# zmupdate.pl 

Database already at version 1.37.46, update skipped.

[c128]

Since the very latest release:

Code: Select all

# apt-cache policy zoneminder
zoneminder:
  Installed: 1.37.46~20231027104343-jammy
  Candidate: 1.37.46~20231027104343-jammy

The timeline view seems to all be working again now - thanks!

I do see this error in the logs when accessing it though:

Code: Select all

Oct 30 12:52:58 cctv web_php[1657600]: 10/30/23, 12:52:58 PM UTC.653337 web_php[1657600].ERR [10.10.20.1] [SQL-ERR 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 'FROM Events AS E 
Oct 30 12:52:58 cctv web_php[1657600]: INNER JOIN Monitors AS M ON (E.MonitorId = M.Id) 
Oct 30 12:52:58 cctv web_php[1657600]: LEFT JOIN Ev' at line 4', statement was 'SELECT 
Oct 30 12:52:58 cctv web_php[1657600]:   min(E.StartDateTime) AS MinTime, 
Oct 30 12:52:58 cctv web_php[1657600]:   max(E.EndDateTime) AS MaxTime, 
Oct 30 12:52:58 cctv web_php[1657600]: FROM Events AS E 
Oct 30 12:52:58 cctv web_php[1657600]: INNER JOIN Monitors AS M ON (E.MonitorId = M.Id) 
Oct 30 12:52:58 cctv web_php[1657600]: LEFT JOIN Events_Tags AS ET ON E.Id = ET.EventId 
Oct 30 12:52:58 cctv web_php[1657600]: LEFT JOIN Tags AS T ON T.Id = ET.TagId 
Oct 30 12:52:58 cctv web_php[1657600]: WHERE NOT isnull(E.StartDateTime) AND NOT isnull(E.EndDateTime) AND   E.StartDateTime >= '2023-10-29 12:52:58'
Oct 30 12:52:58 cctv web_php[1657600]:  and E.MonitorId = '1'
Oct 30 12:52:58 cctv web_php[1657600]: ' params:] at /usr/share/zoneminder/www/includes/database.php line 161
Oct 30 12:52:58 cctv web_php[1657600]: 10/30/23, 12:52:58 PM UTC.661500 web_php[1657600].ERR [10.10.20.1] [SQL-ERR dbFetchOne no result, statement was 'SELECT 
Oct 30 12:52:58 cctv web_php[1657600]:   min(E.StartDateTime) AS MinTime, 
Oct 30 12:52:58 cctv web_php[1657600]:   max(E.EndDateTime) AS MaxTime, 
Oct 30 12:52:58 cctv web_php[1657600]: FROM Events AS E 
Oct 30 12:52:58 cctv web_php[1657600]: INNER JOIN Monitors AS M ON (E.MonitorId = M.Id) 
Oct 30 12:52:58 cctv web_php[1657600]: LEFT JOIN Events_Tags AS ET ON E.Id = ET.EventId 
Oct 30 12:52:58 cctv web_php[1657600]: LEFT JOIN Tags AS T ON T.Id = ET.TagId 
Oct 30 12:52:58 cctv web_php[1657600]: WHERE NOT isnull(E.StartDateTime) AND NOT isnull(E.EndDateTime) AND   E.StartDateTime >= '2023-10-29 12:52:58'
Oct 30 12:52:58 cctv web_php[1657600]:  and E.MonitorId = '1'
Oct 30 12:52:58 cctv web_php[1657600]: '] at /usr/share/zoneminder/www/includes/database.php line 170

[iconnor]

Another fix pushed. Sigh.

[c128]

All working now with no errors after installing 1.37.46~20231030102238-jammy - thanks!