(FreeBSD) SQL Error on new monitor add

[abishai]

I think MySQL settings on FreeBSD are more strict and it doesn't allow incorrect integer values when no servers are used (default ZM settings). In this case, empty values should be converted to 0 or default server added to Servers table

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 = 'Monitor', Enabled = '1', RefBlendPerc = '6', AlarmRefBlendPerc = '6', AnalysisFPS = '', MaxFPS = '', AlarmMaxFPS = '', Options = '', LabelFormat = '%N - %d/%m/%y %H:%M:%S', 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', Protocol = 'http', Method = 'simple', Host = 'admin:admin@192.168.1.101', Port = '80', Path = '', Colours = '3', Width = '320', Height = '240', Orientation = '0', Deinterlacing = '0', Sequence = 1'

[abishai]

A lot of similar errors, actually.

SQL-ERR 'SQLSTATE[HY000]: General error: 1366 Incorrect decimal value: '' for column 'AnalysisFPS' at row 1', statement was 'insert into Monitors set LinkedMonitors = '', Name = 'Monitor-1', ServerId = '1', Type = 'Remote', Function = 'Monitor', Enabled = '1', RefBlendPerc = '6', AlarmRefBlendPerc = '6', AnalysisFPS = '', MaxFPS = '', AlarmMaxFPS = '', Options = '', LabelFormat = '%N - %d/%m/%y %H:%M:%S', 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', Protocol = 'http', Method = 'simple', Host = 'admin:admin@192.168.1.101', Port = '80', Path = '/channel1', Colours = '3', Width = '2048', Height = '1536', Orientation = '0', Deinterlacing = '0', Sequence = 1'

[abishai]

PHP notices in SQL query!

SQL-ERR 'SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'User' at row 1', statement was 'insert into Monitors set LinkedMonitors = '', Protocol = 'http', Host = 'admin:admin@192.168.1.101', Port = '80', Method = 'simple', Options = '', Path = '/channel1', User = '
\r\nNotice: Undefined index: User in /usr/local/www/zoneminder/skins/classic/views/monitor.php on line 556
', Pass = '
\r\nNotice: Undefined index: Pass in /usr/local/www/zoneminder/skins/classic/views/monitor.php on line 557
', Colours = '3', Width = '320', Height = '240', Orientation = '0', Deinterlacing = '0', RTSPDescribe = '
\r\nNotice: Undefined index: RTSPDescribe in /usr/local/www/zoneminder/skins/classic/views/monitor.php on line 573
', LabelFormat = '%N - %d/%m/%y %H:%M:%S', 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 = '
\r\nNotice: Undefined index: Exif in /usr/local/www/zoneminder/skins/classic/views/monitor.php on line 631
', Name = 'Monitor-1', ServerId = '1', Type = 'Remote', Function = 'Monitor', Enabled = '1', AnalysisFPS = '100', MaxFPS = '100', AlarmMaxFPS = '100', RefBlendPerc = '6', AlarmRefBlendPerc = '6', Sequence = 1'

[abishai]

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

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

[abishai]

Looks like FreeBSD installs my.cnf with STRICT_TRANS_TABLES set. I don't know if it's default MySQL setting, but I strongly approve what it's doing. Fixing the project, however, can be very boring.

[knight-of-ni]

This sounds like a duplicate of #319

[abishai]

Yep, didn't find.
I ported some time ago one site from MySQL to PostgreSQL (rather restrictive thing) and similar errors were real pain.