search

ZoneMinder / zoneminder

ZoneMinder is a free, open source Closed-circuit television software application developed for Linux which supports IP, USB and Analog cameras.
http://www.zoneminder.com/
GNU General Public License v2.0
5.04k stars 1.22k forks source link

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

Open prometheanfire opened 1 year ago

prometheanfire commented 1 year ago

Describe Your Environment

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...

welcome[bot] commented 1 year ago

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 commented 1 year ago

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.

prometheanfire commented 1 year ago 
{
    "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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.

connortechnology commented 1 year ago

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