OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



Editorial Assignments not showing up on editor's task list

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

Editorial Assignments not showing up on editor's task list

Postby davidley » Tue Nov 08, 2005 8:03 am

I’ve been having a very strange problem I am hoping someone might be able to help me with.

On our submissions.php page, I have a list of articles that I want to assign to a specific editor. I assign an editor using the drop down box and hit the refresh table button.

However, when I go to that editor’s editorial assignment page (editor.php), there appears to be no tasks assigned to them. This was working find for a long time, but suddenly I can no longer get editorial assignments to show up in editor's task lists.

Our managing editor logged in one day to find that everything was removed from his editorial assignments. When I try to re-assign them, they won’t appear on his plate (but they do stay marked as assigned in the submissions queue).

Do you have any thoughts on what might be going wrong?

Thanks very much for your help.
davidley
 
Posts: 5
Joined: Tue Nov 08, 2005 7:33 am

Postby davidley » Thu Nov 10, 2005 12:58 pm

Here is some more details about my case:

Running OJS 1.1.9

On the editors.php page, I noticed that the "Active" column was blank, no matter how many articles are assigned to an editor.

The code for the Active number calls a function activeEditor in the /include/validatefunc.inc.php file.

That function makes the following SQL query:


SELECT COUNT( * )
FROM tblarticles, tblsections
LEFT JOIN tbleditors ON tblarticles.fkEditorID = nEditorID
LEFT JOIN tblusers ON tblusers.fkEditorID = tblarticles.fkEditorID
WHERE tblarticles.fkSectionID = nSectionID AND (
tblarticles.fkEditorID = '20' OR nSectionID
IN (
'0'
)
) AND bArchive = '0' AND bSchedule = '0' AND dtDateSubmitted IS NOT NULL


Which when I try to run manually gives me the following error:

MySQL said:
#1054 - Unknown column 'tblarticles.fkEditorID' in 'on clause'


If anyone has any thought I this I would be very grateful.

Thanks,
Dave
davidley
 
Posts: 5
Joined: Tue Nov 08, 2005 7:33 am

Postby asmecher » Thu Nov 10, 2005 6:16 pm

Hello Dave,

That sounds very strange -- could you check to see that your database schema matches the one that OJS creates in admin/include/createdb.php? The column that MySQL complains doesn't exist is created on line 84 in OJS 1.1.9.

You can check the schema by executing the following command in MySQL:
Code: Select all
DESCRIBE tblarticles;

The error message you're describing suggests that MySQL has dropped the fkEditorID column from the table for some reason.

Regards,
Alec Smecher
Open Journal Systems Team
asmecher
 
Posts: 9212
Joined: Wed Aug 10, 2005 12:56 pm

Postby kevin » Fri Nov 11, 2005 1:47 am

Have you upgraded MySQL recently? I know that query would fail in PostgreSQL and I suspect it would also fail the stricter standards checking in MySQL 5.0; not sure if it would be a problem in any of the 4.x releases.
kevin
 
Posts: 338
Joined: Tue Oct 14, 2003 8:23 pm

Postby davidley » Fri Nov 11, 2005 10:21 am

Hi Kevin,

Yes, my hosting server did recently upgrade MySQL to 5.0. When I described the problem I was having they said:

" Mysql has changed some of the its syntax in the 5.0 version. I haven't
really had time to check all of the changes out, however it is likely
that this is what is causing the problem. This might be a good starting
point to see if you can find what needs to be changed.
http://dev.mysql.com/doc/refman/5.0/en/join.html " (the link points to information about the JOIN syntax).

Any advice I what I should do now?

I can't revert to a previous version of MySQL -- it is running on a shared server and they needed to do the upgrade to fill a security hole.

