Solution: Incorrect integer value: '' for column zm.Monitors.ServerId

[TopView]

I can see that this bug has been reported previously, i.e. in #1555 and #1440

But SteveGilvarry's suggested fix:

https://zoneminder.readthedocs.io/en/latest/installationguide/ubuntu.html#easy-way-ubuntu-16-04

doesn't work for me, though I have gone thru that twice. And also I'm not using Ubuntu, though it is based on Debian.

---

But the good news is that I think I have found a solution to this bug ...(read on, and look for 'Immediate solution' below):

---

VERSION: zm v1.30.4; (v1.33.1 source has same issue).

INSTALLED: Installed from GNU/Linux Debian Stretch 9.6 repository using synaptic (apt).

IMPORTANT (and buggy) DEPENDENCIES
cakephp - v 2.8.5 (BTW, my zoneminder v1.30.4 says it depends on cakephp >= v2.8.5).

SYSTEM: $ uname -a
Linux Love2d 4.9.0-8-amd64 #1 SMP Debian 4.9.130-2 (2018-10-27) x86_64 GNU/Linux

WEB SERVER: $ sudo apache2 -v
Server version: Apache/2.4.25 (Debian)
Server built: 2018-11-03T18:46:19

DATABASE: $ mysql --version
mysql Ver 15.1 Distrib 10.2.21-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

---

Hi,

I see this bug just after a fresh install.

i click the Add New Monitors button, then in the Monitor dialog box, enter camera info as follows, (though this specific info doesn't seem to matter).

I change only the following, leaving the rest as the initial defaults:

General tab:
Name='Front',
Source Type='Remote';

Source tab:
Remote Protocol='RTSP',
Remote Method='RTP/RTSP',
Remote Host Name='192.168.1.3',
Remote Host Path='/11',
Capture width (pixels)=1920,
Capture Height (pixels)= 1080)

and then click the Save button and get the error message.

Expected behavior
No error message.

Error log

Text version of error message:

I connected to this database and did some testing and reading, and discovered that the 'ignore' modifier could be used to make this sql work, ... so:
A clear and concise description of what the bug is.
SQL-ERR 'SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column zm.Monitors.ServerId at row 1', statement was 'insert into Monitors set LinkedMonitors = '', Name = 'Front', ServerId = '', Type = 'Remote', Function = 'Monitor', Enabled = '1', RefBlendPerc = '6', AlarmRefBlendPerc = '6', AnalysisFPS = '', MaxFPS = '', AlarmMaxFPS = '', Device = '/dev/video0', Channel = '0', Format = '255', Palette = '0', V4LMultiBuffer = '', V4LCapturesPerFrame = '1', Options = '', LabelFormat = '%N - %Y-%m-%d %H:%M:%S %z', LabelX = '0', LabelY = '0', LabelSize = '1', ImageBufferCount = '50', WarmupCount = '25', PreEventCount = '25', PostEventCount = '25', StreamReplayBuffer = '1000', AlarmFrameCount = '1', EventPrefix = 'Event-', SectionLength = '600', FrameSkip = '0', MotionFrameSkip = '0', AnalysisUpdateDelay = '0', FPSReportInterval = '1000', DefaultView = 'Events', DefaultRate = '100', DefaultScale = '100', WebColour = 'red', Exif = '0', SignalCheckColour = '#0000c0', Protocol = 'rtsp', Method = 'rtpRtsp', Host = '192.168.1.3', Port = '80', Path = '/11', Colours = '3', Width = '1920', Height = '1080', Orientation = '0', Deinterlacing = '0', Sequence = 1'

Immediate solution
Error log shows a string is being inserted into an integer field.

If I then edit my /usr/share/zoneminder/api/lib/Cake/Model/Datasource/DboSource.php file and change lines 1975 and 3178 as follows:

- return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
+ return "INSERT IGNORE INTO {$table} ({$fields}) VALUES ({$values})";

- $sql = "INSERT INTO {$table} ({$fields}) VALUES ({$holder})";
+ $sql = "INSERT IGNORE INTO {$table} ({$fields}) VALUES ({$holder})";

Then I no longer get the error.

But because this file is really a symbolic link to cakephp that is where the trouble is!

(I will see if I can upgrade that and re-test.)

---

Analysis
This above, is not a very good fix, but it works for now.

Probably better to not be putting a string into an integer at all. It's just not a good idea in the first place.

Here's what the current MySQL manual says,

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE, such INSERT statements are aborted with an error. When INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched. For an example, see Section 19.2.2, “LIST Partitioning”.

Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

