OJS update (from 3.3.0 to 3.4.0) fails: `a foreign key constraint fails: doi_settings_doi_id_foreign`

Describe the issue or problem
Upgrade from 3.3.0 to 3.4.0 fails at:

ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (ojs.doi_settings, CONSTRAINT doi_settings_doi_id_foreign FOREIGN KEY (doi_id) REFERENCES dois (doi_id) ON DELETE CASCADE) (SQL: insert into doi_settings (doi_id, setting_name, setting_value) values (, registrationAgency, DataciteExportPlugin))

[..]
2024-06-10 10:23:40 [migration: PKP\migration\upgrade\v3_4_0\I8060_UpdateUserLocalesDefaultToEmptyArrayFromNull]
2024-06-10 10:23:41 [migration: PKP\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString]
2024-06-10 10:24:49 [migration: APP\migration\upgrade\v3_4_0\I7129_IssueEntityDAORefactor]
2024-06-10 10:24:52 [migration: APP\migration\upgrade\v3_4_0\I6091_AddFilterNamespaces]
2024-06-10 10:24:52 [migration: APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys]
2024-06-10 10:33:59 [migration: APP\migration\upgrade\v3_4_0\I6093_AddForeignKeys]
2024-06-10 10:37:05 [migration: APP\migration\upgrade\v3_4_0\MergeLocalesMigration]
2024-06-10 10:42:29 [migration: PKP\migration\upgrade\v3_4_0\FailedJobsMigration]
2024-06-10 10:42:29 [migration: PKP\migration\upgrade\v3_4_0\UpgradeMigration]
2024-06-10 10:42:39 [migration: APP\migration\upgrade\v3_4_0\I6807_SetLastModified]
2024-06-10 10:42:39 [migration: APP\migration\upgrade\v3_4_0\I4235_OAISetSpec]
2024-06-10 10:42:39 [migration: APP\migration\upgrade\v3_4_0\I7264_UpdateEmailTemplates]
2024-06-10 10:42:53 [migration: APP\migration\upgrade\v3_4_0\I7596_RemoveNonExpiring]
2024-06-10 10:42:53 [migration: APP\migration\upgrade\v3_4_0\I7014_DoiMigration]
2024-06-10 10:45:41 [revert migration: APP\migration\upgrade\v3_4_0\I7596_RemoveNonExpiring]
2024-06-10 10:45:41 [revert migration: APP\migration\upgrade\v3_4_0\I7264_UpdateEmailTemplates]
2024-06-10 10:45:55 [revert migration: APP\migration\upgrade\v3_4_0\I4235_OAISetSpec]
2024-06-10 10:45:55 [revert migration: APP\migration\upgrade\v3_4_0\I6807_SetLastModified]
2024-06-10 10:45:55 [revert migration: PKP\migration\upgrade\v3_4_0\UpgradeMigration]
2024-06-10 10:45:55 [revert migration: PKP\migration\upgrade\v3_4_0\FailedJobsMigration]
2024-06-10 10:45:55 [revert migration: APP\migration\upgrade\v3_4_0\MergeLocalesMigration]
2024-06-10 10:45:55 [downgrade for "APP\migration\upgrade\v3_4_0\MergeLocalesMigration" unsupported: Downgrade not supported]
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`ojs`.`doi_settings`, CONSTRAINT `doi_settings_doi_id_foreign` FOREIGN KEY (`doi_id`) REFERENCES `dois` (`doi_id`) ON DELETE CASCADE) (SQL: insert into `doi_settings` (`doi_id`, `setting_name`, `setting_value`) values (, registrationAgency, DataciteExportPlugin))

Any idea where I can start searching?

Steps I took leading up to the issue

What application are you using?
OJS 3.3.0

Additional information
Code version: 3.4.0.5
Database version: 3.3.0.8
Latest version: 3.4.0.5

Got a bit further

There are indeed missing doi_id entries at ojs/classes/migration/upgrade/v3_4_0/I7014_DoiMigration.php at bdcf22434ce026d184377f66f0ec2f04baaa8480 · pkp/ojs · GitHub

I added

				if(!isset($item['doi_id'])) {
					echo "missing".PHP_EOL;
				}

after if (isset($item['datacite::status'])) {

and got 4 hits where doi_id is not set? How to proceed? ignore them inside migration?

Okay, got the following results from my tests:

Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)
missing2
Array
(
    [doi_id] =>
    [datacite::registeredDoi] => ****removed****
    [datacite::status] => registered
)

Thats from a print_r($item) in my mentioned if from above.

I know, your time is limited, but @asmecher @bozana could you take a look into that? This is a blocker at our side :confused:

Thanks in advance!

We rolled back for now, but we can continue testing at a copy.

Hi @Commifreak,

I think @ewhanson had already saw/solved a similar issue, so I will first ask him if he remember how to best proceed.

EDIT: it seems there are entries for ‘DataCite’ status for the objects that actually do not have a DOI…

Best,
Bozana

Well, they hav a registeredDoi but no doi_id. I will await further reponses. Thanks! :slight_smile:

And are there settings with setting_name = ‘pub-id::doi’ for the publication(s) of those submissions in the publication_settings DB table?

Yes.

publication_id: 11661
locale: -empty-
setting_name: pub-id::doi
setting_value: **removed**

Thats from one example where my patched “check” hit.

Hi @Commifreak, I’ve seen a few cases with similar errors. Let me know if any of the info in this thread is helpful:

Thanks for the hint! Iam not sure if this helps because I dont understand the tables right. However, I tried your mentioned SQL query to find corrupt data.

The query returns two rows but they mention DOIs which were not in my print_r at all. I had 7 hits for my code above but only two hits (with different DOI values) from your sql code.

EDIT: I also have checked our TEST instance with that SQL query and it brought up even more rows - but the upgrade to 3.4 went without any errors?

Hi @Commifreak, thanks for giving that a try. We can always hope the solution will be an easy one we already know, but it sounds like that wasn’t the case!

Another case where I’ve seen something similar happen is when the journal/install has previously used more than one registration agency or had the plugins for more than one registration agency active. Have you ever used Crossref in addition to or instead of Datacite? Or have the plugins ever both been enabled even if they were n’t properly configured?