zoneminder notwork with mysql8

Forum for questions and support relating to 1.33.x development only.
stonygate
Posts: 15
Joined: Wed Mar 05, 2014 1:52 pm

zoneminder notwork with mysql8

Post by stonygate »

Hi, I recently moved from ubuntu 8.04 to 8.10
during the installation of the new system, I kept the configurations of the previous one, with the only exception of mysql that from version 5 I moved to 8.
I immediately had problems with all the programs that used mysql, I proceeded with the complete uninstallation of mysql encountering not a few problems.
I managed to install it correctly, it seems to work correctly except for zoneminder.
if I log in it gives me an error: Invalid username or password.
restarting zonemninder from terminal and using journalctl -xe gives me this error:

Code: Select all

[zma_m1] [Can't run query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version of the syntax right
what can I do for correct it?
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

For now go back to Ubuntu 18.04, which will mean you have to start over. I have not been successful getting any version of Zoneminder to run on Ubuntu 19.10 with MySQL or Mariadb. One of these days I'll get the time to do some work on it after I get all the projects around the new house up to date.

bb
rockedge
Posts: 1173
Joined: Fri Apr 04, 2014 1:46 pm
Location: Connecticut,USA

Re: zoneminder notwork with mysql8

Post by rockedge »

@bbunge

I did not know about ZM not running in 19.10...but I do have it running in Void Linux which is using the very latest mariaDB. The main issue I think is the mysql query functions have some syntax depreciated for example replace every mysql_* function call with its equivalent mysqli_*

on this Void system I am running Hiawatha, php 7.4 and the 10.1.30-MariaDB Source distribution and ZM 1.33.15 zmNinja 1.3.082
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

Well we all know Mariadb is different from Mysql. I tried 19.10 with Mariadb 10.4 and did not get Zoneminder to work but not because of database errors.
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

I have successfully installed Zoneminder 1.33 with Maridb 10 on Ubuntu 19.10.
Please see the link for installation details.
https://bkjaya.wordpress.com/2019/11/03 ... th-mriadb/

You can also use docker Zoneminder on Ubuntu 19.10 as an alternative
https://hub.docker.com/r/bkjaya1952/docker-zoneminder

According to Isaac Connor , mysql 8 problem with Zoneminder has been solved now.Please refer the following link
https://github.com/ZoneMinder/zoneminder/issues/2685
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

Better install package:

https://wiki.zoneminder.com/Ubuntu_Serv ... der_1.33.x

Thanks bkjaya1952 for the fix! There are some things in your procedure that are not needed.

Edit: Working on an install using MySQL8. May have a fix for the Zoneminder package not setting up a user...
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

I have managed to install zoneminder with mysql 8 successfully on Ubuntu 19.10
Please see the following my posts to get the installation
https://bkjaya.wordpress.com/2020/01/04 ... ntu-19-10/
https://wiki.zoneminder.com/File:Zonemi ... ysql8_.pdf
Last edited by bkjaya1952 on Mon Jan 06, 2020 2:31 pm, edited 2 times in total.
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

bkjaya1952 wrote: Fri Jan 03, 2020 7:29 pm I have managed to install zoneminder with mysql 8 successfully on Ubuntu 19.10
Please see the following my posts to get the installation
https://bkjaya.wordpress.com/2020/01/04 ... ntu-19-10/

https://wiki.zoneminder.com/File:Zm_with_mysql8.pdf
You do not need to add the timezone in php.ini

Next, we should get this to work with the enhanced mysql security! Until that happens I recommend sticking with Mariadb.
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

bbunge wrote: Fri Jan 03, 2020 7:50 pm
bkjaya1952 wrote: Fri Jan 03, 2020 7:29 pm I have managed to install zoneminder with mysql 8 successfully on Ubuntu 19.10
Please see the following my posts to get the installation
https://bkjaya.wordpress.com/2020/01/04 ... ntu-19-10/

https://wiki.zoneminder.com/File:Zm_with_mysql8.pdf
You do not need to add the timezone in php.ini

Next, we should get this to work with the enhanced mysql security! Until that happens I recommend sticking with Mariadb.
Yes . You can change TIMEZONE later under Options/System/TIMEZONE in latest Zoneminder versions.
The use of Maradb instead of Mysql may not improve the security level of data base. In my case , I have used a password for mysql-server-8

The main problem is the present PHP release make errors with the "caching_sha2_password" introduced in the Mysql 8 by default .
Please refer the following link
https://www.php.net/manual/en/mysqli.requirements.php
Therefore , we will have to use "mysql_native_password" method in Mysql 8.
There is always risks in any system .No one can build a 100% foolproof system. I am of the opinion that ,there is no harm in finding ways and means to use present Mysql 8 & php with Zoneminder and share the experience with others , without waiting someone to fix the problems.
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

Cannot get the procedure to work with MySQL8. Error creating zm database/user:

Procedure fails to secure the MySQL database root password

Have added: default_authentication_plugin = mysql_native_password to /etc/mysql/mysql.conf.d/mysqld.conf

Creating zm db
ERROR 1064 (42000) at line 1: 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 'identified by "zmpass"' at line 1
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
01/05/2020 16:11:08.204181 zmupdate[8454].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
01/05/2020 16:11:08.204394 zmupdate[8454].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
01/05/2020 16:11:08.205129 zmupdate[8454].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
Can't call method "prepare_cached" on an undefined value at /usr/share/perl5/ZoneMinder/Config.pm line 96.
BEGIN failed--compilation aborted at /usr/share/perl5/ZoneMinder/Config.pm line 147.
Compilation failed in require at /usr/bin/zmupdate.pl line 73.
BEGIN failed--compilation aborted at /usr/bin/zmupdate.pl line 73.
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
01/05/2020 16:11:08.263248 zmupdate[8455].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
01/05/2020 16:11:08.263462 zmupdate[8455].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
DBI connect('database=zm;host=localhost','zmuser',...) failed: Access denied for user 'zmuser'@'localhost' (using password: YES) at /usr/share/perl5/ZoneMinder/Database.pm line 110.
01/05/2020 16:11:08.264097 zmupdate[8455].ERR [ZoneMinder::Database:113] [Error reconnecting to db: errstr:Access denied for user 'zmuser'@'localhost' (using password: YES) error val:]
Can't call method "prepare_cached" on an undefined value at /usr/share/perl5/ZoneMinder/Config.pm line 96.
BEGIN failed--compilation aborted at /usr/share/perl5/ZoneMinder/Config.pm line 147.
Compilation failed in require at /usr/bin/zmupdate.pl line 73.
BEGIN failed--compilation aborted at /usr/bin/zmupdate.pl line 73.
Done Updating; starting ZoneMinder.
zoneminder.service is a disabled or a static u
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

In order to have clean mysql-server-8.0 install , it is better to completely delete exiting mysql as follows
sudo apt remove --purge mysql-server mysql-client mysql-common
sudo apt autoremove
sudo apt autoclean
sudo rm -rf /etc/mysql
Last edited by bkjaya1952 on Tue Jan 07, 2020 8:18 am, edited 1 time in total.
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

bkjaya1952 wrote: Mon Jan 06, 2020 12:20 pm In order to have clean mysql-server-8.0 install , it is better to completely delete exiting mysql as follows
sudo apt remove --purge mysql-server mysql-client mysql-common
sudo apt autoremove
sudo apt autoclean
I start with a clean install every time I test. I have images of the Ubuntu and Debian releases with LAMP (either MySQL or Mariadb) which have no root passwords for the database server and no configurations beyond a basic install. So my tests with 19.10/MySQL 8 are fresh and have failed every time. I do not use a desktop version but a minimal server install.
bbunge
Posts: 2923
Joined: Mon Mar 26, 2012 11:40 am
Location: Pennsylvania

Re: zoneminder notwork with mysql8

Post by bbunge »

I was able to get Zoneminder to work with MySQL 8. Your procedure is not quite right. I was able to use your basics. I will correct the procedure and post to the WIKI hopefully tomorrow. Has been a bad day for me, computer wise, until I was able to get this to work.
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

bbunge wrote: Tue Jan 07, 2020 2:33 am I was able to get Zoneminder to work with MySQL 8. Your procedure is not quite right. I was able to use your basics. I will correct the procedure and post to the WIKI hopefully tomorrow. Has been a bad day for me, computer wise, until I was able to get this to work.
:lol: I am happy , you have at least slightly mentioned that you have used my basics. I have worked very hard to find the solution what you called the "basics". I think that ,the ' basics' is the most important part of the solution. I always appreciate constructive criticism . But, some people are used to copy the technical findings of others , while condemning it totally .
User avatar
bkjaya1952
Posts: 282
Joined: Sat Aug 25, 2018 3:24 pm
Location: Sri Lanka

Re: zoneminder notwork with mysql8

Post by bkjaya1952 »

bbunge wrote: Tue Jan 07, 2020 2:33 am I was able to get Zoneminder to work with MySQL 8. Your procedure is not quite right. I was able to use your basics. I will correct the procedure and post to the WIKI hopefully tomorrow. Has been a bad day for me, computer wise, until I was able to get this to work.
If you are not sure about the installation procedure , please see the following screen shots which show mysql and Zoneminder running

https://www.dropbox.com/s/sjrw5a6w4462f ... 3.png?dl=0

https://www.dropbox.com/s/yl3nk6q6r1noh ... 0.png?dl=0

https://www.dropbox.com/s/5xsrjospvglp5 ... 8.png?dl=0
Locked