Furthermore, I suspect that a similar bug exists in other code lines. I've looked thru the code, but can't be sure which lines might be affected. I think there are about 15 other INSERT lines at risk, but both INSERT and APPEND are affected, so there are probably more.

I also left some two posts at the bottom of https://forums.zoneminder.com/viewtopic.php?f=34&t=24943&p=109421#p109421

I suspect that stuffing a string into an integer used to work in prior databases. I don't know for sure.

I also have been unable to find a suitable fix in my database server.

I considered submitting a commit to just fix these two lines, but I think a more carefully thought out patch might be better. At this time I'm not up to speed enough to submit that.

Again, this is a bug in the cakephp dependency.

Thanks for the help.

[welcome]

Thanks for opening your first issue here! Just a reminder, this forum is for Bug Reports only. Be sure to follow the issue template!

[connortechnology]

I find it hard to believe that this happens with 1.33.1. I definitely believe it happens in 1.30, because I spent a lot of time fixing it. I can certainly imagine a complex scenario where this could happen in 1.33.1, I can't imagine it happening easily. Here's why:

The entry for Server Id in the monitor edit popup defaults to auto. This causes the code to look for the least loaded server and assigns it to the new ServerId. In a fresh setup, there would be no servers, so the returned value would be null. (Unless your db is actually returning '' instead of NULL but that would just be weird.

Next, then we use the getFormChanges function and that now returns NULLs instead of ''.

So I would really like you to try again with 1.33.1. Please post the logs (preferably with debug turned on) from 1.33.1. We won't be fixing 1.30.

[TopView]

Hi Isaac,

First, I'm new to zm. Thank you for the work you've done on it!

---

In the zm v1.33.1 git I downloaded the file: web/includes/actions/monitor.php, on line# 161, has:

  $sql = 'INSERT INTO Monitors SET '.implode(', ', $changes);

I think this is an example of what is causing this bug. It's stuffing a bunch of single quoted strings into the Monitors table which has mixed field types, i.e. not all string. This produces MySLQ errors for fields which are not of type string.

---

Also last night, when I wrote, "(v1.33.1 source has same issue)", I had not yet realized that in my version of zm, the Cake folder's files are soft linked all of the way outside of the zm source tree, and back up into the cake root directory. At first I got this wrong, and thought that the buggy php was inside zm.

So this bug is NOT in zm, but rather a bug in the cakephp package. Also part and parcel of that is that it has to do with the version of cakephp that I am using.

The only control zm might have is to require a newer version of cakephp, but that version is not available in my Debian repo.

Furthermore, the cakephp package in my repo, doesn't even specify which mysql it is dependent on. lol:

---

[wringing hands]

So today, I'll do some more work on this and see if there is some way I can get the cakephp package upgraded a bit without breaking zm.

Failing that, and possibly in parallel, I might be able to write a patch for the existing cakephp package that will fix all of the places this is broken in.

Bottom line: In the zm version I'm using I think the issue is mostly incompatibility between newer MariaDB (and from the looks of it even the newer MySQL) and older cakephp interface. It's hard for me to tell fully what's going on in the new version of zm, but from the line I cited above it might also have an issue.

-- Howard

I find it hard to believe that this happens with 1.33.1. I definitely believe it happens in 1.30, because I spent a lot of time fixing it. I can certainly imagine a complex scenario where this could happen in 1.33.1, I can't imagine it happening easily. Here's why:

The entry for Server Id in the monitor edit popup defaults to auto. This causes the code to look for the least loaded server and assigns it to the new ServerId. In a fresh setup, there would be no servers, so the returned value would be null. (Unless your db is actually returning '' instead of NULL but that would just be weird.

Next, then we use the getFormChanges function and that now returns NULLs instead of ''.

So I would really like you to try again with 1.33.1. Please post the logs (preferably with debug turned on) from 1.33.1. We won't be fixing 1.30.

[TopView]

I finally got zoneminder to work after using kate to edit both zm and cake php files, where lots of places I inserted 'IGNORE' between all 'INSERT INTO' and after 'APPEND ' sql statements. (This was a little risky, but so far it's working and has bypassed the above described issue.)

I will try someday to move to 1.33, but I'm not ready for that challenge yet, especially in light of the, "Building from Source is Discouraged" comment in /usr/share/doc/zoneminder/README.md. For now I figured I would first try to learn on the older version before jumping into water that is too deep for me. I had to re-learn my apache and php stuff, as it was very old and rusty in my brain. Thanks again for the hand up. Cheers!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[TopView]

In reply to stale bot: I hope to work on this more this summer (northern hemisphere), so want to keep this open till then. Thanks.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.