Good morning everyone.
We would be grateful if you could give us a helping hand in upgrading OJS system from version 3.1.2.0 to 3.2.1.2 - (we are experiencing problems with upgrading database; previous steps were not a problem).
The process is done via CLI using PHP 7.2 preprocessor. We are working on a copy of website to not destroy anything in case upgrade fails. Unfortunatelly upgrade failed at many points.
Therefore we enabled logger to check what is causing problems.
Here are the steps we took and problems we encountered:
First error that terminated the process was:
PKP-Database-Logger 1611305261,4953: Query:
INSERT INTO email_templates_settings (email_id, locale, setting_value, setting_name)
SELECT email_id, locale, subject, ‘subject’
FROM email_templates_data
failed. Duplicate entry ‘1-cs_CZ-subject’ for key ‘email_settings_pkey’
Based on that we checked the contents of “email_templates_settings” table and found out there is no crucial data inside (and only four pages of records), so we temporarily truncated this table (before that we recovered database to state before upgrade).
This step let us proceed with upgrade process, but next thing that showed up was:
PKP-Database-Logger 1611306130,8765: Query:
UPDATE submissions as s
SET current_publication_id = (
SELECT p.publication_id
FROM publications as p
WHERE s.submission_id = p.submission_id
)
failed. Subquery returns more than 1 row
There is a strange behaviour happening during this step, we compared “publications” table from before the upgrade with the current one and found out records are duplicated. The table looks like this:
Basically each record from id 16xxx onwards is a duplicate of already existing record and that causes the query to break (subquery returns two records, not one). We fixed this by adding fragment of code:
ORDER BY publication_id DESC LIMIT 1
This helped and next time we did run upgrade, next error showed up:
PKP-Database-Logger 1611305541,9159: Query:
INSERT INTO publication_galleys(galley_id, locale, publication_id, label, file_id, seq, remote_url, is_approved)
SELECT tsg.galley_id, tsg.locale, s.current_publication_id, tsg.label, tsg.file_id, tsg.seq, tsg.remote_url, 1
FROM submissions as s, temp_submission_galleys as tsg
WHERE s.submission_id = tsg.submission_id
failed. Duplicate entry ‘1’ for key ‘PRIMARY’
At this point we are hopeless, one solved problem generates another one. We do not have experience regaring how OJS system works from backend perspective, and we need to ask for help with this task.
Maybe someone here knows if there is an easy way to solve this problem (or another method of upgrading that could work).
Thank you in advance.