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

[Andreas Hasenack]

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.

[Christian Ehrhardt  (paelzer)]

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

[Andreas Hasenack (ahasenack)]

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

[Andreas Hasenack (ahasenack)]

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.

[Andreas Hasenack (ahasenack)]

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

[Andreas Hasenack (ahasenack)]

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.

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
     echo 'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost identified by "zmpass";'\
     | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
 
+With MySQL8:
+
+    echo "CREATE USER 'zmuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'zmpass';"\
+    | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
+    echo 'grant lock tables,alter,create,select,insert,update,delete,index on zm.* to 'zmuser'@localhost;'\
+    | sudo mysql --defaults-file=/etc/mysql/debian.cnf mysql
+
 Hint: generate secure password with `pwgen` and update "/etc/zm/zm.conf"
 accordingly.
 
@@ -128,8 +135,8 @@ System time zone can be set by the following command:
 PHP time zone can be configured by changing value of "date.timezone" in one
 of the following files:
 
-    /etc/php/7.2/fpm/php.ini
-    /etc/php/7.2/apache2/php.ini
+    /etc/php/7.4/fpm/php.ini
+    /etc/php/7.4/apache2/php.ini
 
 Please note that MariaDB/MySQL time zone should be identical to system and
 PHP 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 @@
+zoneminder (1.32.3-2ubuntu1) focal; urgency=medium
+
+  * d/p/mysql8_my_bool.patch: Reintroduce my_bool to fix build with MySQL 8.
+    (LP: #1863026)
+  * d/rules: override build-dep to fix FTBFS (thanks to
+    [e-mail]) (LP: #1863793, Closes: #951600)
+  * MySQL8 runtime fixes (LP: #1859295):
+    - d/p/mysql8-password-deprecated.patch: password() is deprecated
+    - d/p/mysql8-reserved-keywords.patch: quote MySQL8 reserved keywords
+    - d/p/mysql8-replace-password-call.patch: replace password()
+  * d/README.Debian: quick update for MySQL8 and Focal
+
+ -- Andreas Hasenack <[e-mail]>  Tue, 25 Feb 2020 10:22:20 -0300
+
 zoneminder (1.32.3-2build1) eoan; urgency=medium
 
   * 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
 Section: net
 Priority: optional
 Standards-Version: 4.3.0
-Maintainer: Dmitry Smirnov <[e-mail]>
+Maintainer: Ubuntu Developers <[e-mail]>
+XSBC-Original-Maintainer: Dmitry Smirnov <[e-mail]>
 Uploaders: Chris Lamb <[e-mail]>
 Build-Depends: debhelper (>= 11~) ,dh-linktree
     ,apache2-dev
diff --git a/debian/patches/mysql8-password-deprecated.patch b/debian/patches/mysql8-password-deprecated.patch
new file mode 100644
index 0000000..f16dc63
--- /dev/null
+++ b/debian/patches/mysql8-password-deprecated.patch
@@ -0,0 +1,26 @@
+From 5b896b5e5ca57fe9780e852d570a31c7a5b2ecfb Mon Sep 17 00:00:00 2001
+From: Isaac Connor <[e-mail]>
+Date: Mon, 8 Jul 2019 14:16:57 -0400
+Subject: [PATCH] Replace password('admin') with the resulting string because
+ use of password is deprecated
+
+---
+ db/zm_create.sql.in | 2 +-
+ 1 file changed, 1 insertion(+), 1 deletion(-)
+
+Origin: https://github.com/ZoneMinder/zoneminder/commit/5b896b5e5ca57fe9780e852d570a31c7a5b2ecfb
+Bug: https://github.com/ZoneMinder/zoneminder/issues/2658
+Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
+Last-Updated: 2020-02-21
+
+--- a/db/zm_create.sql.in
++++ b/db/zm_create.sql.in
+@@ -738,7 +738,7 @@
+ --
+ -- Create a default admin user.
+ --
+-insert into Users VALUES (NULL,'admin',password('admin'),'',1,'View','Edit','Edit','Edit','Edit','Edit','Edit','','');
++insert into Users VALUES (NULL,'admin','$2b$12$NHZsm6AM2f2LQVROriz79ul3D6DnmFiZC.ZK5eqbF.ZWfwH9bqUJ6','',1,'View','Edit','Edit','Edit','Edit','Edit','Edit','','');
+ 
+ --
+ -- 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
new file mode 100644
index 0000000..574bf24
--- /dev/null
+++ b/debian/patches/mysql8-replace-password-call.patch
@@ -0,0 +1,58 @@
+Description: replace password()
+ password() is deprecated in MySQL8, replace it with an equivalent implementation:
+ .
+ mysql> select password('secret'),CONCAT('*',UPPER(SHA1(UNHEX(SHA1('secret')))));
+ +-------------------------------------------+------------------------------------------------+
+ | password('secret')                        | CONCAT('*',UPPER(SHA1(UNHEX(SHA1('secret'))))) |
+ +-------------------------------------------+------------------------------------------------+
+ | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7      |
+ +-------------------------------------------+------------------------------------------------+
+Author: Joseph Yasi <[e-mail]>
+Origin: other, https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295/comments/1
+Bug: https://github.com/ZoneMinder/zoneminder/issues/2659
+Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
+Last-Update: 2020-02-24
+---
+This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
+--- a/src/zm_user.cpp
++++ b/src/zm_user.cpp
+@@ -99,7 +99,7 @@
+     int password_length = strlen(password);
+     char *safer_password = new char[(password_length * 2) + 1];
+     mysql_real_escape_string(&dbconn, safer_password, password, password_length);
+-    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 );
++    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 );
+     delete safer_password;
+   } else {
+     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 );
+--- a/web/includes/actions.php
++++ b/web/includes/actions.php
+@@ -960,7 +960,7 @@
+     $changes = getFormChanges($dbUser, $_REQUEST['newUser'], $types);
+ 
+     if ( $_REQUEST['newUser']['Password'] )
+-      $changes['Password'] = 'Password = password('.dbEscape($_REQUEST['newUser']['Password']).')';
++      $changes['Password'] = 'Password = CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1('.dbEscape($_REQUEST['newUser']['Password']).')))))';
+     else
+       unset($changes['Password']);
+ 
+@@ -1015,7 +1015,7 @@
+     $changes = getFormChanges($dbUser, $_REQUEST['newUser'], $types);
+ 
+     if ( !empty($_REQUEST['newUser']['Password']) )
+-      $changes['Password'] = 'Password = password('.dbEscape($_REQUEST['newUser']['Password']).')';
++      $changes['Password'] = 'Password = CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1('.dbEscape($_REQUEST['newUser']['Password']).')))))';
+     else
+       unset($changes['Password']);
+     if ( count($changes) ) {
+--- a/web/includes/auth.php
++++ b/web/includes/auth.php
+@@ -27,7 +27,7 @@
+     if ( $passwordHashed ) {
+       $sql .= ' AND Username=? AND Password=?';
+     } else {
+-      $sql .= ' AND Username=? AND Password=password(?)';
++      $sql .= ' AND Username=? AND Password=CONCAT(\'*\',UPPER(SHA1(UNHEX(SHA1(?)))))';
+     }
+     $sql_values = array($username, $password);
+   } else {
diff --git a/debian/patches/mysql8-reserved-keywords.patch b/debian/patches/mysql8-reserved-keywords.patch
new file mode 100644
index 0000000..bc067d1
--- /dev/null
+++ b/debian/patches/mysql8-reserved-keywords.patch
@@ -0,0 +1,326 @@
+Description: Quote MySQL8 reserved keywords
+Author: Joseph Yasi <[e-mail]>
+Origin: other, https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295/comments/1
+Bug: https://github.com/ZoneMinder/zoneminder/issues/2659
+Bug-Ubuntu: https://bugs.launchpad.net/ubuntu/+source/zoneminder/+bug/1859295
+Last-Update: 2020-02-21
+---
+This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
+--- a/scripts/zmtelemetry.pl.in
++++ b/scripts/zmtelemetry.pl.in
+@@ -243,7 +243,7 @@
+ sub getMonitorRef {
+   my $dbh = shift;
+ 
+-  my $sql = 'SELECT Id,Name,Type,Function,Width,Height,Colours,MaxFPS,AlarmMaxFPS FROM Monitors';
++  my $sql = 'SELECT Id,Name,Type,`Function`,Width,Height,Colours,MaxFPS,AlarmMaxFPS FROM Monitors';
+   my $sth = $dbh->prepare_cached( $sql ) or die( "Can't prepare '$sql': ".$dbh->errstr() );
+   my $res = $sth->execute() or die( "Can't execute: ".$sth->errstr() );
+   my $arrayref = $sth->fetchall_arrayref({});
+--- a/scripts/zmtrigger.pl.in
++++ b/scripts/zmtrigger.pl.in
+@@ -333,7 +333,7 @@
+   my %new_monitors = ();
+ 
+   my $sql = "SELECT * FROM Monitors
+-    WHERE find_in_set( Function, 'Modect,Mocord,Nodect' )".
++    WHERE find_in_set( `Function`, 'Modect,Mocord,Nodect' )".
+     ( $Config{ZM_SERVER_ID} ? 'AND ServerId=?' : '' )
+     ;
+   my $sth = $dbh->prepare_cached( $sql )
+--- a/scripts/zmx10.pl.in
++++ b/scripts/zmx10.pl.in
+@@ -464,7 +464,7 @@
+ 
+     my $sql = "SELECT M.*,T.* from Monitors as M
+                INNER JOIN TriggersX10 as T on (M.Id = T.MonitorId)
+-               WHERE find_in_set( M.Function, 'Modect,Record,Mocord,Nodect' )
++               WHERE find_in_set( M.`Function`, 'Modect,Record,Mocord,Nodect' )
+                 AND M.Enabled = 1
+                 AND find_IN_set( 'X10', M.Triggers )"
+     ;
+--- a/scripts/ZoneMinder/lib/ZoneMinder/Database.pm
++++ b/scripts/ZoneMinder/lib/ZoneMinder/Database.pm
+@@ -145,15 +145,15 @@
+ 
+   if ( $function ) {
+     if ( $function == DB_MON_CAPT ) {
+-      $sql .= " where Function >= 'Monitor'";
++      $sql .= " where `Function` >= 'Monitor'";
+     } elsif ( $function == DB_MON_ACTIVE ) {
+-      $sql .= " where Function > 'Monitor'";
++      $sql .= " where `Function` > 'Monitor'";
+     } elsif ( $function == DB_MON_MOTION ) {
+-      $sql .= " where Function = 'Modect' or Function = 'Mocord'";
++      $sql .= " where `Function` = 'Modect' or `Function` = 'Mocord'";
+     } elsif ( $function == DB_MON_RECORD ) {
+-      $sql .= " where Function = 'Record' or Function = 'Mocord'";
++      $sql .= " where `Function` = 'Record' or `Function` = 'Mocord'";
+     } elsif ( $function == DB_MON_PASSIVE ) {
+-      $sql .= " where Function = 'Nodect'";
++      $sql .= " where `Function` = 'Nodect'";
+     }
+   }
+   my $sth = $dbh->prepare_cached( $sql );
+--- a/src/zm_monitor.cpp
++++ b/src/zm_monitor.cpp
+@@ -67,7 +67,7 @@
+ // This is the official SQL (and ordering of the fields) to load a Monitor.
+ // It will be used whereever a Monitor dbrow is needed. WHERE conditions can be appended
+ std::string load_monitor_sql =
+-"SELECT Id, Name, ServerId, StorageId, Type, Function+0, Enabled, LinkedMonitors, "
++"SELECT Id, Name, ServerId, StorageId, Type, `Function`+0, Enabled, LinkedMonitors, "
+ "AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS,"
+ "Device, Channel, Format, V4LMultiBuffer, V4LCapturesPerFrame, " // V4L Settings
+ "Protocol, Method, Options, User, Pass, Host, Port, Path, Width, Height, Colours, Palette, Orientation+0, Deinterlacing, RTSPDescribe, "
+@@ -1770,7 +1770,7 @@
+ 
+   static char sql[ZM_SQL_MED_BUFSIZ];
+   // This seems to have fallen out of date.
+-  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 );
++  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 );
+ 
+   zmDbRow *row = zmDbFetchOne(sql);
+   if ( !row ) {
+@@ -1904,7 +1904,7 @@
+ 
+         db_mutex.lock();
+         static char sql[ZM_SQL_SML_BUFSIZ];
+-        snprintf(sql, sizeof(sql), "select Id, Name from Monitors where Id = %d and Function != 'None' and Function != 'Monitor' and Enabled = 1", link_ids[i] );
++        snprintf(sql, sizeof(sql), "select Id, Name from Monitors where Id = %d and `Function` != 'None' and `Function` != 'Monitor' and Enabled = 1", link_ids[i] );
+         if ( mysql_query(&dbconn, sql) ) {
+           Error("Can't run query: %s", mysql_error(&dbconn));
+ 					db_mutex.unlock();
+@@ -1961,7 +1961,7 @@
+ #if ZM_HAS_V4L
+ int Monitor::LoadLocalMonitors( const char *device, Monitor **&monitors, Purpose purpose ) {
+ 
+-  std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Local'";
++  std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Local'";
+ 
+   if ( device[0] )
+     sql += " AND Device='" + std::string(device) + "'";
+@@ -1972,7 +1972,7 @@
+ #endif // ZM_HAS_V4L
+ 
+ int Monitor::LoadRemoteMonitors( const char *protocol, const char *host, const char *port, const char *path, Monitor **&monitors, Purpose purpose ) {
+-  std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Remote'";
++  std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Remote'";
+   if ( staticConfig.SERVER_ID )
+     sql += stringtf( " AND ServerId=%d", staticConfig.SERVER_ID );
+ 
+@@ -1982,7 +1982,7 @@
+ }
+ 
+ int Monitor::LoadFileMonitors( const char *file, Monitor **&monitors, Purpose purpose ) {
+-  std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'File'";
++  std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'File'";
+   if ( file[0] )
+     sql += " AND Path='" + std::string(file) + "'";
+   if ( staticConfig.SERVER_ID ) {
+@@ -1993,7 +1993,7 @@
+ 
+ #if HAVE_LIBAVFORMAT
+ int Monitor::LoadFfmpegMonitors(const char *file, Monitor **&monitors, Purpose purpose) {
+-  std::string sql = load_monitor_sql + " WHERE Function != 'None' AND Type = 'Ffmpeg'";
++  std::string sql = load_monitor_sql + " WHERE `Function` != 'None' AND Type = 'Ffmpeg'";
+   if ( file[0] )
+     sql += " AND Path = '" + std::string(file) + "'";
+ 
+@@ -2006,7 +2006,7 @@
+ 
+ /*
+   std::string load_monitor_sql =
+- "SELECT Id, Name, ServerId, StorageId, Type, Function+0, Enabled, LinkedMonitors, "
++ "SELECT Id, Name, ServerId, StorageId, Type, `Function`+0, Enabled, LinkedMonitors, "
+  "AnalysisFPSLimit, AnalysisUpdateDelay, MaxFPS, AlarmMaxFPS,"
+  "Device, Channel, Format, V4LMultiBuffer, V4LCapturesPerFrame, " // V4L Settings
+  "Protocol, Method, Options, User, Pass, Host, Port, Path, Width, Height, Colours, Palette, Orientation+0, Deinterlacing, RTSPDescribe, "
+@@ -2811,7 +2811,7 @@
+   // At the moment, only load groups once.
+   if ( ! groups.size() ) {
+     std::string sql = stringtf(
+-        "SELECT Id,ParentId,Name FROM Groups WHERE Groups.Id IN "
++        "SELECT Id,ParentId,Name FROM `Groups` WHERE `Groups`.Id IN "
+         "(SELECT GroupId FROM Groups_Monitors WHERE MonitorId=%d)",id);
+     MYSQL_RES *result = zmDbFetch(sql.c_str());
+     if ( !result ) {
+--- a/src/zm_rtsp.cpp
++++ b/src/zm_rtsp.cpp
+@@ -93,7 +93,7 @@
+ int RtspThread::requestPorts() {
+   if ( !smMinDataPort ) {
+     char sql[ZM_SQL_SML_BUFSIZ];
+-    strncpy( sql, "select Id from Monitors where Function != 'None' and Type = 'Remote' and Protocol = 'rtsp' and Method = 'rtpUni' order by Id asc", sizeof(sql) );
++    strncpy( sql, "select Id from Monitors where `Function` != 'None' and Type = 'Remote' and Protocol = 'rtsp' and Method = 'rtpUni' order by Id asc", sizeof(sql) );
+     if ( mysql_query( &dbconn, sql ) ) {
+       Error( "Can't run query: %s", mysql_error( &dbconn ) );
+       exit( mysql_errno( &dbconn ) );
+--- a/src/zmu.cpp
++++ b/src/zmu.cpp
+@@ -691,9 +691,9 @@
+     }
+ 
+     if ( function & ZMU_LIST ) {
+-      std::string sql = "select Id, Function+0 from Monitors";
++      std::string sql = "select Id, `Function`+0 from Monitors";
+       if ( !verbose ) {
+-        sql += "where Function != 'None'";
++        sql += "where `Function` != 'None'";
+       }
+       sql += " order by Id asc";
+ 
+--- a/src/zm_user.cpp
++++ b/src/zm_user.cpp
+@@ -99,10 +99,10 @@
+     int password_length = strlen(password);
+     char *safer_password = new char[(password_length * 2) + 1];
+     mysql_real_escape_string(&dbconn, safer_password, password, password_length);
+-    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 );
++    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 );
+     delete safer_password;
+   } else {
+-    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 );
++    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 );
+   }
+ 
+   if ( mysql_query( &dbconn, sql ) ) {
+@@ -157,7 +157,7 @@
+ 
+   Debug( 1, "Attempting to authenticate user from auth string '%s'", auth );
+   char sql[ZM_SQL_SML_BUFSIZ] = "";
+-  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" );
++  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" );
+ 
+   if ( mysql_query( &dbconn, sql ) ) {
+     Error( "Can't run query: %s", mysql_error( &dbconn ) );
+--- a/utils/zm-alarm.pl
++++ b/utils/zm-alarm.pl
+@@ -16,7 +16,7 @@
+ my $dbh = zmDbConnect();
+ 
+ my $sql = "SELECT * FROM Monitors
+-  WHERE find_in_set( Function, 'Modect,Mocord,Nodect' )".
++  WHERE find_in_set( `Function`, 'Modect,Mocord,Nodect' )".
+   ( $Config{ZM_SERVER_ID} ? 'AND ServerId=?' : '' )
+   ;
+ 
+--- a/web/ajax/status.php
++++ b/web/ajax/status.php
+@@ -16,7 +16,7 @@
+       'limit' => 1,
+       'elements' => array(
+         'MonitorCount' => array( 'sql' => 'count(*)' ),
+-        'ActiveMonitorCount' => array( 'sql' => "count(if(Function != 'None',1,NULL))" ),
++        'ActiveMonitorCount' => array( 'sql' => "count(if(`Function` != 'None',1,NULL))" ),
+         'State' => array( 'func' => 'daemonCheck()?'.translate('Running').':'.translate('Stopped') ),
+         'Load' => array( 'func' => 'getLoad()' ),
+         'Disk' => array( 'func' => 'getDiskPercent()' ),
+--- a/web/includes/database.php
++++ b/web/includes/database.php
+@@ -227,7 +227,7 @@
+ }
+ 
+ function getEnumValues( $table, $column ) {
+-  $row = dbFetchOne( "describe $table $column" );
++  $row = dbFetchOne( "describe `$table` `$column`" );
+   preg_match_all( "/'([^']+)'/", $row['Type'], $matches );
+   return( $matches[1] );
+ }
+@@ -238,7 +238,7 @@
+ 
+ function getUniqueValues( $table, $column, $asString=1 ) {
+   $values = array();
+-  $sql =  "select distinct $column from $table where (not isnull($column) and $column != '') order by $column";
++  $sql =  "select distinct `$column` from `$table` where (not isnull(`$column`) and `$column` != '') order by `$column`";
+   foreach( dbFetchAll( $sql ) as $row ) {
+     if ( $asString )
+       $values[$row[$column]] = $row[$column];
+@@ -250,7 +250,7 @@
+ 
+ function getTableColumns( $table, $asString=1 ) {
+   $columns = array();
+-  $sql = "describe $table";
++  $sql = "describe `$table`";
+   foreach( dbFetchAll( $sql ) as $row ) {
+     if ( $asString )
+       $columns[$row['Field']] = $row['Type'];
+@@ -267,7 +267,7 @@
+ 
+ function getTableDescription( $table, $asString=1 ) {
+   $columns = array();
+-  foreach( dbFetchAll( "describe $table" ) as $row ) {
++  foreach( dbFetchAll( "describe `$table`" ) as $row ) {
+     $desc = array(
+         'name' => $row['Field'],
+         'required' => ($row['Null']=='NO')?true:false,
+@@ -377,7 +377,7 @@
+ }
+ 
+ function dbFetchGroup( $gid ) {
+-  return( dbFetchOne( 'select * from Groups where Id = ?', NULL, array($gid) ) );
++  return( dbFetchOne( 'select * from `Groups` where Id = ?', NULL, array($gid) ) );
+ }
+ 
+ ?>
+--- a/web/includes/Group.php
++++ b/web/includes/Group.php
+@@ -16,7 +16,7 @@
+     $row = NULL;
+     if ( $IdOrRow ) {
+       if ( is_integer($IdOrRow) or is_numeric($IdOrRow) ) {
+-        $row = dbFetchOne('SELECT * FROM Groups WHERE Id=?', NULL, array($IdOrRow));
++        $row = dbFetchOne('SELECT * FROM `Groups` WHERE Id=?', NULL, array($IdOrRow));
+         if ( ! $row ) {
+           Error('Unable to load Group record for Id=' . $IdOrRow);
+         }
+@@ -59,7 +59,7 @@
+   }
+ 
+   public static function find( $parameters = null, $options = null ) {
+-    $sql = 'SELECT * FROM Groups ';
++    $sql = 'SELECT * FROM `Groups` ';
+     $values = array();
+ 
+     if ( $parameters ) {
+@@ -125,7 +125,7 @@
+   public function delete() {
+     if ( array_key_exists('Id', $this) ) {
+       dbQuery( 'DELETE FROM Groups_Monitors WHERE GroupId=?', array($this->{'Id'}) );
+-      dbQuery( 'DELETE FROM Groups WHERE Id=?', array($this->{'Id'}) );
++      dbQuery( 'DELETE FROM `Groups` WHERE Id=?', array($this->{'Id'}) );
+       if ( isset($_COOKIE['zmGroup']) ) {
+         if ( $this->{'Id'} == $_COOKIE['zmGroup'] ) {
+           unset($_COOKIE['zmGroup']);
+@@ -239,11 +239,11 @@
+ 
+         $MonitorIds = dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId'.$group_id_sql_part, 'MonitorId', $group_id);
+ 
+-        $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));
++        $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));
+       } else { 
+         $MonitorIds = dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId=?', 'MonitorId', array($group_id));
+ 
+-        $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM Groups WHERE ParentId = ?)', 'MonitorId', array($group_id)));
++        $MonitorIds = array_merge($MonitorIds, dbFetchAll('SELECT MonitorId FROM Groups_Monitors WHERE GroupId IN (SELECT Id FROM `Groups` WHERE ParentId = ?)', 'MonitorId', array($group_id)));
+       }
+       $groupSql = " find_in_set( M.Id, '".implode(',', $MonitorIds)."' )";
+     }
+--- a/web/skins/classic/views/control.php
++++ b/web/skins/classic/views/control.php
+@@ -25,13 +25,13 @@
+ 
+ $groupSql = '';
+ if ( !empty($_REQUEST['group']) ) {
+-  $row = dbFetchOne( 'SELECT * FROM Groups WHERE Id = ?', NULL, array($_REQUEST['group']) );
++  $row = dbFetchOne( 'SELECT * FROM `Groups` WHERE Id = ?', NULL, array($_REQUEST['group']) );
+   $groupSql = " and find_in_set( Id, '".$row['MonitorIds']."' )";
+ }
+ 
+ $mid = !empty($_REQUEST['mid']) ? validInt($_REQUEST['mid']) : 0;
+ 
+-$sql = "SELECT * FROM Monitors WHERE Function != 'None' AND Controllable = 1$groupSql ORDER BY Sequence";
++$sql = "SELECT * FROM Monitors WHERE `Function` != 'None' AND Controllable = 1$groupSql ORDER BY Sequence";
+ $mids = array();
+ foreach( dbFetchAll( $sql ) as $row ) {
+   if ( !visibleMonitor( $row['Id'] ) ) {
diff --git a/debian/patches/mysql8_my_bool.patch b/debian/patches/mysql8_my_bool.patch
new file mode 100644
index 0000000..cee2469
--- /dev/null
+++ b/debian/patches/mysql8_my_bool.patch
@@ -0,0 +1,43 @@
+Description: Reintroduce my_bool to fix build with MySQL 8
+Author: Andreas Hasenack <[e-mail]>
+Bug-Ubuntu: https://bugs.launchpad.net/bugs/1863026
+Forwarded: no
+Last-Update: 2020-02-12
+---
+This patch header follows DEP-3: http://dep.debian.net/deps/dep3/
+diff --git a/src/zm_db.h b/src/zm_db.h
+index c707ad2..9f3381f 100644
+--- a/src/zm_db.h
++++ b/src/zm_db.h
+@@ -21,6 +21,7 @@
+ #define ZM_DB_H
+ 
+ #include <mysql/mysql.h>
++typedef bool my_bool;
+ #include "zm_thread.h"
+ 
+ class zmDbRow {
+diff --git a/src/zm_event.h b/src/zm_event.h
+index 1e55ae5..c3225c0 100644
+--- a/src/zm_event.h
++++ b/src/zm_event.h
+@@ -30,6 +30,7 @@
+ #include <sys/stat.h>
+ #include <sys/types.h>
+ #include <mysql/mysql.h>
++typedef bool my_bool;
+ 
+ #include <set>
+ #include <map>
+diff --git a/src/zm_logger.h b/src/zm_logger.h
+index 8e2ab6c..c842a28 100644
+--- a/src/zm_logger.h
++++ b/src/zm_logger.h
+@@ -29,6 +29,7 @@
+ #include <sys/syscall.h>
+ #endif // HAVE_SYS_SYSCALL_H
+ #include <mysql/mysql.h>
++typedef bool my_bool;
+ 
+ class Logger {
+ 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 @@
 0009-Allow-generation-of-random-seeds-on-install-time-not.patch
 0010-Use-mootools-shipped-by-debian-rather-than-the-zonem.patch
 include_path.patch
+mysql8_my_bool.patch
+mysql8-password-deprecated.patch
+mysql8-reserved-keywords.patch
+mysql8-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:
 	dh_clean $(MANPAGES1)
 	$(RM) -r docs/_build docs/installationguide web/api/app/Plugin/Crud
 
-build-indep:
+override_dh_auto_build-indep:
 	$(MAKE) -C docs html
+	dh_auto_build
 
 MANPAGES1 = \
     dbuild/scripts/zmupdate.pl.1    \