Upgrade failed OJS 3.3.0.20 to 3.3.0.4 Integrity contraint violation: 1062 Duplicate entry '' for key journal_settings_unique

Hi great PKP team. I’am upgrading an OJS from 3.2.0.3 to 3.4.0.8, first I upgraded it from 3.2.0.3 to 3.3.0.20 although the process has finished correctly, one message appeared:

Next Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'setting_name' in 'SELECT' (SQL: select `setting_name`, `setting_value`, `s`.* from `object_for_review_settings` as `s` where `setting_type` = object) in /var/www/html/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671

As the process finished with the “Successfully upgraded to version 3.3.0.20” and decided to continue with the process, so I started the following upgrading from 3.3.0.20 to 3.4.0.8, it’s here where I have found multiple times the same error message which finished the upgrading process:

ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '35--doiPrefix' for key 'journal_settings_unique' (SQL: insert into `journal_settings` (`journal_id`, `setting_name`, `setting_value`) values (35, enableDois, 1), (35, doiCreationTime, copyEditCreationTime), (35, enabledDoiTypes, ["issue","publication"]), (35, doiPrefix, 10.22201), (35, doiSuffixType, customPattern), (35, doiPublicationSuffixPattern, iiec.20078951e.%Y.%i.%a), (35, doiRepresentationSuffixPattern, ), (35, doiIssueSuffixPattern, iiec.20078951e.%Y.%i))

I have restored a dump of OJS 3.3.0.20 database and search the table journal_settings but I couldn’t find any duplicate registries with the value doiPrefix. I ran the following query:

SELECT journal_id, locale, setting_name
FROM journal_settings
WHERE setting_name LIKE '%doi%';

The resultset was:

+------------+--------+------------------+
| journal_id | locale | setting_name     |
+------------+--------+------------------+
|         35 |        | doiPrefix        |
|         35 |        | doiSuffix        |
|         35 |        | doiSuffixPattern |
+------------+--------+------------------+
3 rows in set (0.002 sec)

So I try to find duplicates in the journal_settings table using the following query too:

SELECT journal_id, locale, setting_name , COUNT(*) AS count
FROM journal_settings
GROUP BY journal_id, locale, setting_name
HAVING COUNT(*) > 1;

But 0 rows was the response.

Someone can help me to bypass this error? It’s important to mention that I ran the upgrade process using the docker oficial image so the

Best regards, Thanks.

Hi @juancure,

In the upgrade from 3.3.0 to 3.4.0, the DOI settings get moved from plugin_settings to journal_settings; @ewhanson would know better, but I don’t think doiPrefix or doiSuffix should be in journal_settings at all for the 3.3.0 database. (The code to do that is in lib/pkp/classes/migration/upgrade/v3_4_0/PKPI7014_DoiMigration.php.)

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like

Hi @asmecher thanks for your answer, I had removed the registries doiPrefix and doiSufix from the OJS 3.3.0.20 and ran again the upgrade.php script and finally this finished with success.

1 Like

Hi @juancure,

Great, glad to hear it’s working!

Regards,
Alec Smecher
Public Knowledge Project Team

This topic was automatically closed after 9 days. New replies are no longer allowed.