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