Upgrade error from 3.2.1-4 to 3.3.0-5

Software Support
1

Dear,
I have OJS version 3.2.1.4 working perfectly.
I have a question, has the OJS-3.2.x series come to an end or will it still receive more updates?
I am trying to upgrade to OJS-3.3.0-5 without success.
Server configuration: PHP 7.3.22, PostgreSQL 9.5, Apache 2.4
Would anyone know how to solve it?
Thanks
Augusto Torres

Here are the errors found:
php upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.5]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::installDefaultNavigationMenus]
WARNING: The NavigationMenu (ContextId: 7, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 7, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
(
)

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[migration: PKPv3_3_0UpgradeMigration]
A submission file was expected but not found at journals/1/articles/1/submission/1-61-1-1-2-20110223.pdf.
A submission file was expected but not found at journals/1/articles/1/submission/review/1-61-2-1-4-20110223.pdf.
A submission file was expected but not found at journals/1/articles/1/submission/query/1-61-4-1-18-20110223.pdf.
A submission file was expected but not found at journals/1/articles/1/submission/proof/1-61-5-1-10-20110223.pdf.
A submission file was expected but not found at journals/1/articles/2/submission/2-61-6-1-2-20110224.pdf.
(
)
ERROR: Upgrade failed: DB: SQLSTATE[42704]: Undefined object: 7 ERROR: constraint “submission_files_pkey” of relation “submission_files” does not exist (SQL: alter table “submission_files” drop constraint “submission_files_pkey”)

1 Like
3

Hi @torres, I can not find any reference to a migration that drops this foreign key. Are you running from the git repository or did you download the 3.3.0-5 release package? If the release package, did you unpack it into a new directory or over the top of the old one?

4

Hi @NateWr,
Yes, I downloaded the code from https://pkp.sfu.ca/ojs/download/ojs-3.3.0-5.tar.gz
and unzipped it to the newest directory.
Steps:
wget https://pkp.sfu.ca/ojs/download/ojs-3.3.0-5.tar.gz
tar -xvzf ojs-3.3.0-5.tar.gz
chown -R www: www ojs-3.3.0-5
cp -R ojs-old / public for ojs-3.3.0-5 / public
cp config.inc.php for ojs-3.3.0-5
update php.php update

php upgrade.php check
Code version: 3.3.0.5
Database version: 3.2.1.4
Latest version: 3.3.0.5
The database version is older than the code version
Run “upgrade.php upgrade” to update

I think something is wrong when updating the existing submission_files tables in this script.

./lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php

  • Migrate submission files after major refactor
    *
    * - Add files table to manage underlying file storage
    * - Replace the use of file_id/revision as a unique id with a single
    * auto-incrementing submission_file_id, and update all references.
    * - Move revisions to a submission_file_revisons table.
    * - Drop unused columns in submission_files table.

#8 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(496): Illuminate\Database\Schema\Builder->table(‘submission_file
’, Object(Closure))
#9 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(141): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()
#10 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()
#11 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)
#12 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()
#13 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()
#14 /usr/local/www/data/ojs-3.3.0-5/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()
#15 /usr/local/www/data/ojs-3.3.0-5/tools/upgrade.php(22): UpgradeTool->execute()
#16 {main}

Next Illuminate\Database\QueryException: SQLSTATE[42704]: Undefined object: 7 ERROR: constraint “submission_files_pkey” of relation “submission_files” does not exist (SQL: alter table “submission_files” drop constraint “submission_files_pkey”) in /usr/local/www/data/ojs-3.3.0-5/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671

5

@Vitaliy in all of the foreign key issues you found in the 3.3 migration, does this sound familiar? I can’t find any migration code that actually drops the submission_files_pkey foreign key


8

This doesn’t ring a bell for me.
Just tested the upgrade from 3.2.1 to 3.3.0-5 on Postgres and haven’t received any errors.
Is it possible that you are trying to upgrade a database that was already unsuccessfully upgraded? Is this error origins from the intact database upgrade attempt?

9

Dear @Vitaliy,

When you say:
“I just tested the update from 3.2.1 to 3.3.0-5 on Postgres and did not receive any errors.”
Updated without error from version 3.2.1-4?

