zm_update-1.39.5.sql creates wrong field datatype

[net2pw]

ENVIRONMENT:
ZoneMinder 1.39.3
mariadb from 11.8.6-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using EditLine wrapper
Debian trixie 13.4

BUG:
Applied sql patch zm_update-1.39.5.sql which created the following field definition:

WhatDisplay tinyint(3) unsigned NOT NULL DEFAULT 0,

That creates the following error when attempting to add a monitor :

ERR SQL-ERR 'SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'OnlyVideo' for column zm.Monitors.WhatDisplay at row 1', statement was 'INSERT INTO Monitors (Id....

It placed it in the correct field order though so the script ran all the way through.

SOLUTION:
Able to create the correct field definition WhatDisplay enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo' by modifying the column in MariaDB.

[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!

[IgorA100]

@connortechnology
I don't yet understand what the problem with the script is :(
It worked fine for me.

[net2pw]

Well, I'm sure it works fine in most cases but all I can say is that in my environment you can see what the script created :
WhatDisplay tinyint(3) unsigned NOT NULL DEFAULT 0 (this is a copy and paste from the table definition after running the script)

If other users encounter the same problem then we'll know it's an issue. Maybe I did something wrong but hard to imagine what. There's not much that can go wrong with this: MariaDB [zm]> source /usr/share/zoneminder/db/zm_update-1.39.5.sql;

[connortechnology]

I just pushed an update to zm_update-1.39.5.sql that should redefine is as an enum. if you run zmupdate.pl again.

enums are not really supported anywhere other than mysql... we actually really need to get rid of them.

Which is unfortunate because I actually kinda like them.

[brkai]

@connortechnology

Good morning (here in Europe),
i just tried to upgrade my installation to 1.39.5, there's a little typo in the SQL-script:
A comma is missing in zm_update-1.39.5.sql, it should be:

SET @s = (SELECT IF(
(SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Monitors'
AND table_schema = DATABASE()
AND column_name = 'WhatDisplay'
) > 0,
"ALTER TABLE Monitors MODIFY WhatDisplay enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo'",
"ALTER TABLE Monitors ADD COLUMN WhatDisplay enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo' AFTER Decoding"
));

Cheers
Kai

[IgorA100]

#4756

[IgorA100]

It's strange, but it seems like the update isn't working for me.

root@zoneminder:/usr/bin# ./zmupdate.pl --user=root --pass=XXX

Initiating database upgrade to version 1.39.3 from version 1.39.3

Do you wish to take a backup of your database prior to upgrading?
This may result in a large file in /var/tmp/zm if you have a lot of events.
Press 'y' for a backup or 'n' to continue : y
Creating backup to /var/tmp/zm/zm-1.39.3.dump. This may take several minutes.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Database successfully backed up to /var/tmp/zm/zm-1.39.3.dump, proceeding to upgrade.

Upgrading database to version 1.39.3
Upgrading DB to 1.39.3 from 1.39.3
mysql: [Warning] Using a password on the command line interface can be insecure.

Database successfully upgraded to version 1.39.3.
Upgrading DB to 1.39.4 from 1.39.3
mysql: [Warning] Using a password on the command line interface can be insecure.

Database successfully upgraded to version 1.39.4.
Upgrading DB to 1.39.5 from 1.39.3
mysql: [Warning] Using a password on the command line interface can be insecure.

Database successfully upgraded to version 1.39.5.

Database upgrade to version 1.39.3 successful.

There are also problems with cameras:

27.04.2026 AD 16:20:37 | zmdc | 1084 | ERR | 'zmc -m 4' exited abnormally, exit status 255 | zmdc.pl | 763

[IgorA100]

It turns out I also needed to run "zmupdate.pl -f"
Because the logs said:

2026-04-27T18:15:25.127297+03:00 zoneminder zmc_m4[9456]: ERR [zmc_m4] [Attempt to fetch boolean value for ZM_FILTER_EXECUTE_INTERVAL, actual type is integer. Try running 'zmupdate.pl -f' to reload config.]
2026-04-27T18:15:25.169786+03:00 zoneminder zmdc[1084]: ERR ['zmc -m 4' exited abnormally, exit status 255]

[alien8r]

Is the issue caused by specific versions of mysql?
(If so can someone post a method for adding this column manually?)

Update 1.39.5 fails on Ubuntu 24.04.4 LTS with mysql-server version 8.0.45-0ubuntu0.24.04.1.
Specific error message is:

ERROR 1064 (42000) at line 6: 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 '))' at line 10
Output: Checking for WhatDisplay in Monitors

[net2pw]

The following works for me at the mariadb prompt and connected to the zm database (need to check if apostrophes or back single quotes)

MariaDB [zm]> ALTER TABLE Monitors ADD COLUMN WhatDisplay enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo' AFTER Decoding;

[IgorA100]

ERROR 1064

PR #4756 was created to fix the bug related to the missing comma.

[alien8r]

Thank you [net2pw]
Procedure I followed was:

mysql -u'-ID-' -p'-PW-'

mysql> use zm

mysql> ALTER TABLE Monitors ADD COLUMN `WhatDisplay` enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo' AFTER `Decoding`;

Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE Monitors MODIFY `WhatDisplay` enum('OnlyVideo','OnlyAudioVisualization','VideoAudioVisualization') NOT NULL DEFAULT 'OnlyVideo';

Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> exit

---

Note that unlike the script I did the ADD COLUMN before the MODIFY.....
No warnings so I figure those worked.
Now zmupdate.pl finishes without having to do anything.

[IgorA100]

@alien8r
If you already had a WhatDisplay column, but it was the wrong type, then you only need to run MODIFY.
If the column didn't exist, then you need to run ADD COLUMN.