OJS 3.1.2.0 -> 3.2.1.2 upgrade problem

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.

Hi @marek,

If the upgrade fails for some reason, the database remains in a between-upgrade state. Every new upgrade attempt should be done on the intact database, in the state before the upgrade. I suspect the errors that are posted here are due to an attempt to upgrade on an already altered database. Can you double-check what is the initial error message?

Yes, this is crucially important to make a backup before the upgrade. Test upgrades are highly recommended. As per readme, you’ll need to recover the data from a backup on your test instance and run upgrade once again from a command line (helps to identify the problem).

Thank you for help, according to cite:

this could be the case. There were updates done previously by other person but it was many months ago and site works normally by now. Generally speaking for each action I described above the database was restored back to initial state, so it must mean that our initial state is already damaged in some way. Also if initial database was correct, we wouldn’t get any error at first place.

If that’s the case, is there any way to fix database before attempting update so we don’t lose any data that are already stored in there?

Other than trying to fix the database stuck between upgrades, is using Native XML Plugin and Users XML Plugin to export the data and to import it to a newly created 3.1.2.0 OJS instance and then upgrading it. That is if those plugins would be able to export the data, taking into account problems with the database.

The error occurs during migration of galleys, looks like there are galley entries with the same id (id 1 in this case) and they should be unique.

We tried running clean 3.1.2 installation with clean database (database contents were generated by installator) and later updating to also clean 3.3.0 package, but even this isn’t working. Without importing or modifying anything (except for configuration file of course).

Log looks like this:

/usr/local/php74/bin/php tools/upgrade.php upgrade

[pre-install]

[load: upgrade.xml]

[version: 3.3.0.2]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::migrateSubmissionCoverImages]

[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]

[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning_articleGalleySettings.xml (skipped)]

[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning.xml]

[data: dbscripts/xml/upgrade/3.2.0_preupdate_last_activity.xml]

[schema: lib/pkp/xml/schema/common.xml]

[schema: lib/pkp/xml/schema/log.xml]

[schema: lib/pkp/xml/schema/announcements.xml]

[schema: lib/pkp/xml/schema/scheduledTasks.xml]

[schema: lib/pkp/xml/schema/temporaryFiles.xml]

[schema: lib/pkp/xml/schema/metadata.xml]

[schema: lib/pkp/xml/schema/reviews.xml]

[schema: lib/pkp/xml/schema/reviewForms.xml]

[schema: lib/pkp/xml/schema/controlledVocab.xml]

[schema: lib/pkp/xml/schema/submissions.xml]

[schema: lib/pkp/xml/schema/submissionFiles.xml]

[schema: lib/pkp/xml/schema/categories.xml]

[schema: lib/pkp/xml/schema/notes.xml]

[schema: lib/pkp/xml/schema/genres.xml]

[schema: lib/pkp/xml/schema/tombstone.xml]

[schema: lib/pkp/xml/schema/rolesAndUserGroups.xml]

[schema: lib/pkp/xml/schema/metrics.xml]

[schema: lib/pkp/xml/schema/views.xml]

[schema: lib/pkp/xml/schema/libraryFiles.xml]

[schema: lib/pkp/xml/schema/navigationMenus.xml]

[schema: dbscripts/xml/ojs_schema.xml]

[code: Installer Installer::installDefaultNavigationMenus]

WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.

WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.

WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::migrateUserAndAuthorNames (skipped)]

[data: dbscripts/xml/upgrade/3.2.0_stylesheet.xml]

[data: dbscripts/xml/upgrade/3.2.0_archiving_settings.xml]

[data: dbscripts/xml/upgrade/3.2.0_update.xml]

[data: dbscripts/xml/upgrade/3.2.0_navigation_menu_items_locale_change.xml]

[code: Installer Installer::migrateSiteLocales]

[code: Installer Installer::migrateSidebarBlocks]

[code: Installer Installer::migrateSiteStylesheet]

[code: Installer Installer::migrateMetadataSettings]

[code: Installer Installer::createLicenseTerms]

[code: Installer Installer::installEmailTemplate]

[code: Installer Installer::changeUserRolesAndStageAssignmentsForStagePermitSubmissionEdit]

[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]

[data: dbscripts/xml/upgrade/3.2.0_versioning_submissionCategories.xml]

[data: dbscripts/xml/upgrade/3.2.0_last_activity.xml]

[data: dbscripts/xml/upgrade/3.2.0_url_path.xml]

[code: Installer Installer::setStatsEmailSettings]

[code: Installer Installer::fixLibraryFiles]

[code: Installer Installer::installEmailTemplate]

[note: docs/release-notes/README-3.2.0]

[data: dbscripts/xml/upgrade/3.2.1_update.xml]

[code: Installer Installer::installEmailTemplate]

[migration: PKPv3_2_1SubeditorCategoriesMigration]

[note: docs/release-notes/README-3.2.1]

[migration: PKPv3_3_0UpgradeMigration]

ERROR: Upgrade failed: DB: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `js` where `js`.`journal_id` = ? and `js`.`setting_name` in (?, ?, ?, ?, ?, ?' at line 1 (SQL: delete from `journal_settings` as `js` where `js`.`journal_id` = 1 and `js`.`setting_name` in (authorInformation, librarianInformation, openAccessPolicy, privacyStatement, readerInformation, submissionChecklist, clockssLicense, lockssLicense) and `js`.`locale` not in (en_US))