Upgrading OJS 2.4.8 to 3.0.1 Files not visible under Submission/Workflow view

Software Support
,
1

Hi,
I am upgrading a journal from OJS 2.3.7 to 3.0.1. I do this in two stages, first from 2.3.7 to 2.4.8 and then to 3.0.1. Both steps finish without errors or warnings, but I have a problem that occurs in the second upgrade: If I click on any submission, under 3.0.1 I get “no items” everywhere under “Submission files”, “Review files”, etc. (in 2.4.8 everything is still ok, as far as I can tell). Something seems to have gone wrong with the remapping of file stages in the upgrade. Below is an example from the database

After upgrading to 2.4.8

> select * from article_files where article_id=3439;
file_id | revision | source_file_id | source_revision | article_id |     file_name      | file_type | file_size | original_file_name | file_stage | viewable |    date_uploaded  |    date_modified    | round | assoc_id 
--------+----------+----------------+-----------------+------------+--------------------+-----------+-----------+--------------------+------------+----------+---------------------+---------------------+-------+----------
   8427 |        2 |                |                 |       3439 | 3439-8427-2-SM.rtf | text/rtf  |     40408 | uk01.rtf           |          1 |          | 2016-12-25 17:41:38 | 2016-12-25 17:41:38 |     1 |         
   8428 |        1 |           8427 |                 |       3439 | 3439-8428-1-RV.rtf | text/rtf  |     40408 | 3439-8427-2-SM.rtf |          2 |          | 2016-12-25 17:42:46 | 2016-12-25 17:42:46 |     1 |         
(2 rows)

The corresponding files have the locations journals/1/articles/3439/submission/original/3439-8427-2-SM.rtf journals/1/articles/3439/submission/review/3439-8428-1-RV.rtf, i.e. as far as I can tell everything is correct.

After upgrading to 3.0.1

> select * from submission_files where submission_id=3439;
 file_id | revision | source_file_id | source_revision | submission_id | file_type | file_size | original_file_name | file_stage | viewable |    date_uploaded    |    date_modified    | assoc_id | genre_id | direct_sales_price | sales_type | user_group_id | uploader_user_id | assoc_type 
---------+----------+----------------+-----------------+---------------+-----------+-----------+--------------------+------------+----------+---------------------+---------------------+----------+----------+--------------------+------------+---------------+------------------+------------
    8427 |        2 |                |                 |          3439 | text/rtf  |     40408 | uk01.rtf           |          7 |        0 | 2016-12-25 17:41:38 | 2016-12-25 17:41:38 |      703 |        1 |                    |            |             2 |                1 |        521
    8428 |        1 |           8427 |                 |          3439 | text/rtf  |     40408 | 3439-8427-2-SM.rtf |          7 |        0 | 2016-12-25 17:42:46 | 2016-12-25 17:42:46 |      703 |        1 |                    |            |             2 |                1 |        521
(2 rows)

And the corresponding files are located at journals/1/articles/3439/submission/fairCopy/3439-1-8428-1-7-20161225.rtf and journals/1/articles/3439/submission/fairCopy/3439-1-8427-2-7-20161225.rtf

SUBMISSION_FILE_FAIR_COPY is defined as 7, so the database entries seem to match the files, and the files have been moved properly (i.e. no permissions issues), but they should in this case be at stage SUBMISSION_FILE_ORIGINAL, i.e. 2, right?

Everything works when I submit new files.

We use a postgres database, in case it matters.

best regards,
Simon Mitternacht

2

Hi @simonmitternacht

Ah, I believe it has something to do with our SQL statements for PostgreSQL – I remember for another SQL statement it happened to me the same, that all entries were changed… I have to take a closer look…

Thanks!
Bozana

3

