Missing event counts when value is 0

[rockedge]

I have a very recent build of ZM 1.33.0 compiled and running well. I have noticed the event count fields on the web console gui are not always displayed. Until an event is created and the event count is zero... the digit "0" is not displayed. Also the count of the zones shows as "0" and at some point not yet determined, will display the number of zones for that monitor properly but at ZM restart the zone count will be shown as "0".

One can click on the zone count for a monitor and it will open up the zone interface window which has the zones correctly displayed.

I am not wholly positive that it is only a ZM problem but am interested if anyone else has seen this pop up in their version 1.33.0

[knight-of-ni]

I've only got guesses at the moment, but there is a conversation in Slack with Akturas reporting negative events. Perhaps this is related.
It looks like he just waited and things righted themselves.

We fixed an issue displaying event counts on the web console with the 1.32.3 release, but due to the way we implemented it, it is possible the update could have been skipped on any machine that was running our master branch at any time.... Try calling zm_update-1.32.3.sql manually, from a sql prompt, to see if that makes things right.

[iconnor]

So in the 1.30 days, when displaying the console, we did an sql statement like SELECT COUNT(*) FROM Zones where MonitorID=? for each monitor.

In 1.32, we have a ZOneCount column in the Monitors table, and a trigger that updates it when you save a zone.

The trigger looks like this:
DROP TRIGGER IF EXISTS Zone_Insert_Trigger//
CREATE TRIGGER Zone_Insert_Trigger AFTER INSERT ON Zones
FOR EACH ROW
BEGIN
UPDATE Monitors SET ZoneCount=(SELECT COUNT(*) FROM Zones WHERE MonitorId=NEW.MonitorId) WHERE Id=NEW.MonitorID;
END
//
DROP TRIGGER IF EXISTS Zone_Delete_Trigger//
CREATE TRIGGER Zone_Delete_Trigger AFTER DELETE ON Zones
FOR EACH ROW
BEGIN
UPDATE Monitors SET ZoneCount=(SELECT COUNT(*) FROM Zones WHERE MonitorId=OLD.MonitorId) WHERE Id=OLD.MonitorID;
END
//


So I can't imagine how that value got to be a null, but to manually update them all you could do

Update Monitors set ZoneCount=(SELECT COUNT(*) FROM Zones WHERE MonitorId=Monitors.Id);

[rockedge]

I noticed this error when I ran zmupdate.pl but skipped over it and now used phpmyadmin to repeat the update manually and I have this error produced by zm_update-1.32.3.sql :

Code: Select all

Error

SQL query:

DROP TRIGGER IF EXISTS Events_Day_update_trigger;
CREATE TRIGGER Events_Day_update_trigger AFTER UPDATE ON Events_Day
FOR EACH ROW
  BEGIN
    declare diff BIGINT default 0;

    set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0);
    IF ( diff ) THEN
      IF ( NEW.MonitorID != OLD.MonitorID ) THEN
        UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) WHERE Monitors.Id=OLD.MonitorId;
        UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)+COALESCE(NEW.DiskSpace,0) WHERE Monitors.Id=NEW.MonitorId;
      ELSE
        UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)+diff WHERE Monitors.Id=NEW.MonitorId;
      END IF;
    END IF;
  END;

MySQL said: Documentation
#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 'CREATE TRIGGER Events_Day_update_trigger AFTER UPDATE ON Events_Day
FOR EACH ROW' at line 2

which now makes sense why it is working weirdly

there were nulls in the tables....I changed to 0 and the web console looks okay
I have no idea other than the sql syntax error near the end of the database upgrade .sql sequence

[knight-of-ni]

Try calling it from a command prompt:

Code: Select all

$ mysql -uroot -p zm < /usr/share/zoneminder/db/zm_update-1.32.3.sql

This works fine for me, with no error messages.

[rockedge]

I ran it and it appears okay...zmupdate.pl stumbled on it though and I ended up with nulls in the tables

Code: Select all

 ~ #mysql -uroot -p zm < /usr/local/share/zoneminder/db/zm_update-1.32.3.sql
Enter password: 
Column Protocol already exists in Servers
Column Protocol already exists in Servers
Column PathToIndex already exists in Servers
Column PathToIndex already exists in Servers
Column PathToZMS already exists in Servers
Column PathToZMS already exists in Servers
Column PathToApi already exists in Servers
Column PathToApi already exists in Servers
Column Port already exists in Servers
Column Port already exists in Servers

[knight-of-ni]

zmupdate.pl runs the sql statement under the zmuser sql account, but the example I gave ran as the mysql root user.

Verify you have granted all permission to the zmuser sql account like so:

Code: Select all

mysql -uroot -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
mysqladmin -uroot -p reload

[rockedge]

I had checked and the permissions were indicated as being correct. I did use the command you indicated just to be sure.

and then ran zmupdate.pl and it reports the database as at version 1.33.0