OJS upgrade error (DB: Column 'publication_id' cannot be null) from 3.1.0-0 to 3.2.1-1

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

This is basically exactly the same as the error described here: [OMP] Error upgrading from version 3.1.2-4 to 3.2.0 · Issue #5583 · pkp/pkp-lib · GitHub

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!

1 Like

Hi @kasioumis,

Are you running this query…

SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL;

…on a fresh copy of your OJS 3.1.0-0 database from backup, or on the database after the upgrade has failed?

Regards,
Alec Smecher
Public Knowledge Project Team

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 :man_facepalming:, and I get this:

SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.publication_id = s.submission_id) WHERE s.submission_id IS NULL;
+-------------+
| citation_id |
+-------------+
|          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 |
+-------------+
31 rows in set (0.013 sec)

Based on this, what’s the recommendation? Many thanks!

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 :slight_smile:

1 Like

After running this query Results is null. This sql query run before upgrade.

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?
Screenshot 2021-03-07 at 8.34.52 AM

Hi @fairuz,

It’s not clear to me whether or not there are results from that screenshot; can you clarify?

Regards,
Alec Smecher
Public Knowledge Project Team

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;

Results is NULL. Not same like @kasioumis

So what i can do after this?

Hi @fairuz,

Do you mean that you get a result but it is NULL, or that you get no results? In the screenshot above, for example, there is one result.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like

Hello Sir… Following this SQL i get this results. EMPTY
Follow this screenshot
Screenshot 2021-07-07 at 2.54.03 PM

Hi @fairuz,

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

1 Like