Help with upgrading from 3.3.0.22 to 3.5.0-3

[vlada]

Hi everyone,

I am attempting to upgrade OJS from 3.3.0-22 to 3.5.0-3. The process is consistently failing in the database migration stage. Each time I resolve one error and restart the upgrade, the script fails again because it doesn’t account for changes already made in the previous (failed) attempt.

Here is a chronological log of the errors encountered:

1. Unique Index Conflicts (Duplicate Key 1061) The script repeatedly fails when trying to add unique indexes that were already created during partial runs. I’ve had to manually drop these to proceed:

genre_settings_unique

review_round_files_unique

event_log_settings_unique

announcement_settings_unique

category_settings_unique

oai_resumption_tokens_unique

2. Foreign Key Constraint Blocks (#1828) The upgrade stopped because it couldn’t drop columns linked to foreign keys:

current_issue_id in journals (blocked by journals_current_issue_id_foreign)

context_id in announcement_types (blocked by announcement_types_context_id_foreign)

3. Missing Columns during Data Migration (#1054) Later stages of the migration expected columns that were either already dropped or renamed:

Unknown column ‘i.current’ in ‘where clause’ during issue migration.

Unknown column ‘at.assoc_type’ in ‘where clause’ in announcement_types migration.

4. Duplicate Columns and Drop Index Errors (#1060 & #1091)

Duplicate column name ‘context_id’ when the script tried to rename assoc_id.

Can’t DROP INDEX ‘announcement_types_assoc’; check that it exists – the script fails if the index was already removed.

Environment:

Source Version: OJS 3.3.0.22

Target Version: OJS 3.5.0.3

PHP Version: 8.2

DB: 10.4.32-MariaDB

It seems the migrations are not fully idempotent. I am currently stuck at the announcement_types foreign key constraint.

Has anyone else experienced this “loop” of manual database fixes during the 3.5 upgrade? Is there a recommended way to skip these checks or a master SQL script to prepare the 3.3 database for the 3.5 structure?

What I’ve tried so far:

To break out of the loop, I’ve been manually running a cleanup script to drop indexes that were causing the “Duplicate key” errors. I used SET FOREIGN_KEY_CHECKS = 0; to ensure the commands executed, but the upgrade eventually hits a new conflict or reverts to a previous state.

Here is the SQL I used to mitigate the index issues:

SQL

SET FOREIGN_KEY_CHECKS = 0;

– OAI, Categories, and basic repeaters
DROP INDEX IF EXISTS oai_resumption_tokens_unique ON oai_resumption_tokens;
DROP INDEX IF EXISTS category_settings_unique ON category_settings;
DROP INDEX IF EXISTS journal_settings_unique ON journal_settings;
DROP INDEX IF EXISTS author_settings_unique ON author_settings;

– SETTINGS tables (Main cause of the loop)
DROP INDEX IF EXISTS site_settings_unique ON site_settings;
DROP INDEX IF EXISTS user_settings_unique ON user_settings;
DROP INDEX IF EXISTS notification_settings_unique ON notification_settings;
DROP INDEX IF EXISTS plugin_settings_unique ON plugin_settings;
DROP INDEX IF EXISTS library_file_settings_unique ON library_file_settings;
DROP INDEX IF EXISTS submission_settings_unique ON submission_settings;
DROP INDEX IF EXISTS publication_settings_unique ON publication_settings;
DROP INDEX IF EXISTS genre_settings_unique ON genre_settings;
DROP INDEX IF EXISTS issue_settings_unique ON issue_settings;
DROP INDEX IF EXISTS navigation_menu_item_settings_unique ON navigation_menu_item_settings;
DROP INDEX IF EXISTS submission_file_settings_unique ON submission_file_settings;

– Announcement / Event / Scheduler
DROP INDEX IF EXISTS announcement_settings_unique ON announcement_settings;
DROP INDEX IF EXISTS announcement_type_settings_unique ON announcement_type_settings;
DROP INDEX IF EXISTS event_log_settings_unique ON event_log_settings;
DROP INDEX IF EXISTS scheduled_tasks_unique ON scheduled_tasks;

– Email Templates
DROP INDEX IF EXISTS email_templates_default_data_unique ON email_templates_default_data;
DROP INDEX IF EXISTS email_templates_settings_unique ON email_templates_settings;

– Reviews and Workflow
DROP INDEX IF EXISTS review_round_files_unique ON review_round_files;
DROP INDEX IF EXISTS review_files_unique ON review_files;
DROP INDEX IF EXISTS review_form_responses_unique ON review_form_responses;
DROP INDEX IF EXISTS review_form_settings_unique ON review_form_settings;
DROP INDEX IF EXISTS review_form_element_settings_unique ON review_form_element_settings;

– Versions and others
DROP INDEX IF EXISTS versions_unique ON versions;
DROP INDEX IF EXISTS citation_settings_unique ON citation_settings;
DROP INDEX IF EXISTS filter_settings_unique ON filter_settings;
DROP INDEX IF EXISTS user_group_settings_unique ON user_group_settings;

SET FOREIGN_KEY_CHECKS = 1;
Despite this manual intervention, the upgrade script eventually triggers new errors (like the Foreign Key and Missing Column issues mentioned above), forcing me back into the loop. It appears the migration logic is not checking for the existence of these constraints before attempting to recreate them.

Thanks in advance for any help!

[asmecher]

Hi @vlada,

Did you take a backup before the first upgrade attempt? Partial upgrade attempts will leave the database in an indeterminate state. It’s best to restore from backup before trying again.

Regards,
Alec Smecher
Public Knowledge Project Team

[vlada]

Hi @asmecher,

I have a database backup; I even took the site down and set it up locally, but the same thing still happens. It starts like this:

Errors occurred during installation:

A database error has occurred: Storage engine that doesn’t support foreign key constraints detected in one or more tables: access_keys, announcements, announcement_settings, announcement_types, announcement_type_settings, authors, author_settings, auth_sources, categories, category_settings, citations, citation_settings, completed_payments, controlled_vocabs, controlled_vocab_entries, controlled_vocab_entry_settings, custom_issue_orders, custom_section_orders, data_object_tombstones, data_object_tombstone_oai_set_objects, data_object_tombstone_settings, deposit_points, deposit_point_settings, edit_decisions, email_log, email_log_users, email_templates, email_templates_default, email_templates_default_data, email_templates_settings, event_log, event_log_settings, files, filters, filter_groups, filter_settings, genres, genre_settings, institutional_subscriptions, institutional_subscription_ip, issues, issue_files, issue_galleys, issue_galley_settings, issue_settings, item_views, jobs, journals, journal_settings, library_files, library_file_settings, metadata_descriptions, metadata_description_settings, metrics, navigation_menus, navigation_menu_items, navigation_menu_item_assignments, navigation_menu_item_assignment_settings, navigation_menu_item_settings, notes, notifications, notification_mail_list, notification_settings, notification_subscription_settings, oai_resumption_tokens, pln_deposits, pln_deposit_objects, plugin_settings, publications, publication_categories, publication_galleys, publication_galley_settings, publication_settings, publons_reviews, publons_reviews_settings, queries, query_participants, queued_payments, review_assignments, review_files, review_forms, review_form_elements, review_form_element_settings, review_form_responses, review_form_settings, review_rounds, review_round_files, rt_contexts, rt_searches, rt_versions, scheduled_tasks, sections, section_settings, sessions, site, site_settings, stage_assignments, static_pages, static_page_settings, subeditor_submission_group, submissions, submission_artwork_files, submission_comments, submission_files, submission_file_revisions, submission_file_settings, submission_search_keyword_list, submission_search_objects…

I get past this, then the following appears:

A database error has occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘start’‘’ at line 1 (Connection: mysql, SQL: alter table submissions change submission_progress_temp submission_progress varchar(50) collate ‘utf8_general_ci’ not null default ‘‘start’’)

… and I fix this as well. Then:

A database error has occurred: A row with setting_name=“submissionChecklist” found in table journal_settings without the expected setting_value. Expected an array encoded in JSON but found:

All submissions must meet the following requirements.

The submission has not been previously published, nor is it before another journal for consideration (or an explanation has been provided in Comments to the Editor).

The official journal template has been used (download from: https://internationaljournalofdisasterriskmanagement.com/Vol1/libraryFiles/downloadPublic/15
).

Starting from 1 January 2026, the International Journal of Disaster Risk Management (IJDRM) applies an Article Processing Charge (APC) for accepted manuscripts only. What the APC covers – The APC contributes to the costs of editorial processing and publication production, including administrative handling, copyediting, layout/typesetting, online publication, and related publishing services…

I fix this too, and then comes:

A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘at.assoc_type’ in ‘where clause’ (Connection: mysql, SQL: select distinct at.type_id from announcement_types as at left join journals as c on at.assoc_id = c.journal_id where c.journal_id is null or at.assoc_type <> 256)

I fix this as well, and then this appears:

Errors occurred during installation:

A database error has occurred: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘current_issue_id’ (Connection: mysql, SQL: alter table journals add current_issue_id bigint null)

Then this:

Errors occurred during installation:

A database error has occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘i.current’ in ‘where clause’ (Connection: mysql, SQL: select i.issue_id from issues as i where i.journal_id = 1 and i.current = 1)

I fix this too, and from here a loop starts:

A database error has occurred: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘current_issue_id’ (Connection: mysql, SQL: alter table journals add current_issue_id bigint null)

First with one table, then with a whole bunch of tables… Duplicate, duplicate, duplicate…

[asmecher]

Hi @vlada,

It looks like your database is significantly garbled; there’s unfortunately nothing to do but…

1. Restore from pre-upgrade backup,
2. Address the issues you’ve already identified,
3. Run the upgrade,
4. If a new error is encountered, add the solution to the list of issues in step 2.

The most common causes for tangled situations like this are very old OJS installations that have some data corruption, and installations that have experienced a failed upgrade and were not restored from backup.

Note that starting with OJS 3.4.0-x, and increasingly in OJS 3.5.0-x, we’ve added referential database integrity so these kinds of problems will not happen in the future. However, to get through the upgrade, you’ll need to resolve any data issues that OJS doesn’t automatically resolve by itself during the upgrade process.

If there’s a particular message you haven’t been able to resolve, feel free to post it here and I may be able to give guidance.

Regards,
Alec Smecher
Public Knowledge Project Team