I am running a OMP database upgrade from 3.1.2 to 3.3.0, which fails with
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'publication_id' cannot be null (SQL:
UPDATE publication_formats as pf
SET publication_id = (
SELECT s.current_publication_id
FROM temp_publication_formats as tpf
LEFT JOIN submissions as s
ON (tpf.submission_id = s.submission_id)
WHERE pf.publication_format_id = tpf.publication_format_id
)
)
I see there is a similar topic with this message.
I am trying to upgrade an OMP instance I have not personally installed nor upgraded before, so it may well be that some previous upgrade went wrong. That means I can not simply go back to a completely clean state.
It seems to be this update: https://github.com/pkp/omp/blob/main/dbscripts/xml/upgrade/3.2.0_versioning.xml#L74:L84
Running this partial query gives me several publication format entries without a matching submissions entry:
SELECT tpf.publication_format_id, s.*
FROM temp_publication_formats as tpf
LEFT JOIN submissions as s
ON (tpf.submission_id = s.submission_id)
One “solution” would be to just delete those entries in publication_formats before attempting the upgrade, as described here. I was wondering if someone with a little bit more knowledge of OMP’s internals can tell me if an entry in publication_formats
without a corresponding submission is somehow a possible state. The linked comment was the last before the old thread was closed, otherwise I would have posted this content there.
As a side note: I was wondering if there is a way for me to evaluate my database structure after all updates are applied (to check if there may be lurking some unpatched tables that may cause problems in future upgrades).