Ubuntu
zoneminder package

Merge ~ahasenack/ubuntu/+source/zoneminder:focal-zoneminder-mysql8 into ~ahasenack/ubuntu/+source/zoneminder:master

  1. Git
  2. lp:~ahasenack/ubuntu/+source/zoneminder
  3. focal-zoneminder-mysql8
  4. Merge into master
Proposed by Andreas Hasenack
Status: Merged
Approved by: Andreas Hasenack
Approved revision: 6dfc7d363f874e9417bd563ed95c96031ae9568b
Merged at revision: ebdcb0ab8587f01cf4c6b448bf0d5773e2be7dcf
Proposed branch: ~ahasenack/ubuntu/+source/zoneminder:focal-zoneminder-mysql8
Merge into: ~ahasenack/ubuntu/+source/zoneminder:master
Diff against target: 569 lines (+484/-4)
9 files modified
debian/README.Debian (+9/-2)
debian/changelog (+14/-0)
debian/control (+2/-1)
debian/patches/mysql8-password-deprecated.patch (+26/-0)
debian/patches/mysql8-replace-password-call.patch (+58/-0)
debian/patches/mysql8-reserved-keywords.patch (+326/-0)
debian/patches/mysql8_my_bool.patch (+43/-0)
debian/patches/series (+4/-0)
debian/rules (+2/-1)
Reviewer Review Type Date Requested Status
Christian Ehrhardt  (community) Approve
Canonical Server Pending
Review via email: mp+379771@code.launchpad.net

Description of the change

PPA: https://launchpad.net/~ahasenack/+archive/ubuntu/zoneminder-mysql8/

zoneminder isn't in git-ubuntu, and currently fails to import (see bug #1864255).

This fixes several things:
- ftbfs due to mysql8
- another unrelated ftbfs
- runtime issue with mysql8

Newer upstream version claims to work with mysql8, but debian hasn't grabbed it yet. One of the upstream fixes isn't really a good candidate for a patch here, as it's a kind of a refactoring (getting rid of mysql's password()).

I decided to update d/README.Debian a bit because the instructions there didn't work out of the box, and I wanted to try out the package to avoid another bug like #1859295). At least now it comes up and one can see its web interface at http://<ip>/zm. I also updated the php links, expecting 7.4 for focal.

The auth issue workaround I gathered from https://forums.zoneminder.com/viewtopic.php?t=28742

I could add a simple dep8 test without too much extra effort, and that would be one that simply loaded the sql dump into mysql: cat /usr/share/zoneminder/db/zm_create.sql | sudo mysql

It would have caught one issue (using password() in that sql file), but not the rest. Let me know if you think it's worth it. I didn't do it now because it's late :)

All those other steps from the README.Debian file are also doable as a dep8 test, up to the license displayed at first in http://<ip>/zm. We can still do it after feature freeze.

To post a comment you must log in.
Revision history for this message
Christian Ehrhardt  (paelzer) wrote :

I don't think we need to add the test for it, would it be nice: yes; is it really necessary: no.
At least not needed right now.

The mysql8 patches are a bit messy, but I agree that it does the job in as-minimal-as-possible fashion. And when moving to the next version it can be dropped.

Changelog:
- [√] changelog entry correct version and targeted codename
- [√] changelog entries correct
- [√] update-maintainer has been run

Actual changes:
- [√] no further upstream version that we want right now

New Delta:
- [√] new patches are good and while not matching the final way they are from upstream and do the job for now.
- [√] new patches correctly included in debian/patches/series
- [√] new patches have correct DEP3 metadata linking to discussions and such

Build/Test:
- [√] build is ok
- [√] sanity checks test fine

I tested it with mythzoneminder and the basics there worked - if we could long term add an autopkgtest that would be good (as discussed above).

I ran this with a mix of php 7.4 and zoneminder to represent how focal will soon look like:
... mysql-server-8.0 mysql-server-core-8.0 ocl-icd-libopencl1 php-apcu php-apcu-bc php-gd php-mysql php7.3-cli php7.3-common php7.3-json php7.3-opcache
  php7.3-phpdbg php7.3-readline php7.4-gd php7.4-mysql zoneminder zoneminder-doc ...
All the perl installs also looked a lot.
But so far things worked.

One thing you might want to look at before an upload is this (up to you):
Setting up zoneminder (1.32.3-2ubuntu1~ppa2) ...
I: Generating /etc/zm/core.php from /usr/share/zoneminder/www/api/app/Config/core.php.default ...
zoneminder.conf:1: Line references path below legacy directory /var/run/, updating /var/run/zm → /run/zm; please update the tmpfiles.d/ drop-in file accordingly.

It feels a bit less stable than usual, but ok still: +1 for this

