[SOLVED] Incorrect integer value: '' for column 'ServerId' at row 1'

[craver84]

Hi everyone,
I've just install ZM 1.29 on ubuntu core 16.04 following this guide
https://bkjaya.wordpress.com/2016/04/23 ... ntu-16-04/

when I try to add a new camera
on the first tab "General" the second item "Server" can only be "none", and I get this error:


SQL-ERR 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'ServerId' at row 1', statement was 'insert into Monitors set LinkedMonitors = '', Name = 'Monitor-1', ServerId = '', Type = 'Remote', Function = 'Record', 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 = 'http', Method = 'simple', Host = 'user:pass@10.0.1.202', Port = '80', Path = '/axis-cgi/mjpg/video.cgi?resolution=640x480&fps=10', Colours = '3', Width = '320', Height = '240', Orientation = '0', Deinterlacing = '0', Sequence = 1'


any idea?
thanks, Paolo

[SteveGilvarry]

Try the source of this blog post, https://wiki.zoneminder.com/Ubuntu_Serv ... e_easy_way.

But even following the blog post you missed or stuffed the SQL_Mode step.

[craver84]

Reinstalled ubuntu following that guide.
it works! Thanks!

[Rizome]

SOLVED?, i don't think so.

I'm seeing this problem too in v1.30.4. But the installation notes refereed to above didn't fix it for me.

It occurs when I try to create my first monitor, with 'Add New Monitors' when I finally click 'Save'.

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:

The solution begins by first modifying iine 590 in /usr/shared/zoneminder/www/includes/actions.php as follows:

Code: Select all

Is:          dbQuery( "insert into Monitors set ".implode( ", ", $changes ) );
Change to:   dbQuery( "insert ignore into Monitors set ".implode( ", ", $changes ) );

The error is occurring when SQL tries to insert a string into an integer field. This is a bad idea. I suspect it used to work by default, and then later versions of MySQL and MariaDB which I use added the ignore to clarify that the errors were being glossed over. 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.

Once done I can create a new Monitor.

BUT, this also means that every SQL INSERT and APPEND is possibly at risk for this bug, and furthermore, it's probably a bug to be inserting strings into integers in the first place.

So it looks like it's time to go to github and find out more...

[Rizome]

Looking at the latest source, at https://github.com/ZoneMinder/zoneminde ... onitor.php it looks like this is still inserting a whole bunch of strings (a list) into the Monitors table, which is where this bug is coming from here, and possibly a bunch of other places.

Code: Select all

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

I think this bug relates to not only sql insert, but also sql append.

I've looked all over for some global configuration setting which might override this but so far haven't found one. And as suggested elsewhere sql-mode NO_ENGINE_SUBSTITUTION doesn't seem to affect this behavior.

BUT, if I was to submit a patch to the source to fix all of these by adding 'ignore' into the source code, does that break anything???

(And yes, I know the better way to fix this is to rewrite the code to not stuff strings into integers, but will have to work on that later, once I see if this works.)

[iconnor]

DOn't both with that patch. We won't merge it.

The right thing to do is to use the Monitor object which will valid and set to NULL each value before inserting.