ERR: Can't insert frame: Out of range value for column 'Score' at row 1

[mwillfox]

I'm using ZoneMinder to capture an mjpeg stream from an ITS camera. My company has been using ZoneMinder for years with OpenSUSE 11.4 with great success, but I'm trying to move to OpenSUSE 13.1.

I just setup a brand new OpenSUSE 13.1 machine (physical machine, not VM) and installed ZM from the repo (version 1.26.4). It's running in conjunction with MariaDB 5.5.33.

When I tried to add my camera, I am receiving an error from zma "Can't insert frame: Out of range value for column 'Score' at row 1". This is from /src/zm_event.cpp line 568.

The schema in MariaDB shows the following for column Score in table Frames:

Field	Type	Null	Key	Default	Extra
Score	smallint(5) unsigned	NO		0

Could you offer any assistance here? Have I configured something improperly? I would love to get this running with OpenSUSE 13.1 since it was just picked up by Evergreen and we are moving our servers in that direction.

Thanks!

[knight-of-ni]

Yes, the schema looks right, and at the moment I don't have a good answer for you. More troubleshooting would be required.

You could be getting that error because the Score had a value of -1 or Null, which of course begs the question how that happened. Since the Score field is associated with a motion event, I would take a closer look at exactly when this error occurs. Not sure you could get that error by simply adding a new monitor.... at the moment it seems more likely that you might have something out of the ordinary in your zone.

[mwillfox]

My zone is the default, I did not change anything after I added the monitor. The settings show it has 100% pixel coverage and is of Type "Active". Is there something specific I should look for in the Zone definition?

Also, if I'm not mistaken a MySQL smallint unsigned has an acceptable range of 0 - 65535. If you look at /src/zm_event.cpp (line 568):

const char *frame_type = score>0?"Alarm":(score<0?"Bulk":"Normal");

score is an int that obviously can be set to a negative value (seemingly indicating "Bulk" frames).

With my database schema, if ZM tries to insert Bulk frames with a negative score it will produce the out of range error.

[mwillfox]

I went ahead and updated the MariaDB schema to the following for Frames.Score:

Field	Type	Null	Key	Default	Extra
Score	smallint(5)	NO		0

This fixed the issue. If I select from the Frames table for a specific event I see records like this:

EventId	FrameId	Type	TimeStamp	Delta	Score
150	1000	Bulk	2014-02-13 15:26:40	100.47	-1

I have no idea if this is a bug or just that the database schema does not match the new zm_event.cpp code. Judging by the fact that the frame type gets set to "Bulk" if the score is less than zero, I would assume the code is doing this intentionally. Maybe updating the schema to accept a signed value is all that's necessary.

[newburns]

I use a remote MySQL 5.6 installation.
I received the same error.
I removed the unsigned attribute, and everything seems to work fine now.
Not sure if that helps at all, but I am willing to assist debugging if there is anything I can look for specifically.

I changed the /etc/zm.conf file to match my remote database, and I am using the latest zoneminder from zmrepo by @connortechnology

[knight-of-ni]

Steps to Reproduce

It took a while to figure this out, but here are the steps to duplicate this issue:

• Run your dB engine with strict mode on (newer versions of mysql/mariadb ship with this turned on)
• Set any camera to Record or MoRecord

If either of these are not true, the error will not manifest itself.

Background

This link sums up what is happening perfectly:
http://www.tocker.ca/2014/01/14/making-strict-sql_mode-the-default.html

The logic in zm_event.cpp has indeed been attempting to write a value of "-1" to the score dB column. However, due to a "feature" of mysql, the dB engine would automagically translate a "-1" to "0" when trying to stuff the value into a field of type unsigned int, without so much as giving a warning. The refereed link describes this perfectly. This, in effect, covered up a bug that has been there since day one.

Workaround

The immediate workaround is to set sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in your mysql config as @newburns previously pointed out (thanks for discovering this).

Note that changing the field type to a signed int is not the recommended way to fix this. The Score, as written to the database, should always be >= 0.

Permanent Fix

PR #1016 fixes this permanently by resetting score to 0 if it is less than 0.

I would not be surprised if there are other parts of ZoneMinder that develop the same kind of problem.