Upgrade 3.2 to 3.3-LTS fails due "Null value not allowed" for review_assigments.review_round_id about pkp-lib HOT 8 OPEN

@jonasraoni @asmecher @marcbria It's possible there are old review rounds in your database that belong to submissions that no longer exist. We occasionally run into a bug where the review_assignments table has a null review_round_id and the way we fix this is with an SQL query like:

UPDATE review_assignments, review_rounds SET review_assignments.review_round_id = review_rounds.review_round_id WHERE review_assignments.submission_id = review_rounds.submission_id AND review_assignments.round = review_rounds.round;

Or something like that (test it first!). This won't fix stale database records that reference submissions that don't exist any more so perhaps some data clean-up is advised here as upgrading to 3.4 may be problematic later on if they are left hanging around.

from pkp-lib.

If the problem exists, I think it must be fixed, the upgrade path for old users is already not simple with the "pit-stop" step.

from pkp-lib.

@marcbria I'm pretty cautious and would normally only run this after a database backup, please. If you're doing that you should be alright.

from pkp-lib.

@jonasraoni or @mfelczak, do you know if hosting has run into this issue?

from pkp-lib.

I just helped to solve a couple of issues, so I haven't seen this one. The most experienced OJS 2 > 3 upgrader on the house is @jnugent 😁

from pkp-lib.

Thanks for your help guys!

@jnugent it's safe to run this in any journal?
I can add this to the bottom of each mysql dump... just in case the journal fails for this reason.

If it's safe, make sense to add this in the upgradeScript?
Could be reported in log as "Removing orphan reviews for article XXX that don't exist any more" or something like that.

Healing old-damaged DBs during upgrades is a present for our future selves. :-).

from pkp-lib.

Of course. I work on exact replicas of my journals and never touch anything in production until I'm absolutely sure it works in testing. :-)

Right now I'm doing the upgrade of 50 journals all our rubbish is coming to light. :-)

The point is, in other journals, I have found a couple of "DB integrity" issues more but related with authors and email_logs tables. Should I post them in the forum, in this thread or create an issue for each of them?

If it's safe, make sense to add this in the upgradeScript?
It could be reported in log as "Removing orphan reviews for article XXX that don't exist any more" or something like that.

I meant if you think this is something to include in the OJS upgrade script in case others run into this error in the future.

Probably the answer should be "no" as far looks like I'm the only one reporting this?

from pkp-lib.