Bug 8154

Summary: DB Error on Editor Home Page
Product: OJS Reporter: Robert Alessi <alessi>
Component: Open Journal SystemsAssignee: PKP Support <pkp-support>
Status: RESOLVED DUPLICATE    
Severity: major CC: alec, alessi, dnamiot, molavy2003
Priority: P3    
Version: 2.4.2   
Hardware: All   
OS: All   
Version Reported In: Also Affects:
Attachments: database debug output

Description Robert Alessi 2013-03-11 12:16:14 PDT
I think I found something, if not a bug in the EditorSubmissionDAO.inc.php file.

The "$searchSql = ' AND ea.user_id = ?';" statement (line 325) gives an error page which reads: "DB Error: Unknown column 'ea.user_id' in 'where clause'"

To get this, go to the editor home (/<journal_name>/editor), and click on whatever link below "Submissions".

To get rid of this error, I replaced the string "ea.user_id" with "a.user_id"

Many, many thanks to the authors of OJS!

Robert
Comment 1 Alec Smecher 2013-03-11 12:37:11 PDT
Robert, are you sure there are no modifications to your OJS 2.4.2 installation? Looking at line 325...

$searchSql .= ' AND ea.user_id = ?';

...this references the table joined a few lines above...

LEFT JOIN edit_assignments ea ON (a.article_id = ea.article_id)

...so I'm not sure why that column would be unrecognized.
Comment 2 Robert Alessi 2013-03-11 12:38:30 PDT
I left a typo in this report: the string I replaced "ea.user_id" with is "ed.user_id", and not "a.user_id".

Sorry about that.

Robert
Comment 3 Alec Smecher 2013-03-11 12:42:24 PDT
Robert, are you sure you're using OJS 2.4.2 and not e.g. 2.4.1?
Comment 4 Robert Alessi 2013-03-11 12:52:28 PDT
@Alec: I just downloaded 2.4.2, and installed it with no modifications. This was an upgrade from 2.3.6.

I also looked into this file, and I suspect that the corrected ed.user_id may reference to the table joined line 282:

--> LEFT JOIN users ed ON (e.editor_id = ed.editor_id)

I'm not sure, but I will test this further.
Comment 5 Alec Smecher 2013-03-11 12:55:25 PDT
Robert, also take a look at bug #8116; this looks identical but that but was solved against OJS 2.4.1 (which is why I ask about versions).
Comment 6 Robert Alessi 2013-03-11 13:06:59 PDT
Ok, I see the point.

But since ea.user_id gives me a "DB Error: Unknown column 'ea.user_id' in 'where clause'" page, the error may be a few lines above in the WHERE clause.

I'm just asking. I haven't checked the columns in the database yet.
Comment 7 Robert Alessi 2013-03-11 15:39:29 PDT
Got it.

The submitted patch from #8116 is wrong.

Instead of:

- 339     $searchSql .= ' AND ed.user_id = ?';
+ 339     $searchSql .= ' AND ea.user_id = ?';

you should have:

- 339     $searchSql .= ' AND ed.user_id = ?';
+ 339     $searchSql .= ' AND ea.editor_id = ?';


i.e. "ea.editor_id" instead of "ea.user_id"
Comment 8 Alec Smecher 2013-03-11 16:45:29 PDT
Robert, this still isn't making sense to me. The query around line 289 joins the edit_assignments table as "ea":

SELECT ... LEFT JOIN edit_assignments ea ON (a.article_id = ea.article_id) ... WHERE ... AND ea.user_id = ?

The join is always included (i.e. it's not conditional) so I'm not sure where the "column not found" error is coming from. Could you use database logging (in your MySQL configuration) or the "debug" setting (in your OJS configuration) to dump the full query that results in the error message?
Comment 9 Robert Alessi 2013-03-12 01:04:37 PDT
Created attachment 3919 [details]
database debug output
Comment 10 Robert Alessi 2013-03-12 01:06:28 PDT
(In reply to comment #8)
> Robert, this still isn't making sense to me. The query around line 289 joins
> the edit_assignments table as "ea":
> 
> SELECT ... LEFT JOIN edit_assignments ea ON (a.article_id = ea.article_id)
> ... WHERE ... AND ea.user_id = ?
> 
> The join is always included (i.e. it's not conditional) so I'm not sure
> where the "column not found" error is coming from. Could you use database
> logging (in your MySQL configuration) or the "debug" setting (in your OJS
> configuration) to dump the full query that results in the error message?

Sorry I missed this.

The error occurs three times when trying to get the "editor/submissions/submissionsUnassigned" page. I just attached the debug file.
Comment 11 Alec Smecher 2013-03-12 08:42:55 PDT
Robert, your comment #7 was spot on -- I was staring so hard at the table names that I missed the distinction in column names. Committing & adding to our recommended patches list for OJS 2.4.2. Thanks for contributing.
Comment 12 Alec Smecher 2013-03-12 08:45:03 PDT
Fixed column name
https://github.com/pkp/ojs/commit/35edda89d8f89586c1bbc0dbcc298c6af4eb75a1
Comment 13 Alec Smecher 2013-03-12 08:50:03 PDT
Fixed column name
https://github.com/pkp/ojs/commit/3d3925d0b58dde6068466601e40083dcce86fb82
Comment 14 Jason Nugent 2013-03-13 07:02:47 PDT
*** Bug 8156 has been marked as a duplicate of this bug. ***
Comment 15 Alec Smecher 2013-12-17 08:44:55 PST

*** This bug has been marked as a duplicate of bug 8517 ***
Comment 16 Hosein 2014-01-12 22:26:05 PST
after i applied patch 
there is no submissions in editor/submissions/submissionsUnassigned
and editor/submissions/submissionsInReview
pages
Comment 17 Hosein 2014-01-12 22:49:10 PST
sorry 
that happen because 
i set 
Assigned To: me
after i change it to All editors 
show submissions