Should I play around with the format of the SQL query in the activeEditor function until I get it to work? Do you think there are many other places in the code that will result in similar problems? (I haven't noticed any).

We aren't prepared to upgrade to OJS 2.x just yet.

Any suggestions would be greatly appreciated.

Dave


Alec,

Yes the fkEditorID column is still there.

Thanks,
Dave
davidley
 
Posts: 5
Joined: Tue Nov 08, 2005 7:33 am

Postby kevin » Fri Nov 11, 2005 12:27 pm

This (untested) patch would probably fix the instances of this particular class of query. I don't know if there are any other compatibility issues between OJS 1.x and MySQL 5, nor do I know if PKP is willing to do any audit or testing of OJS 1.x against MySQL 5 themselves.

Code: Select all
Index: search.php
===================================================================
RCS file: /cvs/ojs/search.php,v
retrieving revision 1.83
diff -u -r1.83 search.php
--- search.php  11 Nov 2005 18:56:46 -0000      1.83
+++ search.php  11 Nov 2005 19:19:08 -0000
@@ -273,9 +273,9 @@
                dtDateHTMLGalleyCreated, dtDatePDFGalleyCreated, fkFilePDFID,
                dtDatePostScriptGalleyCreated, fkFilePostScriptID,
                bContentsLinkPage, fkPublishStatusID
-               FROM tblarticles AS a, tblsections AS s
+               FROM tblarticles AS a INNER JOIN tblsections AS s ON fkSectionID=nSectionID
                LEFT JOIN tblissues AS i ON fkIssueID=nIssueID
-               WHERE fkSectionID=nSectionID AND a.bPublished='1' AND ($where)
+               WHERE a.bPublished='1' AND ($where)
                ORDER BY nYear DESC, nVolume DESC, nNumber DESC, s.nRank, nOrder";
        $result = $db->query($sql);
        $num_results = $db->num_rows($result);
@@ -660,10 +660,12 @@
                                dtDateHTMLGalleyCreated, dtDatePDFGalleyCreated, fkFilePDFID,
                                dtDatePostScriptGalleyCreated, fkFilePostScriptID,
                                bContentsLinkPage, fkPublishStatusID
-                               FROM tblauthorstoarticles AS t, tblarticles AS a, tblsections AS s
+                               FROM tblauthorstoarticles AS t
+                               INNER JOIN tblarticles AS a ON fkArticleID=nArticleID
+                               INNER JOIN tblsections AS s ON fkSectionID=nSectionID
                                LEFT JOIN tblissues AS i ON fkIssueID=nIssueID
-                               WHERE fkAuthorID='$author_row[fkAuthorID]' AND fkArticleID=nArticleID
-                               AND fkSectionID=nSectionID AND a.bPublished='1' AND bAuthorIndex='1'
+                               WHERE fkAuthorID='$author_row[fkAuthorID]'
+                               AND a.bPublished='1' AND bAuthorIndex='1'
                                ORDER BY nYear DESC, nVolume DESC, nNumber DESC, s.nRank, nOrder";
                        $article_result = $db->query($sql);
                        $num_articles = $db->num_rows($article_result);
@@ -769,10 +771,12 @@
                        dtDateHTMLGalleyCreated, dtDatePDFGalleyCreated, fkFilePDFID,
                        dtDatePostScriptGalleyCreated, fkFilePostScriptID,
                        bContentsLinkPage, fkPublishStatusID
-                       FROM tblauthorstoarticles AS t, tblarticles AS a, tblsections AS s
+                       FROM tblauthorstoarticles AS t
+                       INNER JOIN tblarticles AS a ON fkArticleID=nArticleID
+                       INNER JOIN tblsections AS s ON fkSectionID=nSectionID
                        LEFT JOIN tblissues AS i ON fkIssueID=nIssueID
-                       WHERE fkAuthorID='$author_row[fkAuthorID]' AND fkArticleID=nArticleID
-                       AND fkSectionID=nSectionID AND a.bPublished='1' AND bAuthorIndex='1'
+                       WHERE fkAuthorID='$author_row[fkAuthorID]'
+                       AND a.bPublished='1' AND bAuthorIndex='1'
                        ORDER BY nYear DESC, nVolume DESC, nNumber DESC, s.nRank, nOrder";
                $article_result = $db->query($sql);
                $num_articles = $db->num_rows($article_result);
Index: include/validatefunc.inc.php
===================================================================
RCS file: /cvs/ojs/include/validatefunc.inc.php,v
retrieving revision 1.23
diff -u -r1.23 validatefunc.inc.php
--- include/validatefunc.inc.php        29 Jul 2004 03:15:42 -0000      1.23
+++ include/validatefunc.inc.php        11 Nov 2005 19:19:09 -0000
@@ -329,25 +329,25 @@
 
 
        // Get list of submissions for this editor
-       $submission_query = "SELECT " . ($count ? "COUNT(*)" : "nArticleID, dtDateSubmitted, dtDateEdDec, nSectionID, chAbbrev AS chSection, nUserID AS edUserID, chInitials") . " FROM tblarticles, tblsections LEFT JOIN tbleditors ON tblarticles.fkEditorID = nEditorID LEFT JOIN tblusers ON tblusers.fkEditorID = tblarticles.fkEditorID WHERE tblarticles.fkSectionID = nSectionID";
+       $submission_query = "SELECT " . ($count ? "COUNT(*)" : "nArticleID, dtDateSubmitted, dtDateEdDec, nSectionID, chAbbrev AS chSection, nUserID AS edUserID, chInitials") . " FROM tblarticles INNER JOIN tblsections ON tblarticles.fkSectionID = nSectionID LEFT JOIN tbleditors ON tblarticles.fkEditorID = nEditorID LEFT JOIN tblusers ON tblusers.fkEditorID = tblarticles.fkEditorID WHERE";
 
        if($nEditorRole == 2) {
                // for managing editors
                if($nEdProcessType == 3 && !$show_all) {
-                       $submission_query .= " AND dtDateEdDec IS NOT NULL";
+                       $submission_query .= " dtDateEdDec IS NOT NULL AND";
                }
 
        } else if($nEditorRole == 3) {
                // for layout editors
-               $submission_query .= " AND dtDateRequestGalleys IS NOT NULL AND dtDateGalleysCompleted IS NULL";
+               $submission_query .= " dtDateRequestGalleys IS NOT NULL AND dtDateGalleysCompleted IS NULL AND";
        } else if($nEdProcessType == 1 || $nEditorRole == 0 || $nEditorRole == 1) {
                // for undesignated/section editors
-               $submission_query .= " AND (tblarticles.fkEditorID = '$editor_id' OR nSectionID IN ('" . join("', '", editorSections($editor_id)) . "'))" . ($nEdProcessType == 3 ? " AND dtDateEdDec IS NULL" : "");
+               $submission_query .= " (tblarticles.fkEditorID = '$editor_id' OR nSectionID IN ('" . join("', '", editorSections($editor_id)) . "'))" . ($nEdProcessType == 3 ? " AND dtDateEdDec IS NULL" : ""). " AND";
 
        }
 
        // exclude articles that have been queued for scheduling, archived, or have not been submitted
-       $submission_query .= " AND bArchive = '0' AND bSchedule = '0' AND dtDateSubmitted IS NOT NULL" . (isset($sectionid) ? " AND nSectionID = '" . ((int) $sectionid) . "'" : "");
+       $submission_query .= " bArchive = '0' AND bSchedule = '0' AND dtDateSubmitted IS NOT NULL" . (isset($sectionid) ? " AND nSectionID = '" . ((int) $sectionid) . "'" : "");
 
        return $submission_query;
 }


