Large number of 'column "t0.max_load" must appear in the GROUP BY' errors in opencast.log

Steps to reproduce

Steps to reproduce:
1. Install a clean version of 7.2 (also seems to happen in 7.1 and 7.0.)
2. Use PostgreSQL as your backend database.
3. Run Opencast and wait for it to start up.

Actual Results:

The following error appears in the log repeatedly and frequently. In the admin interface, I believe none of the stats in Systems -> Services also don't appear:

2019-08-17T23:30:45,131 | WARN | (JavaLog:302) -
org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "t0.max_load" must appear in the GROUP BY clause or be used in an aggregate function
Position: 46
Error Code: 0
Call: SELECT t0.host, t2.status, SUM(t2.job_load), t0.max_load FROM oc_service_registration t3, oc_job t2, oc_service_registration t1, oc_host_registration t0 WHERE ((((((t1.online = ?) AND (t1.active = ?)) AND (t0.maintenance = ?)) AND (t2.status IN )) AND (t3.service_type <> ?)) AND (((t1.id = t2.processor_service) AND (t0.id = t1.host_registration)) AND (t3.id = t2.creator_service))) GROUP BY t0.host, t2.status
bind => [5 parameters bound]
Query: ReportQuery(name="ServiceRegistration.hostloads" referenceClass=JpaJob sql="SELECT t0.host, t2.status, SUM(t2.job_load), t0.max_load FROM oc_service_registration t3, oc_job t2, oc_service_registration t1, oc_host_registration t0 WHERE ((((((t1.online = ?) AND (t1.active = ?)) AND (t0.maintenance = ?)) AND (t2.status IN ?)) AND (t3.service_type <> ?)) AND (((t1.id = t2.processor_service) AND (t0.id = t1.host_registration)) AND (t3.id = t2.creator_service))) GROUP BY t0.host, t2.status")
Default FetchGroup(){creatorServiceRegistration, creator, jobLoad, dispatchable, version, queueTime, dateCreated, dateStarted, parentJob, dateCompleted, organization, rootJob, arguments, id, runTime, operation, childJobs, processorServiceRegistration, status}
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:342) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:691) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2096) [321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:603) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:263) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:332) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:728) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2778) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2715) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.queries.ReportQuery.executeDatabaseQuery(ReportQuery.java:854) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1183) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:466) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1271) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2981) ~[321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1895) [321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1877) [321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1842) [321:org.eclipse.persistence.core:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262) [322:org.eclipse.persistence.jpa:2.7.3.v20180807-4be1041]
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482) [322:org.eclipse.persistence.jpa:2.7.3.v20180807-4be1041]
at org.opencastproject.serviceregistry.impl.ServiceRegistryJpaImpl.getHostLoads(ServiceRegistryJpaImpl.java:2233) [150:opencast-serviceregistry:7.1.0]
at org.opencastproject.serviceregistry.impl.ServiceRegistryJpaImpl$JobDispatcher.dispatchDispatchableJobs(ServiceRegistryJpaImpl.java:2985) [150:opencast-serviceregistry:7.1.0]
at org.opencastproject.serviceregistry.impl.ServiceRegistryJpaImpl$JobDispatcher.run(ServiceRegistryJpaImpl.java:2940) [150:opencast-serviceregistry:7.1.0]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:?]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [?:?]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:?]
at java.lang.Thread.run(Thread.java:748) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: column "t0.max_load" must appear in the GROUP BY clause or be used in an aggregate function

Expected Results:

No error message. =)

Workaround (if any):

I managed to find the query, edit 'modules/common-jpa-impl/src/main/java/org/opencastproject/serviceregistry/impl/jpa/ServiceRegistrationJpaImpl.java' and re-compile opencast. That seems to have fixed the problem. The error itself seems to mention how to correct the problem, so I just tried it out.

Fixed copy of 'ServiceRegistrationJpaImpl.java' file attached. I grabbed it from the release 7.2 tag tarball. I only changed 1 line:

[opencast-beta opencast-7.2]# diff modules/common-jpa-impl/src/main/java/org/opencastproject/serviceregistry/impl/jpa/ServiceRegistrationJpaImpl.java.orig modules/common-jpa-impl/src/main/java/org/opencastproject/serviceregistry/impl/jpa/ServiceRegistrationJpaImpl.java
69c69
< + "GROUP BY job.processorServiceRegistration.hostRegistration.baseUrl, job.status"),

> + "GROUP BY job.processorServiceRegistration.hostRegistration.baseUrl, job.status, job.processorServiceRegistration.hostRegistration.maxLoad"),

I'll continue to test it and let you know if I find anything that isn't working.

Status

Assignee

Unassigned

Reporter

Mikey Orr

Severity

Cosmetic Issue

Tags (folksonomy)

None

Components

Affects versions

Priority

Minor
Configure