Issues with Duplicate Keys and Indexes during OJS Upgrade 3.3.0.15 to latest

[Lazar_Stosic]

Description of the problem: I am attempting to upgrade Open Journal Systems (OJS) 3.3.0.15 to 3.4.0.5 but continually encounter errors related to duplicate keys and indexes. Specifically, the errors are as follows:

1. Duplicate key ‘review_form_responses_unique’:

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘review_form_responses_unique’

2. Duplicate key 'review_round_files_unique':

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘review_round_files_unique’

3. Duplicate key 'review_files_unique':

SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key name ‘review_files_unique’

What I have already tried:

I attempted to delete the problematic indexes using SQL commands such as:

ALTER TABLE review_form_responses DROP INDEX review_form_responses_unique;
ALTER TABLE review_round_files DROP INDEX review_round_files_unique;
ALTER TABLE review_files DROP INDEX review_files_unique;

Despite deleting these indexes, they are recreated during the upgrade process, causing the same errors to reoccur.

Request for Assistance:

1. Permanent deletion or correction of the duplicate keys so that they are not recreated during the upgrade.
2. Guidance on properly setting up the database to ensure the upgrade proceeds without errors.
3. Any additional steps I should take to ensure the database is in a correct state before performing the upgrade.

Thank you in advance for your help!

[Joao_Zem]

Hello everything is fine?

I have exactly the same error when upgrading OJS 3.3.9 to 3.4.0-5. Do we have any correction or path regarding this case?

Thanks!

[asmecher]

Hi @Joao_Zem,

If your OJS is reporting version 3.3.9, it means a prior upgrade attempt failed, which is probably the cause of the errors. When you first tried the upgrade from 3.3.x to 3.4.x, what error was reported? Do you have a backup from prior to that attempt?

Regards,
Alec Smecher
Public Knowledge Project Team

[Joao_Zem]

Hello everything is fine?

Thank you for your kind response. I understood the error, I wasn’t aware of this, I’ll keep an eye on it.

I don’t have a backup of the previous version, unfortunately. Is there a way to rollback?

Thanks! =)

[asmecher]

Hi @Joao_Zem,

If a previous upgrade failed, then the structure of the database will be somewhere between 3.3 and 3.4; the error message encountered during upgrade will indicate where the upgrade failed. You would normally have two options:

• Restore from backup and try again, or
• Correct the problem with the database and continue.

It sounds like you don’t have a backup, and don’t have the original error message… Unfortunately that makes it hard to know what to recommend!

Regards,
Alec Smecher
Public Knowledge Project Team

[asmecher]

Hi @Joao_Zem,

My previous post didn’t give you much in the way of options. Let me lay out a way forward that doesn’t require either a backup or knowledge the error that was first encountered, though it’ll take some work.

In short, you’ll need to find out what step in the upgrade failed by looking at your database structure and conducting a “binary search” to see whether the failure occurred before or after the step you’re looking at.

1. Take a good backup before going further.
2. Look through dbscripts/xml/version.xml. This lays out step-by-step how the upgrade is executed. If your OJS reports version 3.3.9, then you’ll be interested in everything in these lines:
   ojs/dbscripts/xml/upgrade.xml at 3_4_0-5 · pkp/ojs · GitHub
3. Pick a step, and look to see if you can find evidence that it was executed. If you can, then the failure must’ve occurred after this step; if not, then it occurred on or during that step.

For example, a good first step might be:

<migration class="APP\migration\upgrade\v3_4_0\I8151_ExtendSettingValues"/>

Inspecting the corresponding class (classes/migration/upgrade/v3_4_0/I8151_ExtendSettingValues.php), you can see that it attempts to change a database column definition:

Schema::table('section_settings', function (Blueprint $table) {
    $table->mediumText('setting_value')->nullable()->change();
});

…and you can see from the relevant Github page for #8151 that the code resolves a problem where the previous TEXT column type was too short for some data.

So you could inspect your database to see what type the section_settings table’s setting_value column looks like, e.g.:

> describe section_settings;
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| section_setting_id | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| section_id         | bigint(20)          | NO   | MUL | NULL    |                |
| locale             | varchar(28)         | NO   |     |         |                |
| setting_name       | varchar(255)        | NO   |     | NULL    |                |
| setting_value      | mediumtext          | YES  |     | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.004 sec)

…and see whether it’s text or mediumtext. Mine is mediumtext, meaning that this part of the OJS 3.3 to 3.4 migration executed successfully.

Regards,
Alec Smecher
Public Knowledge Project Team