Editorial Assignments not showing up on editor's task list

[davidley]

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]

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

[asmecher]

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

[kevin]

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.

[davidley]

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

[kevin]

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

[davidley]

Thanks Kevin,

How do I go about applying this patch?

Thanks,
Dave

[davidley]

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

[asmecher]

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