DB Upgrade to 1.36.34 fails when Monitor_Status contains rows. #4180
Open
Description
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:
- Ensure the
Monitor_Statustable 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)
- Attempt to upgrade from the PPA (
sudo apt install zmorsudo apt -f installto repeat the error) - Alternatively, manually run the
zm_update-1.36.34.sqlupgrade 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)
Metadata
Assignees
Labels
No labels