zmupdate.pl syntax error?

[DrJohn999]

I upgraded from 1.30.4 to 1.34.22 today. The upgrade sequence started at Ubuntu 16.04 and included do-release-upgrade twice to go to 18.04 and then to 20.04 I then downloaded and ran the zm-install script, which ran to completion but the zoneminder service did not start, and the configuration was incomplete:

Code: Select all

root@c2550:/usr/local/lib/zoneminder# aptitude show zoneminder
Package: zoneminder
Version: 1.34.22-focal1
State: partially configured
Automatically installed: no
Priority: optional
Section: net
Maintainer: Isaac Connor <isaac@zoneminder.com>
Architecture: amd64

Apache2 served up the web pages, and I was able to correct the time zone, which appeared to go into the db OK. However, the zoneminder service failed to start manually.

I found the query_cache_limit= problem with upgrading to mysql 8.0 (see https://bugs.launchpad.net/ubuntu/+sour ... ug/1850895) and removed that from the configuration, so that mysql could start at all.

When rebooting and trying to start zoneminder it then became apparent that the database upgrade had failed during installation:

Code: Select all

root@c2550:/usr/local/lib/zoneminder# systemctl status zoneminder
● zoneminder.service - ZoneMinder CCTV recording and surveillance system
     Loaded: loaded (/lib/systemd/system/zoneminder.service; enabled; vendor preset: enabled)
     Active: activating (auto-restart) (Result: exit-code) since Sun 2020-11-22 20:02:28 PST; 7s ago
    Process: 11058 ExecStart=/usr/bin/zmpkg.pl start (code=exited, status=255/EXCEPTION)

root@c2550:/usr/local/lib/zoneminder# journalctl -xe
Nov 22 20:03:11 c2550 zmpkg[11159]: FAT [Version mismatch, system is version 1.34.22, database is 1.30.4, please ru>
Nov 22 20:03:11 c2550 systemd[1]: zoneminder.service: Control process exited, code=exited, status=255/EXCEPTION
-- Subject: Unit process exited
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- An ExecStart= process belonging to unit zoneminder.service has exited.
--
-- The process' exit code is 'exited' and its exit status is 255.
Nov 22 20:03:11 c2550 systemd[1]: zoneminder.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- The unit zoneminder.service has entered the 'failed' state with result 'exit-code'.
Nov 22 20:03:11 c2550 systemd[1]: Failed to start ZoneMinder CCTV recording and surveillance system.
-- Subject: A start job for unit zoneminder.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A start job for unit zoneminder.service has finished with a failure.
--
-- The job identifier is 2847 and the job result is failed.
lines 3690-3712/3712 (END)
Nov 22 20:03:11 c2550 zmpkg[11159]: FAT [Version mismatch, system is version 1.34.22, database is 1.30.4, please run zmupdate.pl to update.]
Nov 22 20:03:11 c2550 systemd[1]: zoneminder.service: Control process exited, code=exited, status=255/EXCEPTION
-- Subject: Unit process exited
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- An ExecStart= process belonging to unit zoneminder.service has exited.
--
-- The process' exit code is 'exited' and its exit status is 255.
Nov 22 20:03:11 c2550 systemd[1]: zoneminder.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- The unit zoneminder.service has entered the 'failed' state with result 'exit-code'.
Nov 22 20:03:11 c2550 systemd[1]: Failed to start ZoneMinder CCTV recording and surveillance system.
-- Subject: A start job for unit zoneminder.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A start job for unit zoneminder.service has finished with a failure.
--
-- The job identifier is 2847 and the job result is failed.

Thinking this had happened due to the query_cache_limit= business, I ran zmupdate.pl, which resulted in a syntax error when stepping to 1.31.5:

Code: Select all

root@c2550:/usr/local/lib/zoneminder# /usr/bin/zmupdate.pl

Initiating database upgrade to version 1.34.22 from version 1.30.4

Please ensure that ZoneMinder is stopped on your system prior to upgrading the database.
Press enter to continue or ctrl-C to stop :

Do you wish to take a backup of your database prior to upgrading?
This may result in a large file in /tmp/zm if you have a lot of events.
Press 'y' for a backup or 'n' to continue : n

Upgrading database to version 1.34.22
Loading config from DB 230 entries
Saving config to DB 230 entries
Upgrading DB to 1.31.0 from 1.30.4
mysql: [Warning] Using a password on the command line interface can be insecure.

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

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

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

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

Database successfully upgraded to version 1.31.4.
Upgrading DB to 1.31.5 from 1.30.4
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 16: 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 'Groups ADD `ParentId` int(10) unsigned AFTER `Name`' at line 1
Output:
Command 'mysql -hlocalhost -uzmuser -p'zmpass' zm < /usr/share/zoneminder/db/zm_update-1.31.5.sql' exited with status: 1
root@c2550:/usr/local/lib/zoneminder#

Just to be sure I removed and reinstalled zoneminder, but no joy.

Is there a fix for this? Zeroing the db would wipe the camera configurations, which I could recreate easily enough, but this issue may be of interest to others as well. Thanks!

[Magic919]

It's the newer DB that's behind this error. There are reserved keywords that can't be used https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Looks like 'Groups' is the problem here. I'd presume you can escape them.

[DrJohn999]

This isn't my script; it's part of the default distro from iconner's zoneminder-1.34 ppa.

[Magic919]

Yes, I know.

[iconnor]

Hey thanks for the heads up. I'll try to get this fixed ... by Wednesday.

In the meantime, you can edit /usr/share/zoneminder/db/zm_update-1.31.5.sql line 16 and put ` backticks around any column or table names.

THen re-run zmupdate.pl.

[DrJohn999]

So I patched the failing sql update scripts zm_update-1.31.5.sql, ..1.31.7.sql, and ..1.31.16.sql by escaping instances of Groups to `Groups`. The rest of them at higher db revisions don't reference Groups.
This seemed to work but zm_update-1.31.19.sql fails with a different error

Code: Select all

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1419 (HY000) at line 25: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Output:
Command 'mysql -hlocalhost -uzmuser -p'zmpass' zm < /usr/share/zoneminder/db/zm_update-1.31.19.sql' exited with status: 1

This one is clear. There appear to be two solutions, either

Code: Select all

 mysql> SET GLOBAL log_bin_trust_function_creators = 1;

or by disabling the binary log option. (From https://dev.mysql.com/doc/refman/8.0/en ... gging.html)
In the context of Zoneminder I'm not sure which or if either is the correct solution. Any ideas?

Thanks

[Magic919]

This thread took a different approach viewtopic.php?t=27811

[iconnor]

The

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Is a temporary setting so maybe that is better. The mysql user permissions thing is interesting... our package should auto-update the zmuser permissions when installing the package so that is... interesting.

[DrJohn999]

SET GLOBAL log_bin_trust_function_creators = 1;
did the trick, thanks!! Nice to have the cameras, events, and the rest of the configuration come over into the updated UI.