I believe the problem was that there should not be table aliases in the WHERE part of an UPDATE SQL… for example here: UPDATE submission_files SET file_stage=7 FROM submission_files sf, articles_migration am WHERE am.editor_file_id=sf.file_id (s. https://github.com/pkp/ojs/blob/master/dbscripts/xml/upgrade/3.0.0_update.xml#L204). – In the example I had problems with, I had to replace those aliases with the whole table names (s. https://github.com/pkp/ojs/blob/master/classes/install/Upgrade.inc.php#L1633-L1635).
Would it be possible for you to give me your 2.4.8 DB dump without sensitive data (you could send it to bozana dot bokan at posteo dot net), so that I could test the upgrade locally? If not, no problem, I’ll try to test it somehow differently… Also, I could change the SQLs and send you a patch, if you could try…

Best,
Bozana

4

Hi, I will try to anonymize the database dump then. Get back to you in a bit.

5

Hi @simonmitternacht

Great! I will then wait for the dump. If you would like to test it too, here is my patch, based on the current ojs-stable-3_0_1 branch: fix PostgreSQL UPDATE statements · bozana/ojs@1a10fa1 · GitHub

Thanks!
Bozana

6

Hi @simonmitternacht

Sorry, I was not quite right – the updated table must not be in the FORM part of the UPDATE statement and thus it can not be with alias in the WHERE part of the statement.
I changed the statements again, tested them. It should work now.
The corresponding issue is: PostgreSQL Update Statements · Issue #2208 · pkp/pkp-lib · GitHub
and the actual patch: pkp/pkp-lib#2208 fix PostgreSQL UPDATE statements by bozana · Pull Request #1197 · pkp/ojs · GitHub
Could you then please test it once again and confirm?

Thanks a lot!
Bozana

7

This version solved everything. Thank you!

Simon

8

Hi again @bozana,
Seems there were more problems after all, files submitted by reviewers are still missing. I fiddled a bit with the database and found that there were three fields I had to change In the table submission_files in order to make the files visible:

  • file_stage was 4, I had to change it to 5.
  • assoc_id was empty, I had to set it to the relevant review_id
  • assoc_type was empty, I set it to 517 (because when I uploaded a test file it got that type).

After these three changes the review files showed up as expected in the read review modal. The file had to be moved to submission/review/attachment/ and the file stage changed from 4 to 5 in the file name too.

I assume these errors are related to the other upgrade problems above, since they are quite similar. Problem is now the site is in production, so we can’t just redo the upgrade with fixed scripts. I still have database dumps from the old installation, so I guess one can extract the relevant file ids from there and only apply the changes to those files.

cheers,
Simon

9

Hi,

I noticed this as well when upgrading to 3.0.1, see issue 2 here: [OJS] Upgrading to OJS3: multiple issues with ongoing reviews · Issue #2173 · pkp/pkp-lib · GitHub

I think this was fixed for OJS 3.0.2 but that does not of course help you if you are in production already.

Did you manage to rename and move the files to the attachment folders? I did a script that did this for our installation which I ran after the upgrade.

10

Hi,
I would love to have a look at your script! I didn’t want to do anything in batch before I was sure I wasn’t ruining something else.

thanks!
Simon

11

Hi @simonmitternacht and @ajnyga

The patch is still not merged, waiting for the last code review, so stay tuned…

Best,
Bozana

12

Hi @bozana,
Great, than I will wait till further notice. We only have a couple of manuscripts that are active right now, so we can handle those manually while we’re waiting.

Simon

13

@simonmitternacht, the PR is merged and in ojs-stable-3_0_2 branch. Please do the backup!, then the DB update and check if the problem is solved…
Thanks!
Bozana

14

@bozana, great! This seems to work. Thank you!

I had an issue where the file was missing for one of the reviews, where line 1839, $revision->getFilePath(), crashed. I changed the code to output the reviewer_file_id for each row before that line to find the where the error came from, and set that particular reviewer_file_id to NULL. After that everything worked. I don’t know why this file was missing, but it would be helpful if the upgrade script reported which object causes the crash, not only on what line in the code.

15

Thanks a lot @simonmitternacht, I merged a patch that considers that…
Best,
Bozana