Database upgrade problem for 2.4.8-2 to 3.1.0-1

Software Support
21

@bozana The table articles in 2.4.8 does not have a submission_id field, but it has an article_id field which I’m assuming has the same meaning.

In fact all those articles have an apparently meaningful review_file_id field:

SELECT article_id, review_file_id FROM articles WHERE article_id IN (1093,1497,1498,1499,1505,1506,1508,1509,1513,1514,1713,5739);

 article_id | review_file_id 
------------+----------------
       1093 |           2354
       1497 |           3390
       1498 |           3394
       1499 |           3397
       1505 |           3407
       1506 |           3410
       1508 |           3414
       1509 |           3419
       1513 |           3434
       1514 |           3438
       1713 |           4101
       5739 |          19622

Cheers, Brendan.

22

Hi @bdm

Hmmm… And do those file_ids exist in the DB table article_files?
I am just wondering why the following SQL returned only one result:
SELECT DISTINCT ra.submission_id, ra.round, af.revision FROM review_assignments ra, article_files af, articles a WHERE (ra.submission_id, ra.round) NOT IN (SELECT rr.submission_id, rr.round FROM review_rounds rr) AND a.article_id = ra.submission_id AND af.article_id = a.article_id AND af.file_id = a.review_file_id AND af.file_stage = 2 AND af.round = ra.round
Hmmm…

Thanks!
Bozana

23

Hi @bozana

In 2.4.8 db:

SELECT file_id,article_id,file_stage,round FROM article_files WHERE file_id in (2354,3390,3394,3397,3407,3410,3414,3419,3434,3438,4101,19622) ORDER BY article_id;

 file_id | article_id | file_stage | round
---------+------------+------------+-------
    3407 |       1505 |          1 |     1
    3410 |       1506 |          1 |     1
    3414 |       1508 |          1 |     1
    3419 |       1509 |          1 |     1
   19622 |       5739 |          2 |     2
   19622 |       5739 |          2 |     3
   19622 |       5739 |          2 |     1

Not all of them are present. Also you can see that the reason why only submission 5739 was selected by your SQL is that only that one has file_stage=2. If I run it without the file_stage=2 test, it gives the same as above (i.e. files 3407, 3410, 3414, 3419, 19622).

Thanks, Brendan.

24

Hi @bdm

Ah, I see…

What about the other review_file_ids, e.g. 2354, 3390…? Do they also exist?

If they all exist (also in the files folder), I think you could then try to insert those missing review_rounds (no matter what file stage those files have), i.e. something like this:
INSERT INTO review_rounds (submission_id, round, review_revision) SELECT DISTINCT ra.submission_id, ra.round, af.revision FROM review_assignments ra, article_files af, articles a WHERE (ra.submission_id, ra.round) NOT IN (SELECT rr.submission_id, rr.round FROM review_rounds rr) AND a.article_id = ra.submission_id AND af.article_id = a.article_id AND af.file_id = a.review_file_id AND af.round = ra.round

Then all review_round_id in the table review_assignments could/should be 0.

Then you could try the upgrade to 3.1.1 again.

But, please do all possible full backups, after each step/change…

THANKS!
Bozana

25

Hi @bozana

The 2.4.8->3.1.1 upgrade is done on a test server, without changing the running 2.4.8 installation at all, so at least it is fairly robust against disasters.

A clue perhaps. Files 3390, 3394, 3397, 3434, 3438 and 4101 are in the uploads directories in the submission/original folders. For example: 1713-4101-1-SM.pdf. However, files 3407, 3410, 3414, 3419, 19622 are in the submission/review folders. For example 5739-19622-1-RV.pdf.

File 2354 is missing entirely. It belongs to a paper published years ago and it won’t be a serious problem if the review information is lost.

Cheers, Brendan.