review: Approve
Revision history for this message
Andreas Hasenack (ahasenack) wrote :

Thanks, I filed https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1864645 for the dep8 test.

I'll take a look at the tmpfile bit

Revision history for this message
Andreas Hasenack (ahasenack) wrote :

Hmm, RUNDIR is set all over the place at /var/run, even in d/rules, just changing it in the systemd tmpfile would be incomplete, and I'm not ready to test such a larger change at this moment. I'll drop that bit for now, and file a bug to do it properly.

Revision history for this message
Andreas Hasenack (ahasenack) wrote :

https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1864653

Revision history for this message
Andreas Hasenack (ahasenack) wrote :

Uploading (no tagging, since this isn't in g-u) ebdcb0ab8587f01cf4c6b448bf0d5773e2be7dcf

$ dput ubuntu ../zoneminder_1.32.3-2ubuntu1_source.changes
Checking signature on .changes
gpg: ../zoneminder_1.32.3-2ubuntu1_source.changes: Valid signature from AC983EB5BF6BCBA9
Checking signature on .dsc
gpg: ../zoneminder_1.32.3-2ubuntu1.dsc: Valid signature from AC983EB5BF6BCBA9
Uploading to ubuntu (via ftp to upload.ubuntu.com):
  Uploading zoneminder_1.32.3-2ubuntu1.dsc: done.
  Uploading zoneminder_1.32.3-2ubuntu1.debian.tar.xz: done.
  Uploading zoneminder_1.32.3-2ubuntu1_source.buildinfo: done.
  Uploading zoneminder_1.32.3-2ubuntu1_source.changes: done.
Successfully uploaded packages.

Revision history for this message
Joseph Yasi (joe-yasi) :
Revision history for this message
Andreas Hasenack (ahasenack) :

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
diff --git a/debian/README.Debian b/debian/README.Debian
index 712526a..1ee8bb5 100644
--- a/debian/README.Debian
+++ b/debian/README.Debian
@@ -11,6 +11,13 @@ OR
11 echo 'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost identified by "zmpass";'\11 echo 'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost identified by "zmpass";'\
12 | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql12 | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
1313
14With MySQL8:
15
16 echo "CREATE USER 'zmuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zmpass';"\
17 | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
18 echo 'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost;'\
19 | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
20
14Hint: generate secure password with `pwgen` and update "/etc/zm/zm.conf"21Hint: generate secure password with `pwgen` and update "/etc/zm/zm.conf"
15accordingly.22accordingly.
1623
@@ -128,8 +135,8 @@ System time zone can be set by the following command:
128PHP time zone can be configured by changing value of "date.timezone" in one135PHP time zone can be configured by changing value of "date.timezone" in one
129of the following files:136of the following files:
130137
131 /etc/php/7.2/fpm/php.ini138 /etc/php/7.4/fpm/php.ini
132 /etc/php/7.2/apache2/php.ini139 /etc/php/7.4/apache2/php.ini
133140
134Please note that MariaDB/MySQL time zone should be identical to system and141Please note that MariaDB/MySQL time zone should be identical to system and
135PHP time zones. By default MariaDB uses system time zone but it might be142PHP time zones. By default MariaDB uses system time zone but it might be
diff --git a/debian/changelog b/debian/changelog
index 68b6007..3d575f6 100644
--- a/debian/changelog
+++ b/debian/changelog
@@ -1,3 +1,17 @@
1zoneminder (1.32.3-2ubuntu1) focal; urgency=medium
2
3 * d/p/mysql8_my_bool.patch: Reintroduce my_bool to fix build with MySQL 8.
4 (LP: #1863026)
5 * d/rules: override build-dep to fix FTBFS (thanks to
6 robie.basak@canonical.com) (LP: #1863793, Closes: #951600)
7 * MySQL8 runtime fixes (LP: #1859295):
8 - d/p/mysql8-password-deprecated.patch: password() is deprecated
9 - d/p/mysql8-reserved-keywords.patch: quote MySQL8 reserved keywords
10 - d/p/mysql8-replace-password-call.patch: replace password()
11 * d/README.Debian: quick update for MySQL8 and Focal
12
13 -- Andreas Hasenack <andreas@canonical.com> Tue, 25 Feb 2020 10:22:20 -0300
14
1zoneminder (1.32.3-2build1) eoan; urgency=medium15zoneminder (1.32.3-2build1) eoan; urgency=medium
216
3 * No change rebuild for libmysqlclient21.17 * No change rebuild for libmysqlclient21.
diff --git a/debian/control b/debian/control
index d435117..c561ce0 100644
--- a/debian/control
+++ b/debian/control
@@ -2,7 +2,8 @@ Source: zoneminder
2Section: net2Section: net
3Priority: optional3Priority: optional
4Standards-Version: 4.3.04Standards-Version: 4.3.0
5Maintainer: Dmitry Smirnov <onlyjob@debian.org>5Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
6XSBC-Original-Maintainer: Dmitry Smirnov <onlyjob@debian.org>
6Uploaders: Chris Lamb <lamby@debian.org>7Uploaders: Chris Lamb <lamby@debian.org>
7Build-Depends: debhelper (>= 11~) ,dh-linktree8Build-Depends: debhelper (>= 11~) ,dh-linktree
8 ,apache2-dev9 ,apache2-dev
diff --git a/debian/patches/mysql8-password-deprecated.patch b/debian/patches/mysql8-password-deprecated.patch
9new file mode 10064410new file mode 100644
index 0000000..f16dc63
--- /dev/null
+++ b/debian/patches/mysql8-password-deprecated.patch
@@ -0,0 +1,26 @@
1From 5b896b5e5ca57fe9780e852d570a31c7a5b2ecfb Mon Sep 17 00:00:00 2001
2From: Isaac Connor <isaac@zoneminder.com>
3Date: Mon, 8 Jul 2019 14:16:57 -0400
4Subject: [PATCH] Replace password('admin') with the resulting string because
5 use of password is deprecated
6
7---
8 db/zm_create.sql.in | 2 +-
9 1 file changed, 1 insertion(+), 1 deletion(-)
10
11Origin: https://github.com/ZoneMinder/zoneminder/commit/5b896b5e5ca57fe9780e852d570a31c7a5b2ecfb
12Bug: https://github.com/ZoneMinder/zoneminder/issues/2658
13Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
14Last-Updated: 2020-02-21
15
16--- a/db/zm_create.sql.in
17+++ b/db/zm_create.sql.in
18@@ -738,7 +738,7 @@
19 --
20 -- Create a default admin user.
21 --
22-insert into Users VALUES (NULL,'admin',password('admin'),'',1,'View','Edit','Edit','Edit','Edit','Edit','Edit','','');
23+insert into Users VALUES (NULL,'admin','$2b$12$NHZsm6AM2f2LQVROriz79ul3D6DnmFiZC.ZK5eqbF.ZWfwH9bqUJ6','',1,'View','Edit','Edit','Edit','Edit','Edit','Edit','','');
24
25 --
26 -- Add a sample filter to purge the oldest 100 events when the disk is 95% full
diff --git a/debian/patches/mysql8-replace-password-call.patch b/debian/patches/mysql8-replace-password-call.patch
0new file mode 10064427new file mode 100644
index 0000000..574bf24
--- /dev/null
+++ b/debian/patches/mysql8-replace-password-call.patch
@@ -0,0 +1,58 @@
1Description: replace password()
2 password() is deprecated in MySQL8, replace it with an equivalent implementation:
3 .
4 mysql> select password('secret'),CONCAT('*',UPPER(SHA1(UNHEX(SHA1('secret')))));
5 +-------------------------------------------+------------------------------------------------+
6 | password('secret') | CONCAT('*',UPPER(SHA1(UNHEX(SHA1('secret'))))) |
7 +-------------------------------------------+------------------------------------------------+
8 | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 |
9 +-------------------------------------------+------------------------------------------------+
10Author: Joseph Yasi <joe.yasi@gmail.com>
11Origin: other, https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295/comments/1
12Bug: https://github.com/ZoneMinder/zoneminder/issues/2659
13Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
14Last-Update: 2020-02-24
15---
16This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
17--- a/src/zm_user.cpp
18+++ b/src/zm_user.cpp
19@@ -99,7 +99,7 @@
20 int password_length = strlen(password);
21 char *safer_password = new char[(password_length * 2) + 1];
22 mysql_real_escape_string(&dbconn, safer_password, password, password_length);
23- snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds from Users where Username = '%s' and Password = password('%s') and Enabled = 1", safer_username, safer_password );
24+ snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds from Users where Username = '%s' and Password = CONCAT('*',UPPER(SHA1(UNHEX(SHA1('%s'))))) and Enabled = 1", safer_username, safer_password );
25 delete safer_password;
26 } else {
27 snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds from Users where Username = '%s' and Enabled = 1", safer_username );
28--- a/web/includes/actions.php
29+++ b/web/includes/actions.php
30@@ -960,7 +960,7 @@
31 $changes = getFormChanges($dbUser, $_REQUEST['newUser'], $types);
32
33 if ( $_REQUEST['newUser']['Password'] )
34- $changes['Password'] = 'Password = password('.dbEscape($_REQUEST['newUser']['Password']).')';
35+ $changes['Password'] = 'Password = CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1('.dbEscape($_REQUEST['newUser']['Password']).')))))';
36 else
37 unset($changes['Password']);
38
39@@ -1015,7 +1015,7 @@
40 $changes = getFormChanges($dbUser, $_REQUEST['newUser'], $types);
41
42 if ( !empty($_REQUEST['newUser']['Password']) )
43- $changes['Password'] = 'Password = password('.dbEscape($_REQUEST['newUser']['Password']).')';
44+ $changes['Password'] = 'Password = CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1('.dbEscape($_REQUEST['newUser']['Password']).')))))';
45 else
46 unset($changes['Password']);
47 if ( count($changes) ) {
48--- a/web/includes/auth.php
49+++ b/web/includes/auth.php
50@@ -27,7 +27,7 @@
51 if ( $passwordHashed ) {
52 $sql .= ' AND Username=? AND Password=?';
53 } else {
54- $sql .= ' AND Username=? AND Password=password(?)';
55+ $sql .= ' AND Username=? AND Password=CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1(?)))))';
56 }
57 $sql_values = array($username, $password);
58 } else {
diff --git a/debian/patches/mysql8-reserved-keywords.patch b/debian/patches/mysql8-reserved-keywords.patch
0new file mode 10064459new file mode 100644
index 0000000..bc067d1
--- /dev/null
+++ b/debian/patches/mysql8-reserved-keywords.patch
@@ -0,0 +1,326 @@
1Description: Quote MySQL8 reserved keywords
2Author: Joseph Yasi <joe.yasi@gmail.com>
3Origin: other, https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295/comments/1
4Bug: https://github.com/ZoneMinder/zoneminder/issues/2659
5Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
6Last-Update: 2020-02-21
7---
8This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
9--- a/scripts/zmtelemetry.pl.in
10+++ b/scripts/zmtelemetry.pl.in
11@@ -243,7 +243,7 @@
12 sub getMonitorRef {
13 my $dbh = shift;
14
15- my $sql = 'SELECT Id,Name,Type,Function,Width,Height,Colours,MaxFPS,AlarmMaxFPS FROM Monitors';
16+ my $sql = 'SELECT Id,Name,Type,`Function`,Width,Height,Colours,MaxFPS,AlarmMaxFPS FROM Monitors';
17 my $sth = $dbh->prepare_cached( $sql ) or die( "Can't prepare '$sql': ".$dbh->errstr() );
18 my $res = $sth->execute() or die( "Can't execute: ".$sth->errstr() );
19 my $arrayref = $sth->fetchall_arrayref({});
20--- a/scripts/zmtrigger.pl.in
21+++ b/scripts/zmtrigger.pl.in
22@@ -333,7 +333,7 @@
23 my %new_monitors = ();
24
25 my $sql = "SELECT * FROM Monitors
26- WHERE find_in_set( Function, 'Modect,Mocord,Nodect' )".
27+ WHERE find_in_set( `Function`, 'Modect,Mocord,Nodect' )".
28 ( $Config{ZM_SERVER_ID} ? 'AND ServerId=?' : '' )
29 ;
30 my $sth = $dbh->prepare_cached( $sql )
31--- a/scripts/zmx10.pl.in
32+++ b/scripts/zmx10.pl.in
33@@ -464,7 +464,7 @@
34
35 my $sql = "SELECT M.*,T.* from Monitors as M
36 INNER JOIN TriggersX10 as T on (M.Id = T.MonitorId)
37- WHERE find_in_set( M.Function, 'Modect,Record,Mocord,Nodect' )
38+ WHERE find_in_set( M.`Function`, 'Modect,Record,Mocord,Nodect' )
39 AND M.Enabled = 1
40 AND find_IN_set( 'X10', M.Triggers )"
41 ;
42--- a/scripts/ZoneMinder/lib/ZoneMinder/Database.pm
43+++ b/scripts/ZoneMinder/lib/ZoneMinder/Database.pm
44@@ -145,15 +145,15 @@
45
46 if ( $function ) {
47 if ( $function == DB_MON_CAPT ) {
48- $sql .= " where Function >= 'Monitor'";
49+ $sql .= " where `Function` >= 'Monitor'";
50 } elsif ( $function == DB_MON_ACTIVE ) {
51- $sql .= " where Function > 'Monitor'";
52+ $sql .= " where `Function` > 'Monitor'";
53 } elsif ( $function == DB_MON_MOTION ) {
54- $sql .= " where Function = 'Modect' or Function = 'Mocord'";
55+ $sql .= " where `Function` = 'Modect' or `Function` = 'Mocord'";
56 } elsif ( $function == DB_MON_RECORD ) {
57- $sql .= " where Function = 'Record' or Function = 'Mocord'";
58+ $sql .= " where `Function` = 'Record' or `Function` = 'Mocord'";
59 } elsif ( $function == DB_MON_PASSIVE ) {
60- $sql .= " where Function = 'Nodect'";
61+ $sql .= " where `Function` = 'Nodect'";
62 }
63 }
64 my $sth = $dbh->prepare_cached( $sql );
65--- a/src/zm_monitor.cpp
66+++ b/src/zm_monitor.cpp
67@@ -67,7 +67,7 @@
68 // This is the official SQL (and ordering of the fields) to load a Monitor.
69 // It will be used whereever a Monitor dbrow is needed. WHERE conditions can be appended
70 std::string load_monitor_sql =
71-"SELECT Id, Name, ServerId, StorageId, Type, Function+0, Enabled, LinkedMonitors, "
72+"SELECT Id, Name, ServerId, StorageId, Type, `Function`+0, Enabled, LinkedMonitors, "
73 "AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS,"
74 "Device, Channel, Format, V4LMultiBuffer, V4LCapturesPerFrame, " // V4L Settings
75 "Protocol, Method, Options, User, Pass, Host, Port, Path, Width, Height, Colours, Palette, Orientation+0, Deinterlacing, RTSPDescribe, "
76@@ -1770,7 +1770,7 @@
77
78 static char sql[ZM_SQL_MED_BUFSIZ];
79 // This seems to have fallen out of date.
80- snprintf( sql, sizeof(sql), "select Function+0, Enabled, LinkedMonitors, EventPrefix, LabelFormat, LabelX, LabelY, LabelSize, WarmupCount, PreEventCount, PostEventCount, AlarmFrameCount, SectionLength, FrameSkip, MotionFrameSkip, AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS, FPSReportInterval, RefBlendPerc, AlarmRefBlendPerc, TrackMotion, SignalCheckColour from Monitors where Id = '%d'", id );
81+ snprintf( sql, sizeof(sql), "select `Function`+0, Enabled, LinkedMonitors, EventPrefix, LabelFormat, LabelX, LabelY, LabelSize, WarmupCount, PreEventCount, PostEventCount, AlarmFrameCount, SectionLength, FrameSkip, MotionFrameSkip, AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS, FPSReportInterval, RefBlendPerc, AlarmRefBlendPerc, TrackMotion, SignalCheckColour from Monitors where Id = '%d'", id );
82
83 zmDbRow *row = zmDbFetchOne(sql);
84 if ( !row ) {
85@@ -1904,7 +1904,7 @@
86
87 db_mutex.lock();
88 static char sql[ZM_SQL_SML_BUFSIZ];
89- snprintf(sql, sizeof(sql), "select Id, Name from Monitors where Id = %d and Function != 'None' and Function != 'Monitor' and Enabled = 1", link_ids[i] );
90+ snprintf(sql, sizeof(sql), "select Id, Name from Monitors where Id = %d and `Function` != 'None' and `Function` != 'Monitor' and Enabled = 1", link_ids[i] );
91 if ( mysql_query(&dbconn, sql) ) {
92 Error("Can't run query: %s", mysql_error(&dbconn));
93 db_mutex.unlock();
94@@ -1961,7 +1961,7 @@
95 #if ZM_HAS_V4L
96 int Monitor::LoadLocalMonitors( const char *device, Monitor **&monitors, Purpose purpose ) {
97
98- std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Local'";
99+ std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Local'";
100
101 if ( device[0] )
102 sql += " AND Device='" + std::string(device) + "'";
103@@ -1972,7 +1972,7 @@
104 #endif // ZM_HAS_V4L
105
106 int Monitor::LoadRemoteMonitors( const char *protocol, const char *host, const char *port, const char *path, Monitor **&monitors, Purpose purpose ) {
107- std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Remote'";
108+ std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Remote'";
109 if ( staticConfig.SERVER_ID )
110 sql += stringtf( " AND ServerId=%d", staticConfig.SERVER_ID );
111
112@@ -1982,7 +1982,7 @@
113 }
114
115 int Monitor::LoadFileMonitors( const char *file, Monitor **&monitors, Purpose purpose ) {
116- std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'File'";
117+ std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'File'";
118 if ( file[0] )
119 sql += " AND Path='" + std::string(file) + "'";
120 if ( staticConfig.SERVER_ID ) {
121@@ -1993,7 +1993,7 @@
122
123 #if HAVE_LIBAVFORMAT
124 int Monitor::LoadFfmpegMonitors(const char *file, Monitor **&monitors, Purpose purpose) {
125- std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Ffmpeg'";
126+ std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Ffmpeg'";
127 if ( file[0] )
128 sql += " AND Path = '" + std::string(file) + "'";
129
130@@ -2006,7 +2006,7 @@
131
132 /*
133 std::string load_monitor_sql =
134- "SELECT Id, Name, ServerId, StorageId, Type, Function+0, Enabled, LinkedMonitors, "
135+ "SELECT Id, Name, ServerId, StorageId, Type, `Function`+0, Enabled, LinkedMonitors, "
136 "AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS,"
137 "Device, Channel, Format, V4LMultiBuffer, V4LCapturesPerFrame, " // V4L Settings
138 "Protocol, Method, Options, User, Pass, Host, Port, Path, Width, Height, Colours, Palette, Orientation+0, Deinterlacing, RTSPDescribe, "
139@@ -2811,7 +2811,7 @@
140 // At the moment, only load groups once.
141 if ( ! groups.size() ) {
142 std::string sql = stringtf(
143- "SELECT Id,ParentId,Name FROM Groups WHERE Groups.Id IN "
144+ "SELECT Id,ParentId,Name FROM `Groups` WHERE `Groups`.Id IN "
145 "(SELECT GroupId FROM Groups_Monitors WHERE MonitorId=%d)",id);
146 MYSQL_RES *result = zmDbFetch(sql.c_str());
147 if ( !result ) {
148--- a/src/zm_rtsp.cpp
149+++ b/src/zm_rtsp.cpp
150@@ -93,7 +93,7 @@
151 int RtspThread::requestPorts() {
152 if ( !smMinDataPort ) {
153 char sql[ZM_SQL_SML_BUFSIZ];
154- strncpy( sql, "select Id from Monitors where Function != 'None' and Type = 'Remote' and Protocol = 'rtsp' and Method = 'rtpUni' order by Id asc", sizeof(sql) );
155+ strncpy( sql, "select Id from Monitors where `Function` != 'None' and Type = 'Remote' and Protocol = 'rtsp' and Method = 'rtpUni' order by Id asc", sizeof(sql) );
156 if ( mysql_query( &dbconn, sql ) ) {
157 Error( "Can't run query: %s", mysql_error( &dbconn ) );
158 exit( mysql_errno( &dbconn ) );
159--- a/src/zmu.cpp
160+++ b/src/zmu.cpp
161@@ -691,9 +691,9 @@
162 }
163
164 if ( function & ZMU_LIST ) {
165- std::string sql = "select Id, Function+0 from Monitors";
166+ std::string sql = "select Id, `Function`+0 from Monitors";
167 if ( !verbose ) {
168- sql += "where Function != 'None'";
169+ sql += "where `Function` != 'None'";
170 }
171 sql += " order by Id asc";
172
173--- a/src/zm_user.cpp
174+++ b/src/zm_user.cpp
175@@ -99,10 +99,10 @@
176 int password_length = strlen(password);
177 char *safer_password = new char[(password_length * 2) + 1];
178 mysql_real_escape_string(&dbconn, safer_password, password, password_length);
179- snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, System+0, MonitorIds from Users where Username = '%s' and Password = password('%s') and Enabled = 1", safer_username, safer_password );
180+ snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds from Users where Username = '%s' and Password = password('%s') and Enabled = 1", safer_username, safer_password );
181 delete safer_password;
182 } else {
183- snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, System+0, MonitorIds from Users where Username = '%s' and Enabled = 1", safer_username );
184+ snprintf( sql, sizeof(sql), "select Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds from Users where Username = '%s' and Enabled = 1", safer_username );
185 }
186
187 if ( mysql_query( &dbconn, sql ) ) {
188@@ -157,7 +157,7 @@
189
190 Debug( 1, "Attempting to authenticate user from auth string '%s'", auth );
191 char sql[ZM_SQL_SML_BUFSIZ] = "";
192- snprintf( sql, sizeof(sql), "SELECT Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, System+0, MonitorIds FROM Users WHERE Enabled = 1" );
193+ snprintf( sql, sizeof(sql), "SELECT Id, Username, Password, Enabled, Stream+0, Events+0, Control+0, Monitors+0, `System`+0, MonitorIds FROM Users WHERE Enabled = 1" );
194
195 if ( mysql_query( &dbconn, sql ) ) {
196 Error( "Can't run query: %s", mysql_error( &dbconn ) );
197--- a/utils/zm-alarm.pl
198+++ b/utils/zm-alarm.pl
199@@ -16,7 +16,7 @@
200 my $dbh = zmDbConnect();
201
202 my $sql = "SELECT * FROM Monitors
203- WHERE find_in_set( Function, 'Modect,Mocord,Nodect' )".
204+ WHERE find_in_set( `Function`, 'Modect,Mocord,Nodect' )".
205 ( $Config{ZM_SERVER_ID} ? 'AND ServerId=?' : '' )
206 ;
207
208--- a/web/ajax/status.php
209+++ b/web/ajax/status.php
210@@ -16,7 +16,7 @@
211 'limit' => 1,
212 'elements' => array(
213 'MonitorCount' => array( 'sql' => 'count(*)' ),
214- 'ActiveMonitorCount' => array( 'sql' => "count(if(Function != 'None',1,NULL))" ),
215+ 'ActiveMonitorCount' => array( 'sql' => "count(if(`Function` != 'None',1,NULL))" ),
216 'State' => array( 'func' => 'daemonCheck()?'.translate('Running').':'.translate('Stopped') ),
217 'Load' => array( 'func' => 'getLoad()' ),
218 'Disk' => array( 'func' => 'getDiskPercent()' ),
219--- a/web/includes/database.php
220+++ b/web/includes/database.php
221@@ -227,7 +227,7 @@
222 }
223
224 function getEnumValues( $table, $column ) {
225- $row = dbFetchOne( "describe $table $column" );
226+ $row = dbFetchOne( "describe `$table` `$column`" );
227 preg_match_all( "/'([^']+)'/", $row['Type'], $matches );
228 return( $matches[1] );
229 }
230@@ -238,7 +238,7 @@
231
232 function getUniqueValues( $table, $column, $asString=1 ) {
233 $values = array();
234- $sql = "select distinct $column from $table where (not isnull($column) and $column != '') order by $column";
235+ $sql = "select distinct `$column` from `$table` where (not isnull(`$column`) and `$column` != '') order by `$column`";
236 foreach( dbFetchAll( $sql ) as $row ) {
237 if ( $asString )
238 $values[$row[$column]] = $row[$column];
239@@ -250,7 +250,7 @@
240
241 function getTableColumns( $table, $asString=1 ) {
242 $columns = array();
243- $sql = "describe $table";
244+ $sql = "describe `$table`";
245 foreach( dbFetchAll( $sql ) as $row ) {
246 if ( $asString )
247 $columns[$row['Field']] = $row['Type'];
248@@ -267,7 +267,7 @@
249
250 function getTableDescription( $table, $asString=1 ) {
251 $columns = array();
252- foreach( dbFetchAll( "describe $table" ) as $row ) {
253+ foreach( dbFetchAll( "describe `$table`" ) as $row ) {
254 $desc = array(
255 'name' => $row['Field'],
256 'required' => ($row['Null']=='NO')?true:false,
257@@ -377,7 +377,7 @@
258 }
259
260 function dbFetchGroup( $gid ) {
261- return( dbFetchOne( 'select * from Groups where Id = ?', NULL, array($gid) ) );
262+ return( dbFetchOne( 'select * from `Groups` where Id = ?', NULL, array($gid) ) );
263 }
264
265 ?>
266--- a/web/includes/Group.php
267+++ b/web/includes/Group.php
268@@ -16,7 +16,7 @@
269 $row = NULL;
270 if ( $IdOrRow ) {
271 if ( is_integer($IdOrRow) or is_numeric($IdOrRow) ) {
272- $row = dbFetchOne('SELECT * FROM Groups WHERE Id=?', NULL, array($IdOrRow));
273+ $row = dbFetchOne('SELECT * FROM `Groups` WHERE Id=?', NULL, array($IdOrRow));
274 if ( ! $row ) {
275 Error('Unable to load Group record for Id=' . $IdOrRow);
276 }
277@@ -59,7 +59,7 @@
278 }
279
280 public static function find( $parameters = null, $options = null ) {
281- $sql = 'SELECT * FROM Groups ';
282+ $sql = 'SELECT * FROM `Groups` ';
283 $values = array();
284
285 if ( $parameters ) {
286@@ -125,7 +125,7 @@
287 public function delete() {
288 if ( array_key_exists('Id', $this) ) {
289 dbQuery( 'DELETE FROM Groups_Monitors WHERE GroupId=?', array($this->{'Id'}) );
290- dbQuery( 'DELETE FROM Groups WHERE Id=?', array($this->{'Id'}) );
291+ dbQuery( 'DELETE FROM `Groups` WHERE Id=?', array($this->{'Id'}) );
292 if ( isset($_COOKIE['zmGroup']) ) {
293 if ( $this->{'Id'} == $_COOKIE['zmGroup'] ) {
294 unset($_COOKIE['zmGroup']);
295@@ -239,11 +239,11 @@
296
297 $MonitorIds = dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId'.$group_id_sql_part, 'MonitorId', $group_id);
298
299- $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM Groups WHERE ParentId'.$group_id_sql_part.')', 'MonitorId', $group_id));
300+ $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM `Groups` WHERE ParentId'.$group_id_sql_part.')', 'MonitorId', $group_id));
301 } else {
302 $MonitorIds = dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId=?', 'MonitorId', array($group_id));
303
304- $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM Groups WHERE ParentId = ?)', 'MonitorId', array($group_id)));
305+ $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM `Groups` WHERE ParentId = ?)', 'MonitorId', array($group_id)));
306 }
307 $groupSql = " find_in_set( M.Id, '".implode(',', $MonitorIds)."' )";
308 }
309--- a/web/skins/classic/views/control.php
310+++ b/web/skins/classic/views/control.php
311@@ -25,13 +25,13 @@
312
313 $groupSql = '';
314 if ( !empty($_REQUEST['group']) ) {
315- $row = dbFetchOne( 'SELECT * FROM Groups WHERE Id = ?', NULL, array($_REQUEST['group']) );
316+ $row = dbFetchOne( 'SELECT * FROM `Groups` WHERE Id = ?', NULL, array($_REQUEST['group']) );
317 $groupSql = " and find_in_set( Id, '".$row['MonitorIds']."' )";
318 }
319
320 $mid = !empty($_REQUEST['mid']) ? validInt($_REQUEST['mid']) : 0;
321
322-$sql = "SELECT * FROM Monitors WHERE Function != 'None' AND Controllable = 1$groupSql ORDER BY Sequence";
323+$sql = "SELECT * FROM Monitors WHERE `Function` != 'None' AND Controllable = 1$groupSql ORDER BY Sequence";
324 $mids = array();
325 foreach( dbFetchAll( $sql ) as $row ) {
326 if ( !visibleMonitor( $row['Id'] ) ) {
diff --git a/debian/patches/mysql8_my_bool.patch b/debian/patches/mysql8_my_bool.patch
0new file mode 100644327new file mode 100644
index 0000000..cee2469
--- /dev/null
+++ b/debian/patches/mysql8_my_bool.patch
@@ -0,0 +1,43 @@
1Description: Reintroduce my_bool to fix build with MySQL 8
2Author: Andreas Hasenack <andreas@canonical.com>
3Bug-Ubuntu: https://bugs.launchpad.net/bugs/1863026
4Forwarded: no
5Last-Update: 2020-02-12
6---
7This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
8diff --git a/src/zm_db.h b/src/zm_db.h
9index c707ad2..9f3381f 100644
10--- a/src/zm_db.h
11+++ b/src/zm_db.h
12@@ -21,6 +21,7 @@
13 #define ZM_DB_H
14
15 #include <mysql/mysql.h>
16+typedef bool my_bool;
17 #include "zm_thread.h"
18
19 class zmDbRow {
20diff --git a/src/zm_event.h b/src/zm_event.h
21index 1e55ae5..c3225c0 100644
22--- a/src/zm_event.h
23+++ b/src/zm_event.h
24@@ -30,6 +30,7 @@
25 #include <sys/stat.h>
26 #include <sys/types.h>
27 #include <mysql/mysql.h>
28+typedef bool my_bool;
29
30 #include <set>
31 #include <map>
32diff --git a/src/zm_logger.h b/src/zm_logger.h
33index 8e2ab6c..c842a28 100644
34--- a/src/zm_logger.h
35+++ b/src/zm_logger.h
36@@ -29,6 +29,7 @@
37 #include <sys/syscall.h>
38 #endif // HAVE_SYS_SYSCALL_H
39 #include <mysql/mysql.h>
40+typedef bool my_bool;
41
42 class Logger {
43 public:
diff --git a/debian/patches/series b/debian/patches/series
index f16125a..c295979 100644
--- a/debian/patches/series
+++ b/debian/patches/series
@@ -25,3 +25,7 @@
250009-Allow-generation-of-random-seeds-on-install-time-not.patch250009-Allow-generation-of-random-seeds-on-install-time-not.patch
260010-Use-mootools-shipped-by-debian-rather-than-the-zonem.patch260010-Use-mootools-shipped-by-debian-rather-than-the-zonem.patch
27include_path.patch27include_path.patch
28mysql8_my_bool.patch
29mysql8-password-deprecated.patch
30mysql8-reserved-keywords.patch
31mysql8-replace-password-call.patch
diff --git a/debian/rules b/debian/rules
index 98131b0..90031c5 100755
--- a/debian/rules
+++ b/debian/rules
@@ -34,8 +34,9 @@ override_dh_clean:
34 dh_clean $(MANPAGES1)34 dh_clean $(MANPAGES1)
35 $(RM) -r docs/_build docs/installationguide web/api/app/Plugin/Crud35 $(RM) -r docs/_build docs/installationguide web/api/app/Plugin/Crud
3636
37build-indep:37override_dh_auto_build-indep:
38 $(MAKE) -C docs html38 $(MAKE) -C docs html
39 dh_auto_build
3940
40MANPAGES1 = \41MANPAGES1 = \
41 dbuild/scripts/zmupdate.pl.1 \42 dbuild/scripts/zmupdate.pl.1 \

Subscribers

People subscribed via source and target branches

to all changes: