zoneminder icon indicating copy to clipboard operation
zoneminder copied to clipboard
verified publisher icon ZoneMinder

monitors api broken in 1.37.27 (/zm/api/monitors.json)

Open prometheanfire opened this issue 3 years ago • 9 comments

Describe Your Environment

  • Version of ZoneMinder: 1.37.27
  • kubernetes
  • Alpine
  • N/A

Describe the bug Syntax error or access violation: 1055 'zm.Monitor.Name' isn't in GROUP BY when trying to fetch curl http://server/zm/api/monitors.json

curl http://server/zm/api/monitors/1.json works just fine

To Reproduce Steps to reproduce the behavior:

  1. install 1.37.27
  2. set up monitor
  3. enable api
  4. try to fetch monitors via api
  5. See error

Expected behavior The API works

Debug Logs no debug logs found, only a 500 showed up

Thoughts It looks like the data model in sql changed for the 1.38 development and the api code hasn't been refactored to adjust. Just a thought though...

prometheanfire avatar Nov 16 '22 06:11 prometheanfire

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

welcome[bot] avatar Nov 16 '22 06:11 welcome[bot]

This is weird because it works fine for me here. I wonder if it matters what version of mysql server is being used.

You could turn on cakephp debugging and it should log the sql being used which might shed some light. edit /usr/share/zoneminder/www/api/app/Config/core.php and change debug to 1 or 2. Look in /var/log/zm/cake_*.log for more info. These paths are what is appropriate for ubuntu, likely not correct for alpine.

connortechnology avatar Nov 17 '22 13:11 connortechnology 

{
    "success": false,
    "data": {
        "code": 500,
        "name": "SQLSTATE[42000]: Syntax error or access violation: 1055 'zm.Monitor.Name' isn't in GROUP BY",
        "message": "SQLSTATE[42000]: Syntax error or access violation: 1055 'zm.Monitor.Name' isn't in GROUP BY",
        "url": "\/api\/monitors.json?p=%2Fmonitors.json",
        "error": {
            "errorInfo": [
                "42000",
                1055,
                "'zm.Monitor.Name' isn't in GROUP BY"
            ],
            "queryString": "SELECT `Monitor`.`Id`, `Monitor`.`Name`, `Monitor`.`Notes`, `Monitor`.`ServerId`, `Monitor`.`StorageId`, `Monitor`.`ManufacturerId`, `Monitor`.`ModelId`, `Monitor`.`Type`, `Monitor`.`Function`, `Monitor`.`Capturing`, `Monitor`.`Analysing`, `Monitor`.`AnalysisSource`, `Monitor`.`AnalysisImage`, `Monitor`.`Recording`, `Monitor`.`Enabled`, `Monitor`.`DecodingEnabled`, `Monitor`.`Decoding`, `Monitor`.`JanusEnabled`, `Monitor`.`JanusAudioEnabled`, `Monitor`.`Janus_Profile_Override`, `Monitor`.`Janus_Use_RTSP_Restream`, `Monitor`.`Janus_RTSP_User`, `Monitor`.`LinkedMonitors`, `Monitor`.`Triggers`, `Monitor`.`EventStartCommand`, `Monitor`.`EventEndCommand`, `Monitor`.`ONVIF_URL`, `Monitor`.`ONVIF_Username`, `Monitor`.`ONVIF_Password`, `Monitor`.`ONVIF_Options`, `Monitor`.`ONVIF_Event_Listener`, `Monitor`.`ONVIF_Alarm_Text`, `Monitor`.`use_Amcrest_API`, `Monitor`.`Device`, `Monitor`.`Channel`, `Monitor`.`Format`, `Monitor`.`V4LMultiBuffer`, `Monitor`.`V4LCapturesPerFrame`, `Monitor`.`Protocol`, `Monitor`.`Method`, `Monitor`.`Host`, `Monitor`.`Port`, `Monitor`.`SubPath`, `Monitor`.`Path`, `Monitor`.`SecondPath`, `Monitor`.`Options`, `Monitor`.`User`, `Monitor`.`Pass`, `Monitor`.`Width`, `Monitor`.`Height`, `Monitor`.`Colours`, `Monitor`.`Palette`, `Monitor`.`Orientation`, `Monitor`.`Deinterlacing`, `Monitor`.`DecoderHWAccelName`, `Monitor`.`DecoderHWAccelDevice`, `Monitor`.`SaveJPEGs`, `Monitor`.`VideoWriter`, `Monitor`.`OutputCodec`, `Monitor`.`Encoder`, `Monitor`.`OutputContainer`, `Monitor`.`EncoderParameters`, `Monitor`.`RecordAudio`, `Monitor`.`RecordingSource`, `Monitor`.`RTSPDescribe`, `Monitor`.`Brightness`, `Monitor`.`Contrast`, `Monitor`.`Hue`, `Monitor`.`Colour`, `Monitor`.`EventPrefix`, `Monitor`.`LabelFormat`, `Monitor`.`LabelX`, `Monitor`.`LabelY`, `Monitor`.`LabelSize`, `Monitor`.`ImageBufferCount`, `Monitor`.`MaxImageBufferCount`, `Monitor`.`WarmupCount`, `Monitor`.`PreEventCount`, `Monitor`.`PostEventCount`, `Monitor`.`StreamReplayBuffer`, `Monitor`.`AlarmFrameCount`, `Monitor`.`SectionLength`, `Monitor`.`MinSectionLength`, `Monitor`.`FrameSkip`, `Monitor`.`MotionFrameSkip`, `Monitor`.`AnalysisFPSLimit`, `Monitor`.`AnalysisUpdateDelay`, `Monitor`.`MaxFPS`, `Monitor`.`AlarmMaxFPS`, `Monitor`.`FPSReportInterval`, `Monitor`.`RefBlendPerc`, `Monitor`.`AlarmRefBlendPerc`, `Monitor`.`Controllable`, `Monitor`.`ControlId`, `Monitor`.`ControlDevice`, `Monitor`.`ControlAddress`, `Monitor`.`AutoStopTimeout`, `Monitor`.`TrackMotion`, `Monitor`.`TrackDelay`, `Monitor`.`ReturnLocation`, `Monitor`.`ReturnDelay`, `Monitor`.`ModectDuringPTZ`, `Monitor`.`DefaultRate`, `Monitor`.`DefaultScale`, `Monitor`.`DefaultCodec`, `Monitor`.`SignalCheckPoints`, `Monitor`.`SignalCheckColour`, `Monitor`.`WebColour`, `Monitor`.`Exif`, `Monitor`.`Sequence`, `Monitor`.`ZoneCount`, `Monitor`.`Refresh`, `Monitor`.`Latitude`, `Monitor`.`Longitude`, `Monitor`.`RTSPServer`, `Monitor`.`RTSPStreamName`, `Monitor`.`Importance`, `Monitor`.`MQTT_Enabled`, `Monitor`.`MQTT_Subscriptions`, `Manufacturer`.`Id`, `Manufacturer`.`Name`, `CameraModel`.`Id`, `CameraModel`.`Name`, `CameraModel`.`ManufacturerId`, `Monitor_Status`.`MonitorId`, `Monitor_Status`.`Status`, `Monitor_Status`.`CaptureFPS`, `Monitor_Status`.`AnalysisFPS`, `Monitor_Status`.`CaptureBandwidth`, `Monitor_Status`.`UpdatedOn`, `Event_Summary`.`MonitorId`, `Event_Summary`.`TotalEvents`, `Event_Summary`.`TotalEventDiskSpace`, `Event_Summary`.`HourEvents`, `Event_Summary`.`HourEventDiskSpace`, `Event_Summary`.`DayEvents`, `Event_Summary`.`DayEventDiskSpace`, `Event_Summary`.`WeekEvents`, `Event_Summary`.`WeekEventDiskSpace`, `Event_Summary`.`MonthEvents`, `Event_Summary`.`MonthEventDiskSpace`, `Event_Summary`.`ArchivedEvents`, `Event_Summary`.`ArchivedEventDiskSpace` FROM `zm`.`Monitors` AS `Monitor` LEFT JOIN `zm`.`Manufacturers` AS `Manufacturer` ON (`Monitor`.`ManufacturerId` = `Manufacturer`.`Id`) LEFT JOIN `zm`.`Models` AS `CameraModel` ON (`Monitor`.`ModelId` = `CameraModel`.`Id`) LEFT JOIN `zm`.`Monitor_Status` AS `Monitor_Status` ON (`Monitor_Status`.`MonitorId` = `Monitor`.`Id`) LEFT JOIN `zm`.`Event_Summaries` AS `Event_Summary` ON (`Event_Summary`.`MonitorId` = `Monitor`.`Id`) left JOIN `zm`.`Groups_Monitors`  ON (`Groups_Monitors`.`MonitorId` = `Monitor`.`Id`)  WHERE 1 = 1  GROUP BY `Monitor`.`Id`"
        },
        "exception": {
            "class": "PDOException",
            "code": "42000",
            "message": "SQLSTATE[42000]: Syntax error or access violation: 1055 'zm.Monitor.Name' isn't in GROUP BY",
            "trace": [
                "#0 \/var\/www\/html\/api\/lib\/Cake\/Model\/Datasource\/DboSource.php(502): PDOStatement->execute()",
                "#1 \/var\/www\/html\/api\/lib\/Cake\/Model\/Datasource\/DboSource.php(468): DboSource->_execute()",
                "#2 \/var\/www\/html\/api\/lib\/Cake\/Model\/Datasource\/DboSource.php(715): DboSource->execute()",
                "#3 \/var\/www\/html\/api\/lib\/Cake\/Model\/Datasource\/DboSource.php(1226): DboSource->fetchAll()",
                "#4 \/var\/www\/html\/api\/lib\/Cake\/Model\/Model.php(3053): DboSource->read()",
                "#5 \/var\/www\/html\/api\/lib\/Cake\/Model\/Model.php(3025): Model->_readDataSource()",
                "#6 \/var\/www\/html\/api\/app\/Controller\/MonitorsController.php(67): Model->find()",
                "#7 [internal function]: MonitorsController->index()",
                "#8 \/var\/www\/html\/api\/lib\/Cake\/Controller\/Controller.php(499): ReflectionMethod->invokeArgs()",
                "#9 \/var\/www\/html\/api\/app\/Plugin\/Crud\/Lib\/CrudControllerTrait.php(31): Controller->invokeAction()",
                "#10 \/var\/www\/html\/api\/lib\/Cake\/Routing\/Dispatcher.php(193): AppController->invokeAction()",
                "#11 \/var\/www\/html\/api\/lib\/Cake\/Routing\/Dispatcher.php(167): Dispatcher->_invoke()",
                "#12 \/var\/www\/html\/api\/app\/webroot\/index.php(117): Dispatcher->dispatch()",
                "#13 \/var\/www\/html\/api\/index.php(41): require('\/var\/www\/html\/a...')",
                "#14 {main}"
            ]
        }
    }
}

the DB version is 10.9-debian-11

prometheanfire avatar Nov 20 '22 22:11 prometheanfire

this query works, but everything selected seems to need to be in the group by

`SELECT `Monitor`.`Id`, `Monitor`.`Name`, `Monitor`.`Notes`, `Monitor`.`ServerId`, `Monitor`.`StorageId`, `Monitor`.`ManufacturerId`, `Monitor`.`ModelId`, `Monitor`.`Type`, `Monitor`.`Function`, `Monitor`.`Capturing`, `Monitor`.`Analysing`, `Monitor`.`AnalysisSource`, `Monitor`.`AnalysisImage`, `Monitor`.`Recording`, `Monitor`.`Enabled`, `Monitor`.`DecodingEnabled`, `Monitor`.`Decoding`, `Monitor`.`JanusEnabled`, `Monitor`.`JanusAudioEnabled`, `Monitor`.`Janus_Profile_Override`, `Monitor`.`Janus_Use_RTSP_Restream`, `Monitor`.`Janus_RTSP_User`, `Monitor`.`LinkedMonitors`, `Monitor`.`Triggers`, `Monitor`.`EventStartCommand`, `Monitor`.`EventEndCommand`, `Monitor`.`ONVIF_URL`, `Monitor`.`ONVIF_Username`, `Monitor`.`ONVIF_Password`, `Monitor`.`ONVIF_Options`, `Monitor`.`ONVIF_Event_Listener`, `Monitor`.`ONVIF_Alarm_Text`, `Monitor`.`use_Amcrest_API`, `Monitor`.`Device`, `Monitor`.`Channel`, `Monitor`.`Format`, `Monitor`.`V4LMultiBuffer`, `Monitor`.`V4LCapturesPerFrame`, `Monitor`.`Protocol`, `Monitor`.`Method`, `Monitor`.`Host`, `Monitor`.`Port`, `Monitor`.`SubPath`, `Monitor`.`Path`, `Monitor`.`SecondPath`, `Monitor`.`Options`, `Monitor`.`User`, `Monitor`.`Pass`, `Monitor`.`Width`, `Monitor`.`Height`, `Monitor`.`Colours`, `Monitor`.`Palette`, `Monitor`.`Orientation`, `Monitor`.`Deinterlacing`, `Monitor`.`DecoderHWAccelName`, `Monitor`.`DecoderHWAccelDevice`, `Monitor`.`SaveJPEGs`, `Monitor`.`VideoWriter`, `Monitor`.`OutputCodec`, `Monitor`.`Encoder`, `Monitor`.`OutputContainer`, `Monitor`.`EncoderParameters`, `Monitor`.`RecordAudio`, `Monitor`.`RecordingSource`, `Monitor`.`RTSPDescribe`, `Monitor`.`Brightness`, `Monitor`.`Contrast`, `Monitor`.`Hue`, `Monitor`.`Colour`, `Monitor`.`EventPrefix`, `Monitor`.`LabelFormat`, `Monitor`.`LabelX`, `Monitor`.`LabelY`, `Monitor`.`LabelSize`, `Monitor`.`ImageBufferCount`, `Monitor`.`MaxImageBufferCount`, `Monitor`.`WarmupCount`, `Monitor`.`PreEventCount`, `Monitor`.`PostEventCount`, `Monitor`.`StreamReplayBuffer`, `Monitor`.`AlarmFrameCount`, `Monitor`.`SectionLength`, `Monitor`.`MinSectionLength`, `Monitor`.`FrameSkip`, `Monitor`.`MotionFrameSkip`, `Monitor`.`AnalysisFPSLimit`, `Monitor`.`AnalysisUpdateDelay`, `Monitor`.`MaxFPS`, `Monitor`.`AlarmMaxFPS`, `Monitor`.`FPSReportInterval`, `Monitor`.`RefBlendPerc`, `Monitor`.`AlarmRefBlendPerc`, `Monitor`.`Controllable`, `Monitor`.`ControlId`, `Monitor`.`ControlDevice`, `Monitor`.`ControlAddress`, `Monitor`.`AutoStopTimeout`, `Monitor`.`TrackMotion`, `Monitor`.`TrackDelay`, `Monitor`.`ReturnLocation`, `Monitor`.`ReturnDelay`, `Monitor`.`ModectDuringPTZ`, `Monitor`.`DefaultRate`, `Monitor`.`DefaultScale`, `Monitor`.`DefaultCodec`, `Monitor`.`SignalCheckPoints`, `Monitor`.`SignalCheckColour`, `Monitor`.`WebColour`, `Monitor`.`Exif`, `Monitor`.`Sequence`, `Monitor`.`ZoneCount`, `Monitor`.`Refresh`, `Monitor`.`Latitude`, `Monitor`.`Longitude`, `Monitor`.`RTSPServer`, `Monitor`.`RTSPStreamName`, `Monitor`.`Importance`, `Monitor`.`MQTT_Enabled`, `Monitor`.`MQTT_Subscriptions`, `Manufacturer`.`Id`, `Manufacturer`.`Name`, `CameraModel`.`Id`, `CameraModel`.`Name`, `CameraModel`.`ManufacturerId`, `Monitor_Status`.`MonitorId`, `Monitor_Status`.`Status`, `Monitor_Status`.`CaptureFPS`, `Monitor_Status`.`AnalysisFPS`, `Monitor_Status`.`CaptureBandwidth`, `Monitor_Status`.`UpdatedOn`, `Event_Summary`.`MonitorId`, `Event_Summary`.`TotalEvents`, `Event_Summary`.`TotalEventDiskSpace`, `Event_Summary`.`HourEvents`, `Event_Summary`.`HourEventDiskSpace`, `Event_Summary`.`DayEvents`, `Event_Summary`.`DayEventDiskSpace`, `Event_Summary`.`WeekEvents`, `Event_Summary`.`WeekEventDiskSpace`, `Event_Summary`.`MonthEvents`, `Event_Summary`.`MonthEventDiskSpace`, `Event_Summary`.`ArchivedEvents`, `Event_Summary`.`ArchivedEventDiskSpace` FROM `zm`.`Monitors` AS `Monitor` LEFT JOIN `zm`.`Manufacturers` AS `Manufacturer` ON (`Monitor`.`ManufacturerId` = `Manufacturer`.`Id`) LEFT JOIN `zm`.`Models` AS `CameraModel` ON (`Monitor`.`ModelId` = `CameraModel`.`Id`) LEFT JOIN `zm`.`Monitor_Status` AS `Monitor_Status` ON (`Monitor_Status`.`MonitorId` = `Monitor`.`Id`) LEFT JOIN `zm`.`Event_Summaries` AS `Event_Summary` ON (`Event_Summary`.`MonitorId` = `Monitor`.`Id`) left JOIN `zm`.`Groups_Monitors`  ON (`Groups_Monitors`.`MonitorId` = `Monitor`.`Id`)  WHERE 1 = 1  GROUP BY `Monitor`.`Name`, `Monitor`.`Id`, `Monitor`.`Notes`, `Monitor`.`ServerId`, `Monitor`.`StorageId`, `Monitor`.`ManufacturerId`, `Monitor`.`ModelId`, `Monitor`.`Type`, `Monitor`.`Function`, `Monitor`.`Capturing`, `Monitor`.`Analysing`, `Monitor`.`AnalysisSource`, `Monitor`.`AnalysisImage`, `Monitor`.`Recording`, `Monitor`.`Enabled`, `Monitor`.`DecodingEnabled`, `Monitor`.`Decoding`, `Monitor`.`JanusEnabled`, `Monitor`.`JanusAudioEnabled`, `Monitor`.`Janus_Profile_Override`, `Monitor`.`Janus_Use_RTSP_Restream`, `Monitor`.`Janus_RTSP_User`, `Monitor`.`LinkedMonitors`, `Monitor`.`Triggers`, `Monitor`.`EventStartCommand`, `Monitor`.`EventEndCommand`, `Monitor`.`ONVIF_URL`, `Monitor`.`ONVIF_Username`, `Monitor`.`ONVIF_Password`, `Monitor`.`ONVIF_Options`, `Monitor`.`ONVIF_Event_Listener`, `Monitor`.`ONVIF_Alarm_Text`, `Monitor`.`use_Amcrest_API`, `Monitor`.`Device`, `Monitor`.`Channel`, `Monitor`.`Format`, `Monitor`.`V4LMultiBuffer`, `Monitor`.`V4LCapturesPerFrame`, `Monitor`.`Protocol`, `Monitor`.`Method`, `Monitor`.`Host`, `Monitor`.`Port`, `Monitor`.`SubPath`, `Monitor`.`Path`, `Monitor`.`SecondPath`, `Monitor`.`Options`, `Monitor`.`User`, `Monitor`.`Pass`, `Monitor`.`Width`, `Monitor`.`Height`, `Monitor`.`Colours`, `Monitor`.`Palette`, `Monitor`.`Orientation`, `Monitor`.`Deinterlacing`, `Monitor`.`DecoderHWAccelName`, `Monitor`.`DecoderHWAccelDevice`, `Monitor`.`SaveJPEGs`, `Monitor`.`VideoWriter`, `Monitor`.`OutputCodec`, `Monitor`.`Encoder`, `Monitor`.`OutputContainer`, `Monitor`.`EncoderParameters`, `Monitor`.`RecordAudio`, `Monitor`.`RecordingSource`, `Monitor`.`RTSPDescribe`, `Monitor`.`Brightness`, `Monitor`.`Contrast`, `Monitor`.`Hue`, `Monitor`.`Colour`, `Monitor`.`EventPrefix`, `Monitor`.`LabelFormat`, `Monitor`.`LabelX`, `Monitor`.`LabelY`, `Monitor`.`LabelSize`, `Monitor`.`ImageBufferCount`, `Monitor`.`MaxImageBufferCount`, `Monitor`.`WarmupCount`, `Monitor`.`PreEventCount`, `Monitor`.`PostEventCount`, `Monitor`.`StreamReplayBuffer`, `Monitor`.`AlarmFrameCount`, `Monitor`.`SectionLength`, `Monitor`.`MinSectionLength`, `Monitor`.`FrameSkip`, `Monitor`.`MotionFrameSkip`, `Monitor`.`AnalysisFPSLimit`, `Monitor`.`AnalysisUpdateDelay`, `Monitor`.`MaxFPS`, `Monitor`.`AlarmMaxFPS`, `Monitor`.`FPSReportInterval`, `Monitor`.`RefBlendPerc`, `Monitor`.`AlarmRefBlendPerc`, `Monitor`.`Controllable`, `Monitor`.`ControlId`, `Monitor`.`ControlDevice`, `Monitor`.`ControlAddress`, `Monitor`.`AutoStopTimeout`, `Monitor`.`TrackMotion`, `Monitor`.`TrackDelay`, `Monitor`.`ReturnLocation`, `Monitor`.`ReturnDelay`, `Monitor`.`ModectDuringPTZ`, `Monitor`.`DefaultRate`, `Monitor`.`DefaultScale`, `Monitor`.`DefaultCodec`, `Monitor`.`SignalCheckPoints`, `Monitor`.`SignalCheckColour`, `Monitor`.`WebColour`, `Monitor`.`Exif`, `Monitor`.`Sequence`, `Monitor`.`ZoneCount`, `Monitor`.`Refresh`, `Monitor`.`Latitude`, `Monitor`.`Longitude`, `Monitor`.`RTSPServer`, `Monitor`.`RTSPStreamName`, `Monitor`.`Importance`, `Monitor`.`MQTT_Enabled`, `Monitor`.`MQTT_Subscriptions`, `Manufacturer`.`Id`, `Manufacturer`.`Name`, `CameraModel`.`Id`, `CameraModel`.`Name`, `CameraModel`.`ManufacturerId`, `Monitor_Status`.`MonitorId`, `Monitor_Status`.`Status`, `Monitor_Status`.`CaptureFPS`, `Monitor_Status`.`AnalysisFPS`, `Monitor_Status`.`CaptureBandwidth`, `Monitor_Status`.`UpdatedOn`, `Event_Summary`.`MonitorId`, `Event_Summary`.`TotalEvents`, `Event_Summary`.`TotalEventDiskSpace`, `Event_Summary`.`HourEvents`, `Event_Summary`.`HourEventDiskSpace`, `Event_Summary`.`DayEvents`, `Event_Summary`.`DayEventDiskSpace`, `Event_Summary`.`WeekEvents`, `Event_Summary`.`WeekEventDiskSpace`, `Event_Summary`.`MonthEvents`, `Event_Summary`.`MonthEventDiskSpace`, `Event_Summary`.`ArchivedEvents`, `Event_Summary`.`ArchivedEventDiskSpace`;

prometheanfire avatar Nov 20 '22 22:11 prometheanfire

running SET SESSION sql_mode=''; before the query allows it to run as well. Looks like requiring everything selected to be in the group_by is part of the sql standard and managed by the ONLY_FULL_GROUP_BY flag.

prometheanfire avatar Nov 20 '22 22:11 prometheanfire

default (global) sql mode is ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

prometheanfire avatar Nov 20 '22 22:11 prometheanfire

looks like this is where the query is run, I think (I haven't done PHP in a LONG time).

https://github.com/ZoneMinder/zoneminder/blob/master/web/api/app/Controller/MonitorsController.php#L53-L66

prometheanfire avatar Nov 20 '22 23:11 prometheanfire

not sure if the group by is needed, but I removed the group by with

sed -i "/'\`Monitor\`.\`Id\`'/d" /var/www/html/api/app/Controller/MonitorsController.php

and the api works well enough for home-assistant to connect.

prometheanfire avatar Nov 20 '22 23:11 prometheanfire

The group by was added by @gonzalezcalleja in 2020 to prevent duplicate monitors in the results. Perhaps he can chime in

connortechnology avatar Dec 06 '22 08:12 connortechnology