OJS 3.1.1-2: DB error in Archive view

Software Support
,
1

Hi,
I recently updated an OJS3 installation with all commits in the branch ojs-stable-3_1_1 between May 23rd and today, for both OJS and pkp-lib. Running the upgrade script only produced a few warnings (as far as I can tell insignificant ones). After the upgrade I get the following error when I open the Archives section of a journal:

DB Error: ERROR: column "o.seq" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...published" = $2 group by "i"."issue_id" order by "o"."seq" ... ^
Stack Trace:
File: /...../classes/services/IssueService.inc.php line 56
Function: DAO->retrieveRange("select "i".* from "issues" as "i" left join "issue_settings" as ...", Array(2), Object(DBResultRange))

File: /...../pages/issue/IssueHandler.inc.php line 137
Function: OJS\Services\IssueService->getIssues("14", Array(5))

...

By looking at the code diffs I managed to “solve” the issue by commenting out lines 71 and 72 in classes/services/queryBuilders/IssueListQueryBuilder.inc.php, i.e.

...
    //} elseif ($column === 'seq') {
    //    $this->orderColumn = 'o.seq';
...

Our installation uses Postgres. Our code is very close to upstream (GitHub - ubbdst/ojs at ojs-3_1_1-2, GitHub - ubbdst/pkp-lib at ojs-stable-3_1_1), there are mainly a few plugin submodules, some locale stuff, and for pkp-lib the adodb postgres patch. To me it looks like this could be either a postgres-incompatibility, or something that was missed in an upgrade script?

Simon

2

I also just had a problem with upgrading to the latest release 3.1.1-2 from 3.1.0.1 on a postgres based install. The update ran through (after adding the adobd patches) but all submissions were gone (was not able to solve the issue after two tries, so stayed at 3.1.0.1). Maybe this is related? I did not see a db error in the error.log though.

3

On a test server we went from 3.1.1-0 to 3.1.1-4 in one jump, on a postgres d/b system. We have the same error. I can confirm that @simonmitternacht’s solution worked for us. Archive view doesn’t throw an error after commenting out the one “elseif”. Looking forward to seeing a less hacky solution.

Richard
https://scholarworks.iu.edu/journals/index.php

4

This is happening to us too … on a fresh 3.1.2.0 postgresql based installation. As noted previously, @simonmitternacht 's solution works to get rid of the error message.

1 Like
5

There’s a recent revision of this file by PKP that eliminates the need to comment out lines 71 and 72.: [OJS] Fix for #3705 breaks archives with PostgreSQL · Issue #4097 · pkp/pkp-lib · GitHub.

 classes/services/queryBuilders/IssueListQueryBuilder.inc.php
 
 public function orderBy($column, $direction = 'DESC') {
	if ($column === 'lastModified') {
		$this->orderColumn = 'i.last_modified';
--		   // } elseif ($column === 'seq') {
--		   //	$this->orderColumn = 'o.seq';
++		    } elseif ($column === 'seq') {
++		   	$this->orderColumn = 'o.seq';
	} else {
		$this->orderColumn = 'i.date_published';
	}
	$this->orderDirection = $direction;
	return $this;
}
  ...

public function get() {
	$this->columns[] = 'i.*';
	$q = Capsule::table('issues as i')
				->where('i.journal_id','=', $this->contextId)
				->leftJoin('issue_settings as is', 'i.issue_id', '=', 'is.issue_id')
				->leftJoin('custom_issue_orders as o', 'o.issue_id', '=', 'i.issue_id')
				->orderBy($this->orderColumn, $this->orderDirection)
 --			->groupBy('i.issue_id');     
 ++                ->groupBy('i.issue_id', $this->orderColumn);

There are several changes to this file since we last deployed an update. I didn’t replace the full file. The fix at 4097 works for a postgres environment if you simply add “$this->orderColumn” to “->groupBy(‘i.issue_id’ . . .” This small change allowed me to uncomment the lines on 71 and 72.

2 Likes