OJS upgrade from 3.1.2 to 3.2.1 fails when attempting to set the current_publication_id

Software Support
1

Description of issue or problem I’m having:
In a recent attempt to upgrade one of our journals from OJS 3.1.2 to 3.2.1-4, the upgrade fails with the following output.

ERROR: Upgrade failed: DB: Subquery returns more than 1 row

After enabling debugging and checking the upgrade, the particular query where the upgrade fails is:

UPDATE submissions as s
SET current_publication_id = (
    SELECT p.publication_id
    FROM publications as p
    WHERE s.submission_id = p.submission_id
    )

The publications tables does appear to have been created, and it is populated. When I performed a query to see if I could identify submissions in the publications table, there are many instances of submissions that are associated with multiple publications. The query I used to locate these records is:

SELECT p.submission_id,count(p.publication_id)
FROM publications p
JOIN submissions s
ON s.submission_id = p.submission_id
GROUP BY p.submission_id
HAVING count(p.publication_id) > 1;

At least some of these duplicates are a result of there being multiple versions of a submission.

For example, here is some of the output from a submission with numerous entries in the publications table.

*************************** 1. row ***************************
    publication_id: 841
     access_status: 0
    date_published: 2021-01-07
     last_modified: 2021-01-07 11:35:34
            locale: en_US
primary_contact_id: NULL
        section_id: 28
               seq: 2
     submission_id: 1086
            status: 3
          url_path: NULL
           version: 1
*************************** 2. row ***************************
    publication_id: 1024
     access_status: 0
    date_published: 2021-05-24
     last_modified: 2021-05-24 15:27:23
            locale: en_US
primary_contact_id: NULL
        section_id: 28
               seq: 2
     submission_id: 1086
            status: 1
          url_path: NULL
           version: 2

So, can anyone help me identify what might have caused this problem in the first place? Is this a result of multiple versions of a submission? Is there a way forward to fix this issue?

Steps I took leading up to the issue:

  1. Replace code base from 3.1.2 to 3.2.1-4
  2. Run php upgrade.php check
  3. Run php upgrade.php upgrade

What I tried to resolve the issue:

We’re in the unfortunate position where I recently discovered this journal had not been successfully upgraded from 3.1.2 to 3.2.1-4 after an initial unsuccessful attempt from last summer. The backups are no longer available, so it’s not an option for me to revert to a backup and try the upgrade again.

I’m afraid the extent of my attempts to resolve the issue have been limited to exploring the upgrade queries to see where the issue initially occurred and trying to search for a possible manual fix.

Application Version - e.g., OJS 3.1.2:

Application Version: OJS 3.2.1
Database Version: OJS 3.1.2
MySQL Version: 5.6

Additional information, such as screenshots and error log messages if applicable:

If I can provide any further information or clarification, just let me know. Many thanks in advance to any and all help.