How to Fix OJS Upgrade Loop: Database Errors in site and notification_subscription_settings Tables

Table of Contents

1. What Happens When an OJS Upgrade Gets Stuck
2. The Three Errors That Keep the Upgrade in a Loop
3. Why Re-Running the Upgrade Makes the Problem Worse
4. The Safe Way to Identify Where the Migration Failed
5. Resuming the Migration From the Failure Point
6. Verifying the Database After a Successful Migration
7. How to Prevent This Scenario in the Future

What Happens When an OJS Upgrade Gets Stuck

Upgrading Open Journal Systems (OJS) should be straightforward, download the new version, run the migration script, and move on. But when the migration stops halfway, the database ends up trapped between two versions and the journal stays locked in maintenance mode.

We have seen this exact situation multiple times while helping journals through version upgrades. A recent case on the PKP community forum involved an upgrade from OJS 3.4.0.8 to 3.5.0.3 where the migration script entered a loop, rotating between three database errors in the site and notification_subscription_settings tables. Each re-run of the upgrade brought back the same errors, and the journal stayed offline for months.

In this article, we break down what causes this loop and share the recovery steps that the PKP development team recommends, the same approach we use when handling failed migrations for OJS journals.

 

The Three Errors That Keep the Upgrade in a Loop

When the migration script runs on a database that has already been partially modified by a previous failed attempt, it repeatedly hits the same three errors:

1. Missing column in site table: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'redirect' in 'site'
   The migration script needs to modify a column named redirect in the site table, but that column no longer exists because a prior run already renamed or dropped it.
2. Duplicate column in site table: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'redirect_context_id'
   After the redirect column was renamed to redirect_context_id in the first attempt, the script tries the same rename again on a fresh run and fails because the new column name is already present.
3. Missing column in notification_subscription_settings: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'context'
   The script looks for a column named context to rename it to context_id, but the column was already renamed by the earlier partial migration.

These three errors cycle back and forth because manually fixing one, such as adding back the redirect column, pushes the script forward just enough to re-create a conflict further down the same table. We have seen journal administrators try this patchwork approach and end up in a worse position than where they started.

Why Re-Running the Upgrade Makes the Problem Worse

The instinct when a migration fails is to hit the upgrade script again. In a partially migrated database, that instinct is dangerous. Jonas Raoni from the PKP team gave a direct warning on the forum: “DO NOT re-run the upgrade using a failed database unless you’re instructed to do it, otherwise you’re probably going to corrupt or lose data.”

Re-running the migration is what creates the loop in the first place. Each run pushes the database schema further away from both the original and target states. The site table ends up with a mix of old and new column names. The notification_subscription_settings table loses columns the script expects to find. Foreign key constraints in other tables such as filters start throwing duplicate key errors. The more times the script runs, the harder the recovery becomes.

For OJS journal managers without a dedicated developer, this is the point where the upgrade goes from a routine task to a multi-week outage. We have covered a broader approach to planning OJS upgrades in our OJS version upgrade planning guide, which walks through pre-migration checklists that help catch these issues before they happen.

 

The Safe Way to Identify Where the Migration Failed

The correct approach is to find the exact migration step where the process stopped, then resume only from that point. Here is how the PKP team recommends doing it, and how we approach it when troubleshooting for our clients:

1. Locate the migration list. Open the file dbscripts/xml/upgrade.xml in the OJS installation directory. It contains every migration step ordered sequentially.
2. Use a binary search. Instead of reading every entry from top to bottom, pick a migration roughly in the middle. Open its corresponding PHP file and check whether the structural changes it makes, such as adding a column or creating an index, already exist in your database. If the change was applied, the failure happened later. If not, the failure happened earlier. Repeat with the next midpoint until you narrow down the exact script that broke.
3. Examine the broken migration. Once you identify the failed step, open its PHP class file and follow the commands line by line. Compare each command against the actual state of your database. You are looking for the last command that executed correctly before the failure.

This process requires familiarity with MySQL schema inspection and reading PHP migration code. If your journal team does not have that background, bringing in a developer is the safer path, and it is the advice the PKP team gave on the forum.

Resuming the Migration From the Failure Point

After you know which step failed and which commands inside it completed successfully, you can prepare the migration to resume:

• Remove completed migrations from upgrade.xml. Comment out or delete the <migration> entries that ran without errors. The script only needs to re-process the broken step and everything after it.
• Complete the broken migration manually. If the failed step processed 500 of 1,000 records before crashing, your developer needs to finish the remaining 500 programmatically or via direct SQL. The goal is to bring the database to the exact state that step was supposed to produce.
• Leave the version table alone. The versions table only updates after the entire upgrade completes. It is not a reliable indicator of which individual migrations ran.

Only after these preparations should you run php tools/upgrade.php upgrade. The script will skip completed migrations and pick up from the patched failure point.

Verifying the Database After a Successful Migration

Once the upgrade script completes without errors, verify that the schema matches a clean OJS 3.5 installation. Jonas Raoni suggested installing a fresh OJS 3.5 instance in a separate environment and comparing its database structure with the upgraded one.

Check for:

• Column names and data types across core tables
• Primary keys and indexes
• Foreign key constraints

Minor differences may exist due to plugin data or custom configurations, but the structural elements, table definitions, column presence, and relationships, should match the reference install.

How to Prevent This Scenario in the Future

The database loop in this case was triggered by re-running the migration on a database that was already partially modified by a previous failed attempt. That cycle is what makes the errors repeat, and it only gets harder to break the longer it continues.

Making the situation far worse, the journal did not have a clean pre-upgrade backup. Without a snapshot of the database before the first migration attempt, recovery had to be done manually rather than through a simple restore. We have written about how to set up a reliable backup routine for OJS in our backup and disaster recovery guide for OJS journals, which covers the exact steps to take before any major version change.

Before any OJS version update, our team always recommends:

• Take a full database dump using mysqldump or your hosting panel
• Copy the entire OJS files directory, especially public/ and the files/ directory
• Run the upgrade in a staging environment first when possible

If your journal uses plugins from third-party developers, confirm compatibility with the target OJS version before starting. Plugin conflicts are a common trigger for migration failures.

When an upgrade goes sideways despite these precautions, having a partner who has navigated these situations before makes the difference between hours of downtime and days. Open Journal Theme provides OJS upgrade and migration services for journal teams that need hands-on assistance. We have recovered databases from exactly the kind of half-migrated state described in this article, and we know which steps to take, and which to avoid.

Beyond the upgrade itself, maintaining a properly secured OJS installation reduces the risk of incidents where a quick restore becomes necessary. The OJT Advance Security plugin adds hardening layers that keep the installation stable through routine maintenance cycles.

Once your journal is running on the latest OJS release, it is also a good time to assess whether the front-end is keeping pace. A modern theme like Noble ensures full compatibility with current OJS features while giving readers and authors a professional browsing experience. Many of the journals we work with pair an upgrade with a theme refresh, it is a natural moment to improve both the backend and the reader-facing side of the journal.

---

If you need help with an OJS upgrade, database recovery, or custom theme work, our team at Open Journal Theme handles complex migration scenarios regularly. Reach out through the contact page and we will help you get your journal back online.