When upgrading the OJS DB from 3.1.0-0 to 3.2.1-1 I get the following error:
PKP-Database-Logger 1594989653.9116: Query:
UPDATE citations as c
SET c.publication_id = (
SELECT s.current_publication_id
FROM submissions as s
JOIN temp_citations as tc ON s.submission_id = tc.submission_id
WHERE c.citation_id = tc.citation_id
) ORDER BY c.publication_id ASC
failed. Column 'publication_id' cannot be null
...
ERROR: Upgrade failed: DB: Column 'publication_id' cannot be null
However, the suggestions by @asmecher don’t apply unfortunately…
Running SELECT COUNT(*) FROM authors a LEFT JOIN submissions s ON (a.submission_id = s.submission_id) WHERE s.submission_id IS NULL; returns 0 results.
Running SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL; returns ERROR 1054 (42S22): Unknown column 'c.submission_id' in 'on clause' (I’m guessing because this is change introduced after 3.1.0-0).
Any other ideas about where I should look? Many thanks!
When I run it on a fresh copy of the OJS 3.1.0-0 database I get:
SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
ERROR 1054 (42S22): Unknown column 'c.submission_id' in 'on clause'
which is normal, since the citations table does not have the submission_id column yet (but it has the assoc_id one):
describe citations;
+----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+----------------+
| citation_id | bigint(20) | NO | PRI | NULL | auto_increment |
| assoc_type | bigint(20) | NO | MUL | 0 | |
| assoc_id | bigint(20) | NO | | 0 | |
| citation_state | bigint(20) | NO | | NULL | |
| raw_citation | text | YES | | NULL | |
| seq | bigint(20) | NO | | 0 | |
| lock_id | varchar(23) | YES | | NULL | |
+----------------+-------------+------+-----+---------+----------------+
7 rows in set (0.002 sec)
When I run it on the database right after the upgrade has failed I get:
SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
ERROR 1054 (42S22): Unknown column 'c.submission_id' in 'on clause'
which again is normal, since the citations table does not have the submission_id column (but it has the re-renamed publication_id one):
describe citations;
+----------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+----------------+
| citation_id | bigint(20) | NO | PRI | NULL | auto_increment |
| publication_id | bigint(20) | NO | MUL | 0 | |
| raw_citation | text | YES | | NULL | |
| seq | bigint(20) | NO | | 0 | |
+----------------+------------+------+-----+---------+----------------+
4 rows in set (0.003 sec)
It just occured to me however, to adapt the column name in the query for the submissions table , and I get this:
Based on the results of the corrected query, if I now delete those citations on a fresh copy of the OJS 3.1.0-0 database by running:
DELETE FROM citations WHERE citation_id IN (14,15,16,17,18,19,20,21,22,23,24,25,26,27,799,800,2948,2947,2946,2945,2944,801,802,803,804,805,959,960,961,962,963);
Query OK, 31 rows affected (0.004 sec)
the upgrade then works without errors. Thanks again
Did you try the solution on the thread linked above? YES
After running this query after upgrade failed
SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.publication_id = s.submission_id) WHERE s.submission_id IS NULL;
Results is null
After restoring the backup and before running the upgrade script, what do you get for the following database query?
Oppss sorry sir. This screenshot for another thread. But my questions is after running this query below SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.publication_id = s.submission_id) WHERE s.submission_id IS NULL;
I’m sorry, but I still can’t see the entire results output of that query. Could you enlarge the results pane so that the full output is visible? I’m not sure what tool you’re using to connect to the database but I’m still not 100% sure you’re showing zero results vs. a result with a NULL ID.
Regards,
Alec Smecher
Public Knowledge Project Team
, and I get this:
