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?
Hey @Commifreak, thanks for looking into that. Trying to think of what a next step could be…
In your post above: OJS update (from 3.3.0 to 3.4.0) fails: `a foreign key constraint fails: doi_settings_doi_id_foreign` - #3 by Commifreak, you mention these are the entries that showed up without doi_ids. In your pre-upgrade database, is there anything that stands out about these entries? I’m thinking of things like additional fields related to them in the database or possibly if these DOIs appear more than once in the database? I’m also curious what type of object these are for (publication, galley, or issue)?
It may also be worthwhile looking at the DOI migration here (assuming the problematic DOIs in question are publication DOIS):
The query at the beginning is the one that gets all the DOIs in order to add them to the new dois table. I’m not sure if this would result in anything, but you could check if this $doiId is ever null and try and see why this might be happening.
I asked the responsible persons but nothing seems special with these so far.
I’m thinking of things like additional fields related to them in the database or possibly if these DOIs appear more than once in the database?
Idk, I tested some via global text search. The tested one appear two times in submission_settings, with the same key datacite::registeredDoi but with different submission_ids.
One of them appear two times in publication_settings as pub-id::doi.
I’m also curious what type of object these are for (publication, galley, or issue)?
How to tell what type a doi is (via database)? I asked and they said those DOIs are all articles.
you could check if this `$doiId` is ever `null` and try and see why this might be happening.
Hi @Commifreak, apologies for the confusion, the query I mentioned is assumed to be run as part of the migration so modifications have already been made to the table schema to add the doi_id column you mentioned. You could remove 'p.doi_id' from the select part of the query and try it again and it should work on your existing version 3.3.0.8 database.
As for why you cannot use the word ‘qu ote’, I don’t really know. I wasn’t able to either. It appears to be in a blocked word list on the forum to combat spam.
You could remove 'p.doi_id' from the select part of the query and try it again
Did that. I get 8 entries with empty setting_value:
SELECT s.context_id, p.publication_id, pss.setting_name, pss.setting_value
FROM submissions s
LEFT JOIN publications AS p ON p.submission_id = s.submission_id
LEFT JOIN publication_settings AS pss ON pss.publication_id = p.publication_id
WHERE pss.setting_name = 'pub-id::doi'
