DB Upgrade to 1.36.34 fails when Monitor_Status contains rows.

[pHr34kY]

Environment:

• ZoneMinder: v1.36.35-noble1
• OS: Ubuntu 24.04.1 LTS x64
• Installed via apt ppa (https://ppa.launchpadcontent.net/iconnor/zoneminder-1.36/ubuntu/ noble main)

Bug description:
When upgrading the from the Ubuntu repo version (also v1.36), the database schema upgrade will fail with this error:

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'UpdatedOn' at row 1

The problem is that the schema upgrade cannot add a "NOT NULL" column on a table which already has rows. The schema upgrade succeeds if you manually execute TRUNCATE TABLE Monitor_Status; prior to the schema upgrade. The rows are regenerated without issue when zoneminder restarts.

Steps to reproduce:

1. Ensure the Monitor_Status table has rows:

mysql> select * FROM `Monitor_Status` LIMIT 10;
+-----------+------------+------------+-------------+------------------+
| MonitorId | Status     | CaptureFPS | AnalysisFPS | CaptureBandwidth |
+-----------+------------+------------+-------------+------------------+
|         1 | Connected  |      19.99 |        0.00 |           757374 |
|         3 | NotRunning |       0.00 |        0.00 |           163170 |
+-----------+------------+------------+-------------+------------------+
2 rows in set (0.00 sec)

2. Attempt to upgrade from the PPA (sudo apt install zm or sudo apt -f install to repeat the error)
3. Alternatively, manually run the zm_update-1.36.34.sql upgrade script.

Expected behavior
The DB schema upgrade should add the UpdatedOn date column and complete without error.

Debug Logs

0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
1 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Setting up zoneminder (1.36.35-noble1) ...
detected systemd
Detected db service is mysql.service
Checking for db
Db exists.
Updating permissions for user zmuser@localhost

Initiating database upgrade to version 1.36.35 from version 1.36.33

Upgrading database to version 1.36.35
Upgrading DB to 1.36.34 from 1.36.33
ERROR 1292 (22007) at line 11: Incorrect datetime value: '0000-00-00 00:00:00' for column 'UpdatedOn' at row 1
Output: 
Command 'mysql --defaults-file=/etc/mysql/debian.cnf -hlocalhost zm < /usr/share/zoneminder/db/zm_update-1.36.34.sql' exited with stat
us: 1
Error updating db.
dpkg: error processing package zoneminder (--configure):
 installed zoneminder package post-installation script subprocess returned error exit status 1
Errors were encountered while processing:
 zoneminder
needrestart is being skipped since dpkg has failed
E: Sub-process /usr/bin/dpkg returned an error code (1)

[welcome]

Thanks for opening your first issue here! Just a reminder, this forum is for Bug Reports only. Be sure to follow the issue template!

[connortechnology]

So tired of mysql. Thanks for the report. Going to have to do a 1.36.36 fairly soon.