MySQL 5.6 compatibility issues

[newburns]

So far using a REMOTE MySQL 5.6 the following issues have arose:

1.) Using zm1.28.03 - Initial error on column SCORE in FRAMES table. Had to remove the (unsigned) attribute in order for it to work temporarily. Not suggesting this for a fix, but this was the temp solution.

Full error from /var/log/messages:
ERR [Can't insert frame: Out of range value for column 'Score' at row 1]

2.) Using zm1.26.5 - Must have a MaxFPS and AlarmFPS set for all new/updated cameras. The single " is an incorrect decimal value. Not sure how to fix it, but I use a hard number like 32. Assuming that I would never use a 60fps camera for security.

Full error from in Screen:
SQL-ERR 'Incorrect decimal value: '' for column 'MaxFPS' at row 1', statement was 'update Monitors set MaxFPS = '', Triggers = '' where Id = '1';

[newburns]

Using zm1.28.03 - Also, cannot write to the Database:

Full error from /var/log/messages:
template zmdc[10239]: WAR [Can't find process with command of 'zmc -m']

Full error from REMOTE MySQL /var/log/mysqld.log:
14000 [Warning] Did not write failed 'REVOKE ALL PRIVILEGES ON zmdb.* FROM 'zmuser'@'%'' into binary log while granting/revoking privileges in databases.

[connortechnology]

Ok, first off... 1.28.03 doesn't exist. We havn't release 1.28.1, so what are you running, on what distro?

[newburns]

Sorry. Using your distro:

rpm -q zoneminder
zoneminder-1.28.0-3.el6.x86_64

I just imported the zm_create.sql manually. That way there were no issues on initial creation.

[newburns]

Issue still present when Zoneminder is using PHP 5.5.18

[SteveGilvarry]

zmc -m means something is wrong as it should have a number for the monitor, so zmc -m 1 runs zmc for Monitor 1. Assuming you can't write to db is as per your second post, maybe this error is caused by DB being as per setup and can't update monitor and config settings.
Confirm account in remote db matches zm.conf details. Confirm the account in there matches permissions shown here.
'grant lock tables, alter,select,insert,update,delete on zm.* to 'zmuser'@localhost identified by "zmpass";'

[newburns]

This is my zm.conf, MySQL priveleges, and errors I get on writing.
I'm assuming it has something to do with a botched install. I am going to try a fresh VM and report back

cat /etc/zm.conf

Path to installed data directory, used mostly for finding DB upgrade scripts
ZM_PATH_DATA=/usr/share/zoneminder
Path to ZoneMinder binaries
ZM_PATH_BIN=/usr/bin
Path to ZoneMinder libraries (none at present, for future use)
ZM_PATH_LIB=/usr/lib64
Path to ZoneMinder configuration (this file only at present)
ZM_PATH_CONF=/etc
Path to ZoneMinder web files
ZM_PATH_WEB=/usr/share/zoneminder/www
Path to ZoneMinder cgi files
ZM_PATH_CGI=/usr/libexec/zoneminder/cgi-bin
Username and group that web daemon (httpd/apache) runs as
ZM_WEB_USER=apache
ZM_WEB_GROUP=apache
ZoneMinder database type: so far only mysql is supported
ZM_DB_TYPE=mysql
ZoneMinder database hostname or ip address
ZM_DB_HOST=10.1.22.23
ZoneMinder database name
ZM_DB_NAME=tzone
ZoneMinder database user
ZM_DB_USER=zoneminder
ZoneMinder database password
ZM_DB_PASS=zmpass
Host of this machine
ZM_SERVER_HOST=

MySQL Priveleges on 10.1.22.23

| Grants for zoneminder@% |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO 'zoneminder'@'%' IDENTIFIED BY PASSWORD 'zmpass' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON tzone.* TO 'zoneminder'@'%'

[newburns]

I started with zoneminder 1.2.6.5
This is the error when creating a new monitor. Other than that, everything seems to run effeciently on remote MySQL 5.6 DB

SQL-ERR 'Field 'User' doesn't have a default value', statement was 'insert into Monitors set LinkedMonitors = '', Name = 'Monitor-51', Type = 'Remote', Function = 'Monitor', Enabled = '1', RefBlendPerc = '6', AlarmRefBlendPerc = '3', MaxFPS = '', AlarmMaxFPS = '', Device = '/dev/video0', Channel = '0', Format = '255', Palette = '0', LabelFormat = '%N - %d/%m/%y %H:%M:%S', LabelX = '0', LabelY = '0', ImageBufferCount = '50', WarmupCount = '25', PreEventCount = '25', PostEventCount = '25', StreamReplayBuffer = '1000', AlarmFrameCount = '1', Controllable = '0', ControlId = '', ControlDevice = '', ControlAddress = '', AutoStopTimeout = '', TrackMotion = '0', TrackDelay = '', ReturnLocation = '-1', ReturnDelay = '', EventPrefix = 'Event-', SectionLength = '600', FrameSkip = '0', FPSReportInterval = '1000', DefaultView = 'Events', DefaultRate = '100', DefaultScale = '100', WebColour = 'red', SignalCheckColour = '#0000c0', Protocol = 'http', Method = 'simple', Host = 'usern:passw@10.1.22.29', Port = '80', Path = '/mjpg/video.mjpg', Colours = '3', Width = '320', Height = '240', Orientation = '0', Deinterlacing = '0', Sequence = 50'

Moving onto ZM 1.27 then 1.28

[newburns]

On ZM 1.28.0-3

Having a hard time debugging. Turned up the output to a level "9" and I enabled debugging.
I am getting no returns when trying to create the first monitor.
There are no errors in the log, and there are no errors in the mysqld.log on the remote server

Debug message is
.DBG [LogOpts: level=DBG/DBG, screen=OFF, database=INF, logfile=DBG->/var/log/zoneminder/zm_debug.log.5656, weblog=INF, syslog=INF] at /usr/share/zoneminder/www/includes/logger.php line 168

/var/log/messages
Jan 9 11:02:53 tzone zmdc[1363]: WAR [Can't find process with command of 'zmc -m'] Jan 9 11:02:53 tzone zmdc[1363]: WAR [Can't find process with command of 'zmtrack.pl -m'] Jan 9 11:02:54 tzone zmdc[1363]: WAR [Can't find process with command of 'zma -m']

Anything else I can try?
That is all of the outputs that I know of.
This is brand new VM using PHP 5.3 and zmrepo.connortechnology.com Zoneminder 1.28.0-3 remotely connected to MySQL 5.6 database. Both servers have SELinux disbaled.

[newburns]

On ZM 1.27

I started a new VM with RPM install of Zoneminder 1.27 from zmrepo. Using PHP 5.3 and remote DB MySQL 5.6

First step, enable debugging and level "9"

Create new monitor, and error
[SQL-ERR 'Incorrect decimal value: '' for column 'MaxFPS' at row 1', statement was 'insert into Monitors set LinkedMonitors = '', Name = 'Monitor-1', Type = 'Remote', Function = 'Monitor', Enabled = '1', RefBlendPerc = '6', AlarmRefBlendPerc = '6', MaxFPS = '', AlarmMaxFPS = '', Device = '/dev/video0', Channel = '0', Format = '255', Palette = '0', LabelFormat = '%N - %d/%m/%y %H:%M:%S', LabelX = '0', LabelY = '0', ImageBufferCount = '50', WarmupCount = '25', PreEventCount = '25', PostEventCount = '25', StreamReplayBuffer = '1000', AlarmFrameCount = '1', Controllable = '0', ControlId = '', ControlDevice = '', ControlAddress = '', AutoStopTimeout = '', TrackMotion = '0', TrackDelay = '', ReturnLocation = '-1', ReturnDelay = '', EventPrefix = 'Event-', SectionLength = '600', FrameSkip = '0', FPSReportInterval = '1000', DefaultView = 'Events', DefaultRate = '100', DefaultScale = '100', WebColour = 'red', SignalCheckColour = '#0000c0', Protocol = 'http', Method = 'simple', Host = 'user:password@10.1.22.26', Port = '80', Path = '/video.cgi?', Colours = '3', Width = '320', Height = '240', Orientation = '0', Deinterlacing = '0', Sequence = 1']

Database seems to be installed correctly.
Use zmcreate.pl after altering db name

[SteveGilvarry]

I would say we should focus on 1.28 as most current release, I know you are trying to get a running version but swapping around is just confusing things.
Did you already post environment? I can try and replicate that your steps and see what I get. My Production version is the ppa but with local db.
I am on my phone at the moment, but I thought your db name in conf and sql were different, zw on one?

[SteveGilvarry]

Scratch that they are the same when I looked again.

[newburns]

When I created the new ZoneMinder 1.28 I changed it since the other db was
already on the server
My environment is virtual Centos 6.6 with php 5.5 using remote mysql 5.6 on
separate Centos system.

ZoneMinder is installed using default zmrepo rpm.

I have tested both php 5.3 and 5.5.

On Fri, Jan 9, 2015, 3:29 PM Steve Gilvarry notifications@github.com
wrote:

I would say we should focus on 1.28 as most current release, I know you
are trying to get a running version but swapping around is just confusing
things.
Did you already post environment? I can try and replicate that your steps
and see what I get. My Production version is the ppa but with local db.
I am on my phone at the moment, but I thought your db name in conf and sql
were different, zw on one?

—
Reply to this email directly or view it on GitHub
#649 (comment)
.

[newburns]

My environment is virtual.
Centos 6.6 with php 5.5 using remote mysql 5.6 on separate Centos system.

ZoneMinder is installed using default zmrepo rpm.

I have tested both php 5.3 and 5.5

I edit the /etc/zm.conf for the remote db credentials
Then I edit the zmcreate.pl and change the db name
Change the apache conf file
Change php.ini timezone
Start apache
Go to zm and add new monitor... Nothing happens
Turn on debug and level 9, add new monitor... Nothing happens

[SteveGilvarry]

Centos I have not played with but I will have a look as I can bring up some VMs quickly over the weekend. Not sure if I can help much but willing to try and see if I get same issues. I think it is going to be db connection related, @connortechnology is it ok to change DB post make or generally something as part of the build?

[kylejohnson]

If you want to pass me on credentials, I'd be happy to remote in and check on this issue.

[newburns]

Business environment. Can't get you through the firewall

On Fri, Jan 9, 2015, 3:54 PM Kyle Johnson notifications@github.com wrote:

If you want to pass me on credentials, I'd be happy to remote in and check
on this issue.

—
Reply to this email directly or view it on GitHub
#649 (comment)
.

[connortechnology]

I don't know what's going on... but from what I've of the logs it looks like mysql is being pedantic about empty fields vs undefined(NULL) fields...

I may try to bring up a vm wit 5.6 too.. but... anything non-debian is low for me.

[jornmoe]

I am pretty sure that I have this same issue but with a bit different environment: Opensuse 13.2, mysql 5.6.17 (mariadb) , php 5.6.1 and zoneminder 1.28

I have described my issues and findings in the zonminder forums (http://www.zoneminder.com/forums/viewtopic.php?f=32&t=22813&sid=f63f9cf02712a0c5cab3ea4cf8666ce3)

I will do som more debuging this weekend and report findings here... But my current gut feeling is also that is has something to do with how php/mysql combo handles NULL fields

PS. my system is private so I could give access to one of you developers if that would help...

[newburns]

I can't access my system over the weekend, but I see MySQL 5.6 introduced a default strict mode.
http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html
I wonder if that has something to do with it. If it does, we should probably adjust the sql commands instead of disabling strict mode.
Also, when ZoneMinder updates tables it updates all fields, not just the changed one. I had this issue earlier when updating a manually inserted monitor. Changed resolution and it had an error with maxfps can't be null. Never changed maxfps but I input 45 for both and it updated the monitor.
Lastly, if you manually insert a monitor you will have to remover the unsigned from the frames.score column. My monitors wouldn't record because of the invalid value for score.
#319

P.S.
I didn't know the forums were still active :o

[SteveGilvarry]

strict_trans_tables seems like the likely cause, turned it on on mysql5.5 and can no longer add monitors. I couldn't find a definitive reference for our case, but I did find some stuff talking about Insert and Updates with Not Null fields would throw this error. But documentation seemed to indicate this would only occur if a Default was not defined, but we do have empty string ''. I could see strict would not work with the implied Defaults, i.e. int is implied as 0 and varchar implied as '' if no Default specified, but should be OK using explicit Defaults to fill values not given.
The Insert and Update statements are built in actions.php, based on forms values so might be some fun to make all Insert and Updates compatible with Strict mode, but still worthwhile as they are going harder on strict in 5.7.

[jornmoe]

strict_trans_tables may be part of the problem, but it is not the complete problem. In the build instructions for opensuse you ar instructed to comment out this line in my.conf: sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

So i AM running mysql with STRICT_TRANS_TABLES turned off, and still have problems.

[jornmoe]

Just discovered that I am NOT using mariadb, but mysql-community-server.
Note to self: Assumptions is the mother of all f... ups :)

[SteveGilvarry]

Mmmmm very odd thought this was bang on. Before I bail on this option, can I ask you to indulge me and try sql_mode='' instead of just commenting out.
Maybe before and after check the setting with below, I know these are the runtime settings but I think the my.cnf settings should reflect.
SELECT @@GLOBAL.sql_mode; and/or
SELECT @@SESSION.sql_mode;

[jornmoe]

I repeat: Assumptions is the mother of all f... ups!

It turned out that the mysql installation does not use /etc/my.cnf as config file, but /usr/my.cnf

Commented out the line there and then before restart:

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0,00 sec)
+--------------------------------------------+

After restart:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0,00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| |
+--------------------+
1 row in set (0,00 sec)

And now things seems to work nicely...

[SteveGilvarry]

Good news so sounds like a confirmed workaround as per @newburns suggestion, but discussion around the correct fix to allow Strict is needed. Could be change field away from NOT NULLS, or ensure Insert and Update pass all values that are set as NOT NULL. We cannot be the first to hit the problem, but couldn't find any good discussions on resolving it.

[knight-of-ni]

@jornmoe Please do not make false statements in the forum that it is "dead". Just because you didn't get the answer you wanted from the forum does not mean it is dead.

[jornmoe]

Maybe dead is a bit harsh, and thats why i put it within exclamation marks. And my current impression and experience is that the developers have their attention to issues in github, not in the forum. Nothing wrong about that, but it would be nice if this was "documented" in the forum as I think most forum users hardly know that there excists discussions/issues at github that can be relevant for them.

And I actually had to go here to fin an answer to my problems.

Also the comment of newburns saying:
"P.S. I didn't know the forums were still active :o"

Made me draw the conclution that most developers does not pay attention to the forums any more!

[newburns]

Between @jornmoe and I, we can definitely say we appreciate everyone's assistance. The few that can be called developers are essential to this community, and we thank you.
I know a lot of what has been confusing is what to do in forums and what is different in GitHub. Thanks to kknight response, it is clear that forums are for configurations, and GitHub is for errors.
We can also appreciate the fact that we can actually help. This issue is ′ example. I have yet to be able to compile ZoneMinder, and I thought I was pretty useless in terms of helping. However, i was able to assist with this issue, (even though it was my own issue).
Point is, you don't have to know the in and out of ffmpeg to assist in the community...

[connortechnology]

If there are no cases where the db should accept "" instead of NULL, then I can quickly add a line to the db wrapper that looks for "" and replaces it with null.

As to the zoneminder.com forum, I for one check it mostly every day. I don't have answers to everyone's problems though.

[jornmoe]

I thought the problem was that zm tries to put NULL in NOT NULLS fields. With STRICT_TRANS_TABLES turned on, the transaction will then fail. Inserting empty string or 0 would resolve that. But one should really be considering whether all NOT NULLS fields really needs to be NOT NULLS? Reducing number of fields with that attribute would afaik reduce the imnpact of STRICT_TRANS_TABLES turned on.

Also setting defaults to NOT NULLS will override if Field is left empty. Actually, a field with NOT NULLS will accept a default of NULL as the default overrides NOT NULLS. At least that was what a mysql expert told me at work :-P

[connortechnology]

I seem to recall that NOT NULL was actually a performance enhancer for mysql....

The problem if you will go back up to the original post is that we are entering '' into an integer field. '' is not a valid integer. It is also, not a NULL value. Hence the error.

[newburns]

So the solution is to replace " with a 0 inside all the scripts? Where are the scripts, I'll start changing them now. What I'm assuming is that all of the scripts are "if null then " and I just change to "if null then 0"

[kylejohnson]

Also, when ZoneMinder updates tables it updates all fields, not just the changed one.

I think it comes down to this, and it is something that I've also experienced in working with my new frontend.

[connortechnology]

I don't there is anything wrong with updating all fields. That's how it works internally to the database anyways.

And no, don't replace '' with 0. If you do that, then fields that should be '' will become '0'
Replace '' with NULL but we have to make sure that any field we do that for can handle NULL.

[themotu]

still an issue, users get added to database just fine but monitors don't

[knight-of-ni]

@hrwebasst We are going need more information that just "this is still an issue". What distro are you using? What version of ZoneMinder are you using? How did you install ZoneMinder onto your system? What have you tried to get around the problem? Did you read through this thread and attempt to make the suggested changes to your mysql config? What do your logs say?

I'm running mysql 5.6 on Fedora 21 and have not experienced any database issues.
I didn't run into any database issues on CentOS 7, which ships with mariadb 5.5, either.

So far, this looks to be a database configuration issue, but without providing more data we don't have anyway to assist any further.

[themotu]

Sorry about that, I thought it was discussed that this is an issue with "default strict mode"... Adding monitors doesn't happen until I run:
SET @@global.sql_mode= '';
MYSQL version is 5.6.24

[knight-of-ni]

Closing due to inactivity