Hmm, there’s no NOTNULL constraint on the file_id column of submission_galleys in the database XML schema – is this installation upgraded from a previous version? Are you sure the upgrade completed successfully?
Regards,
Alec Smecher
Public Knowledge Project Team
Hi,
This was a multi-step upgrade from 2.3.7 to 3.0.2. There were some postgres-related errors that were fixed, but in the end the upgrade was successful (maybe a few warnings still).
If this column shouldn’t have that constraint, I guess I can change the properties of the table manually.
Hi again @asmecher,
I altered the table directly in the database, and this seems to have solved the problem. Could there be some postgres-incompatibilities still lingering in the upgrade-scripts that don’t trigger warnings/errors?
We depend on ADODB to sync the database schema (in XML) against the actual database – it’s possible that ADODB improperly synced this. Unfortunately that library is (sometimes necessarily) quite a labyrinth so I’m hesitant to dive into debugging it in this case; if you’re able to relax the constraint that was causing you trouble, then proceed, and if you run into anything similar feel free to post here again.
Regards,
Alec Smecher
Public Knowledge Project Team
We upgraded another installation from 2.4.8 to 3.1.0 and I had to do the same fix for that one also, so the problem remains for postgres. The only significant difference in our fork from upstream is the postgres-patch to adodb in pkp-lib discussed elsewhere.
This command solves the issue:
ALTER TABLE submission_galleys ALTER COLUMN file_id DROP NOT NULL;
It would be nice if the error was returned to the frontend, at the moment there is just a spinner that never finishes.
@asmecher : I hit this issue in the middle of a 2.4.8.2 → 3.1.1.4 upgrade (using postgres). In the convertSupplementaryFiles stage:
ojs2: DB Error: ERROR: null value in column “file_id” violates not-null constraint
DETAIL: Failing row contains (7630, en_US, 6196, null, Untitled, 3, RemoteURL, 0)
My analysis is thus: The old database has the NOT NULL attribute on column file_id of the table article_galleys. The upgrade script creates submission_galleys by renaming article_galleys, and that’s how submission_galleys gets to have the NOT NULL attribute too.
The fix is to use
ALTER TABLE article_galleys ALTER COLUMN file_id DROP NOT NULL;
before running the upgrade script. Maybe it can be added to the script.
Hrm, this is actually a bug in the ADODB library we use to handle database schemas. It’s supposed to relax the NOT NULL constraint when it synchronizes the schema XML (dbscripts/xml/ojs_schema.xml in this case).
It’s on our list to update our version of ADODB (it’s currently quite old) and eventually to stop its use entirely. Hopefully the update will fix the bug. In the meantime, your work-around is good.
Thanks,
Alec Smecher
Public Knowledge Project Team
Hi, i´m getting the same error in same versions upgrade with postgre.
Is there any chance to fix it using the
ALTER TABLE article_galleys ALTER COLUMN file_id DROP NOT NULL;
after upgrade is done? Or do i have to upgrade it again?
Regards,
Victor