(As an aside, I expect OJS 2.x is better in this regard; our PostgreSQL testing has likely caught most or all of the issues that would have tripped up MySQL 5.)
kevin
 
Posts: 338
Joined: Tue Oct 14, 2003 8:23 pm

Postby davidley » Fri Nov 11, 2005 12:35 pm

Thanks Kevin,

How do I go about applying this patch?

Thanks,
Dave
davidley
 
Posts: 5
Joined: Tue Nov 08, 2005 7:33 am

Postby davidley » Fri Nov 25, 2005 10:36 am

I just noticed that our search engine has stopped working.

I don't know why, but it stopped working around the time of the MySQL upgrade, so I suspect it had something to do with it as well.

Kevin, how do I go about applying the patch you gave above?

Thanks much,
Dave
davidley
 
Posts: 5
Joined: Tue Nov 08, 2005 7:33 am

Postby asmecher » Fri Nov 25, 2005 1:38 pm

Hello Dave,

Apply the patch using something like the following steps:
  • Save the patch into a file in your OJS root directory (e.g. mysqlUpgrade.patch)
  • Change into your OJS root directory
  • Execute a test run:
    Code: Select all
    patch --dry-run -p3 < mysqlUpgrade.patch
  • Check to see if any conflicts/problems arise and correct them as necessary
  • Patch your OJS installation:
    Code: Select all
    patch -p3 < mysqlUpgrade.patch

Regards,
Alec Smecher
Open Journal Systems Team
asmecher
 
Posts: 9212
Joined: Wed Aug 10, 2005 12:56 pm


Return to OJS Technical Support

Who is online

Users browsing this forum: No registered users and 3 guests