Upgrade failed OJS3.1 to OJS3.3

When running the upgrade script it fails with this error

PHP Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘285-it_IT-preferredPublicName’ for key ‘author_settings_pkey’

The author_settings table seems to be updated quite a lot by this script.

table before:
before

table after:
after

This is the table schema:
table settings

As I can’t proceed with the upgrade I’m a bit stuck.

Because these elements ‘285-it_IT-preferredPublicName’ don’t exist before the upgrade script runs, I can’t see how to manuall fix this in MySQL?

Is it because the user has muliple languages set?

hi there,

Can you paste the author_settings entries from author 285 please?

Jason

here you go - this is before the upgrade:

"author_id","locale","setting_name","setting_value","setting_type"
285,"","orcid","","string"
285,"de_DE","affiliation","","string"
285,"de_DE","biography","","string"
285,"en_US","affiliation","University of KwaZulu-Natal","string"
285,"en_US","biography","<p>Professor, School of Education,&nbsp;University of KwaZulu-Natal</p>","string"
285,"it_IT","affiliation","","string"
285,"it_IT","biography","","string"

Here’s a screenshot
Screenshot_9

Hi @2020media

Sorry for the late reply, things are busy around PKPPS these days.

So, preferredPublicName is created in Upgrade.inc.php as part of the first bit of migration away from OJS 3.1. For each author it finds, it builds an author setting for preferredPublicName for each supported locale in the journal. The code that does this is:

foreach ($supportedLocales as $locale) {
     $preferredPublicName = "$firstName " . ($middleName != '' ? "$middleName " : '') . $lastName . ($suffix != '' ? ", $suffix" : '');
     if (AppLocale::isLocaleWithFamilyFirst($locale)) {
          $preferredPublicName = "$lastName, " . $firstName . ($middleName != '' ? " $middleName" : '');
     }
     $userDao->update(
          "INSERT INTO author_settings (author_id, locale, setting_name, setting_value, setting_type) VALUES (?, ?, 'preferredPublicName', ?, 'string')",
          [(int) $authorId, $locale, $preferredPublicName]
     );
 }

Is it possible that the it_IT locale is duplicated?

Cheers,
Jason

Apologies for my late reply. I have been on leave.

Not quiet sure where to look for this, but here is the “site” table which shows installed locales:

Screenshot_5

Would that be the correct table to check?

I don’t see anything duplicated there.

Been doing some more digging @jnugent comparing this author with others.

I spotted they had a “suffix” set, and they were the first to have this:
before_redacted

I found if I delete the suffix for this user, the error happened on authorid 286 instead. So this seemed pretty conclusive. I have removed all suffix entries (there were a couple more) and now the upgrade script get’s past this.

Instead it is now breaking on what could be a similar thing

[migration: PKPv3_3_0UpgradeMigration]
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘43-it_IT-remoteUrl’ for key ‘navigation_menu_item_settings_pkey’ (SQL: insert into navigation_menu_item_settings (navigation_menu_item_id, locale, setting_name, setting_value, setting_type) values (43, it_IT, remoteUrl, https://www.REDACTED/REDACTED, string))

Similar error.
Looking at this user in the users table they have 3 locales set:
de_DE:en_US:it_IT
Screenshot_6

I tried removing all 3 locales but got the same error.
As this relates to a custom menu item, I could try removing it before the upgrade.

Sidenote: I later found that this OJS install had mixed InnoDB and myISAM tables causing another error. The conversion to all InnoDB has been done but not sure if might have been relevant to these issues.

1 Like

Hi @2020media ,
I found a generic solution for me.
Maybe can help you too:
https://forum.pkp.sfu.ca/t/upgrade-ojs-3-2-1-4-to-3-3-0-8-failed/72049

Hello.
Sorry for my response above in Feb/2021.
I had some problem and maybe was becouse with my solution above.

So, If someone still need this solution, today (august/2023) I think different and believe the solution below is better:

1º) Before the UPGRADE

-- remove the unique key, for allow to include repeated registers
ALTER TABLE `navigation_menu_item_settings` DROP INDEX `navigation_menu_item_settings_pkey`;


2º) After the UPGRADE

-- remove the repeated registers

CREATE TEMPORARY TABLE tmp_navigation_menu_item_settings AS
SELECT *
FROM navigation_menu_item_settings
GROUP BY navigation_menu_item_id, locale, setting_name;

DELETE FROM navigation_menu_item_settings;

INSERT INTO navigation_menu_item_settings
SELECT * FROM tmp_navigation_menu_item_settings;

DROP TABLE tmp_navigation_menu_item_settings;

-- return the unique key
ALTER TABLE `navigation_menu_item_settings` ADD UNIQUE KEY `navigation_menu_item_settings_pkey` (`navigation_menu_item_id`,`locale`,`setting_name`);

ps.: in my case, I resolved my problem that I caused with my previous answer, through upgrading to 3.3.0.15.