I am updating a copy of the database in production, our server is working well with 3.2.1-4 (important 4 here).

Before updating to the latest version of 3.2.1-4, I made an attempt to update from version 3.2.1-2 to version 3.3.0-3, but I lost the article titles, but the errors in submission_file and keys “pkey” did not occur.

So I waited for a more recent version of the 3.3.0.x series to follow the updates, and at this time version 3.2.1-4 came out, with bug fixes. I upgraded to this version and these errors started to appear.

These major branche updates always cause a lot of difficulty in updating, especially using PostgreSQL.

This base that I am using in production, was previously sent for analysis by OJS developers, when it presented an error during migration from version 3.1.x to 3.2.x where a bug was found in the code having been fixed.

I thank you for your attention and remain available for more information needed.

Best regards,
Augusto Torres

10

Hi @torres,

The upgrade script in your case fails here: pkp-lib/PKPv3_3_0UpgradeMigration.inc.php at 4eb006211b736f3531966c9df2ba7bce72d02a12 · pkp/pkp-lib · GitHub

Looks like the data regarding the primary key index for the submission_files table in your case is malformed. It should have name submission_files_pkey but according to the error message, it’s absent or has a different name. Can you take a look what indexes the table has? The easiest way would be using phppgadmin or from PostgreSQL command-line interface \d submission_files (like in this example https://stackoverflow.com/a/56924672/6711224)

11

Hi, we seem to have the same problem. We are upgrading the same version as mentioned above. After running \d submission_files we get

               Table "public.submission_files"
   Column       |            Type             | Collation | Nullable |                    Default

--------------------±----------------------------±----------±---------±-----------------------------------------------
file_id | bigint | | not null | nextval(‘article_files_file_id_seq’::regclass)
revision | bigint | | not null |
source_file_id | bigint | | |
source_revision | bigint | | |
submission_id | bigint | | not null |
file_type | character varying(255) | | not null |
file_size | bigint | | not null |
original_file_name | character varying(127) | | |
file_stage | bigint | | not null |
viewable | smallint | | |
date_uploaded | timestamp without time zone | | not null |
date_modified | timestamp without time zone | | not null |
assoc_id | bigint | | |
genre_id | bigint | | |
direct_sales_price | character varying(255) | | |
sales_type | character varying(255) | | |
uploader_user_id | bigint | | |
assoc_type | bigint | | |
Indexes:
“article_files_pkey” PRIMARY KEY, btree (file_id, revision)
“submission_files_stage_assoc” btree (file_stage, assoc_type, assoc_id)
“submission_files_submission_id” btree (submission_id)

I have tried to change article_files_pkey to submission_files_pkey after restoring the database before running the upgrade process again. But afterwards we run into another error:

ERROR: Upgrade failed: DB: SQLSTATE[42P01]: Undefined table: 7 ERROR: sequence “submission_files_file_id_seq” does not exist (SQL: DROP SEQUENCE submission_files_file_id_seq CASCADE)

     Column           |            Type             | Collation | Nullable |                           Default

---------------------------±----------------------------±----------±---------±-------------------------------------------------------------
submission_file_id | bigint | | not null | nextval(‘submission_files_submission_file_id_seq’::regclass)
source_submission_file_id | bigint | | |
submission_id | bigint | | not null |
file_stage | bigint | | not null |
viewable | smallint | | |
created_at | timestamp without time zone | | not null |
updated_at | timestamp without time zone | | not null |
assoc_id | bigint | | |
genre_id | bigint | | |
direct_sales_price | character varying(255) | | |
sales_type | character varying(255) | | |
uploader_user_id | bigint | | |
assoc_type | bigint | | |
file_id | bigint | | not null |
Indexes:
“submission_files_pkey” PRIMARY KEY, btree (submission_file_id)
“submission_files_stage_assoc” btree (file_stage, assoc_type, assoc_id)
“submission_files_submission_id” btree (submission_id)
Foreign-key constraints:
“submission_files_file_id_foreign” FOREIGN KEY (file_id) REFERENCES files(file_id)
Referenced by:
TABLE “review_files” CONSTRAINT “review_files_submission_file_id_foreign” FOREIGN KEY (submission_file_id) REFERENCES submission_files(submission_file_id)
TABLE “review_round_files” CONSTRAINT “review_round_files_submission_file_id_foreign” FOREIGN KEY (submission_file_id) REFERENCES submission_files(submission_file_id)
TABLE “submission_file_revisions” CONSTRAINT “submission_file_revisions_submission_file_id_foreign” FOREIGN KEY (submission_file_id) REFERENCES submission_files(submission_file_id)

Would appreciate if you could help with this error.

12

Dear @Vitaliy @NateWr (cc @ahmed.maxamed),

Finally I successfully upgraded from version ‘OJS-3.2.1-4’ to ‘OJS-3.3.0-5’

Here is the solution:
$ psql ojsdatabase;
ojsdatabasenew = # \ d submission_files (as directed)
Did not find any relation named “submission_files”.

However, adding the name of the complete table:
ojsdatabase = # \ d public.submission_files
Table “public.submission_files”
Column | Type | Modifiers
-------------------- + ----------------------------- + ------------------------------------------------- -----------------------
file_id | bigint | not null default nextval (‘public.article_files_file_id_seq’ :: regclass)
revision | bigint | not null
source_file_id | bigint |
source_revision | bigint |
submission_id | bigint | not null
file_type | character varying (255) | not null
file_size | bigint | not null
original_file_name | character varying (127) |
file_stage | bigint | not null
viewable | smallint |
date_uploaded | timestamp without time zone | not null
date_modified | timestamp without time zone | not null
assoc_id | bigint |
genre_id | bigint |
direct_sales_price | character varying (255) |
sales_type | character varying (255) |
uploader_user_id | bigint |
assoc_type | bigint |
Indexes:
“article_files_pkey” PRIMARY KEY, btree (file_id, revision)
“submission_files_stage_assoc” btree (file_stage, assoc_type, assoc_id)
“submission_files_submission_id” btree (submission_id)

Note that the primary key “article_files_pkey” should be changed to “submission_files_pkey”

But still, during the update attempt, other keys needed to be changed, as follows
From: “public.article_files_file_id_seq”
To: “public.submission_files_file_id_seq”

CREATE SEQUENCE public.article_files_file_id_seq (alter to submission)
START WITH 1
INCREMENT BY 1
IN MINVALUE
NO MAXVALUE
CACHE 1;

Lower:
ALTER TABLE public.article_files_file_id_seq OWNER TO ojsdbadmin;
To: public.submission_files_id_seq

And since we got here, we got the rest right:
ALTER SEQUENCE public.article_files_file_id_seq OWNED BY public.submission_files.file_id;
To: public.submissions_files_file_id_seq

ALTER TABLE ONLY public.submission_files ALTER COLUMN file_id SET DEFAULT nextval (‘public.article_files_file_id_seq’ :: regclass);
To: public.submission_files_file_id_seq

ALTER TABLE ONLY public.submission_files
ADD CONSTRAINT article_files_pkey PRIMARY KEY (file_id, revision);
To: submission_files_pkey

In short, where it is declared as article, referencing the submission_files table, it should be changed to submission

I now have the site running on the current version OJS-3.3.0-5
I just didn’t understand the reason why I was able to migrate from OJS3.2.1-2 to OJS-3.3.0-3 without these problems from the [article] declarations and after upgrading to version 3.2.1-4 they appeared.

Thanks for the great help of the OJS team, always willing to make the system even better.

We have been users of OJS (also from other platforms developed by PKP.org) since the early 2000s, we have also disseminated this system to several interested parties, due to the great success we have been publishing free and quality scientific articles to the world.
Best regards,
Augusto Torres
Computer Support Analyst
CLE-UNICAMP
PublicaçÔes EletrÎnicas - CLE/UNICAMP

5 Likes
13

:tada: That’s great, @torres! I suspect that was the name of the index from many years ago and that’s why we haven’t encountered the issue from anyone else yet. We’ll keep an eye out for anyone else running into this. :+1:

14

Hi @torres. That was great help. We managed to install it now, thank you.