OJS problems updating from 3.2.1.1 to 3.3.0-10 LTS [POSTGRESQL]

Hi,

I am testing updating my OJS version to the latest available today.
From 3.2.1.1 to 3.3.0-10
I encountered some data consistency issues:

ERROR: Upgrade failed: DB: SQLSTATE[23502]: Not null violation: 7 ERROR: column “assoc_id” of relation “email_log” contains null values (SQL: ALTER TABLE email_log ALTER assoc_id SET NOT NULL)

and

ERROR: Upgrade failed: DB: SQLSTATE[23502]: Not null violation: 7 ERROR: column “publication_id” of relation “authors” contains null values (SQL: ALTER TABLE authors ALTER publication_id SET NOT NULL)

For testing purposes, I deleted all records that were in these cases, using the command:

delete from email_log where assoc_id is null;
delete from authors where publication_id is null;

But now I have the following error:

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535 (SQL: select “locale”, “submission_id” from “publications” where “publication_id” in ([…]))

Here we use postgresql, I believe it is one of the biggest installations that use postgresql as a database.

Could anyone help me with this error?

Regards,
Tarcisio Pereira.

Hi,

Does anyone have any ideas?

Regards,
Tarcisio Pereira

Hi,

I’m testing updating to 3.3.0-12.
Same problems.

For testing porpses I done this:

124 //AJUSTE UPGRADE
126 $chunked_PublicationIds = array_chunk($currentPublicationIds,65000,true);
127 foreach ($chunked_PublicationIds as $publicationIds_chunk){
129 $submissionLocales = Capsule::table(‘publications’)
130 ->whereIn(‘publication_id’, $publicationIds_chunk)
131 ->pluck(‘locale’, ‘submission_id’);
132 foreach ($submissionLocales as $submissionId => $locale) {
133 Capsule::table(‘submissions as s’)
134 ->where(‘s.submission_id’, ‘=’, $submissionId)
135 ->update([‘locale’ => $locale]);
136 }
137 }
139 //FIM AJUSTE UPGRADE
in file lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php

Now I’m facing a new problem:
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”)

Can someone please help me?

Regards,
Tarcisio Pereira

Hi you all,

Ok, let’s summarize this mess.

Error 1:

ERROR: Upgrade failed: DB: SQLSTATE[23502]: Not null violation: 7 ERROR: column “assoc_id” of relation “email_log” contains null values (SQL: ALTER TABLE email_log ALTER assoc_id SET NOT NULL)

Solved by:

delete from email_log where assoc_id is null;

Error 2:

ERROR: Upgrade failed: DB: SQLSTATE[23502]: Not null violation: 7 ERROR: column “publication_id” of relation “authors” contains null values (SQL: ALTER TABLE authors ALTER publication_id SET NOT NULL)

Solved by:

delete from authors where publication_id is null;

Error 3:

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535 (SQL: select “locale”, “submission_id” from “publications” where “publication_id” in ([…]))

Solved by editing file lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php:

119 // pkp/pkp-lib#6057: Migrate locale property from publications to submissions
120 Capsule::schema()->table(‘submissions’, function (Blueprint $table) {
121 $table->string(‘locale’, 14)->nullable();
122 });
123 $currentPublicationIds = Capsule::table(‘submissions’)->pluck(‘current_publica tion_id’);
124
125 //AJUSTE USP UPGRADE
126 $chunked_PublicationIds = $currentPublicationIds->chunk(65000);
127 foreach ($chunked_PublicationIds as $publicationIds_chunk){
128 $submissionLocales = Capsule::table(‘publications’)
129 ->whereIn(‘publication_id’, $publicationIds_chunk)
130 ->pluck(‘locale’, ‘submission_id’);
131 foreach ($submissionLocales as $submissionId => $locale) {
132 Capsule::table(‘submissions as s’)
133 ->where(‘s.submission_id’, ‘=’, $submissionId)
134 ->update([‘locale’ => $locale]);
135 }
136 }
137 //FIM AJUSTE USP UPGRADE

So the problem is in PDO.
PDO stores count of query parameters in the unsigned int, so it can’t operate more than 65535 parameters at once.

Error 4:

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”)

Solved by:

ALTER TABLE submission_files RENAME CONSTRAINT article_files_pkey TO submission_files_pkey;

Error 5:
Unable to alter sequence named “submission_files_file_id_seq”.
Solved by:

ALTER SEQUENCE article_files_file_id_seq RENAME TO submission_files_file_id_seq;

Error 6:
Unable to complete submission file migrations specific to OJS. Fix in query and additional DELETE statement

Solved by editing file lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php:

185 /**
186 * Complete submission file migrations specific to OJS
187 *
188 * The main submission file migration is done in
189 * PKPv3_3_0UpgradeMigration and that migration must
190 * be run before this one.
191 */
192 private function _migrateSubmissionFiles() {
193 Capsule::schema()->table(‘publication_galleys’, function (Blueprint $table) {
194 $table->renameColumn(‘file_id’, ‘submission_file_id’);
195 });
196 Capsule::statement(‘UPDATE publication_galleys SET submission_file_id = NULL WHERE submission_file_id = 0’);
197 //Ajuste USP
198 echo(‘OrphanedIds’);
199 // pkp/pkp-lib#6616 Delete publication_galleys entries that correspond to nonexistent submission_files
200 $orphanedIds = Capsule::table(‘publication_galleys AS pg’)
201 ->leftJoin(‘submission_files AS sf’, ‘pg.submission_file_id’, ‘=’, ‘sf.file_id’)
202 ->whereNull(‘sf.file_id’)
203 ->whereNotNull(‘pg.submission_file_id’)
204 ->pluck(‘pg.submission_file_id’, ‘pg.galley_id’);
205 foreach ($orphanedIds as $galleyId => $submissionFileId) {
206 error_log(“Removing orphaned publication_galleys entry ID $galleyId with submission_file_id $submissionFileId”);
207 Capsule::table(‘publication_galleys’)->where(‘galley_id’, ‘=’, $galleyId)->delete();
208 }
209
210 Capsule::schema()->table(‘publication_galleys’, function (Blueprint $table) {
211 $table->bigInteger(‘submission_file_id’)->nullable()->unsigned()->change();
212 //Ajuste USP
213 Capsule::statement(‘delete from publication_galleys where submission_file_id in (select submission_file_id from publication_galleys EXCEPT (select s ubmission_file_id from submission_files));’);
214 $table->foreign(‘submission_file_id’)->references(‘submission_file_id’)->on(‘submission_files’);
215 });
216 echo(‘Cleaned OrphanedIds’);
217 }
218 }

But I’m not sure if all those fixes are safe to use.
Can someone check it?

Regards,
Tarcisio Pereira

2 Likes

Hi,

All problems still remains in version ojs-3.3.0-13.
Can someone please help me? Almost one year and no answer :pensive:

Regards,
Tarcisio Pereira

Hi @Tarcisio_Pereira,

I see you’ve attempted to fix the issues by yourself. Out of curiosity, were you able to upgrade? If not, I might try to help with the remaining issues.

Best,
Jonas

Hi @jonasraoni

After all the fixes it is possible to update, I updated a test version but not the real site.
My main issue is error 3. Since there is a problem with the number of records that PDO can handle in the subquery. I don’t know if the chunk I made is safe and correct.
My secondary question is whether DELETE solutions are safe on error 1 and 2.
I believe sequence renames are not a problem.

Regards,
Tarcisio Pereira

Hi!

The fixes for #1 and #2 are ok, if you want to inspect it better later (e.g. check if something was lost and attempt to recover), you might backup the values.

The one for #3 is also ok, even though it could be done in a more performatic way, I’ll probably create an issue to improve it and other similar scripts.

Best,
Jonas Raoni

Thanks for the feedback.
I will proceed with the upgrade.

Regards,
Tarcisio Pereira

FYI I’ve created an issue in GitHub to address this problem: Error when upgrading a large installation to OJS 3.3.0 under PostgreSQL · Issue #8631 · pkp/pkp-lib · GitHub, thanks for reporting, we have some tests for the upgrades, but they are not handling large datasets :slight_smile:

Best,
Jonas Raoni

1 Like

Hi @Tarcisio_Pereira!

The fix has been merged. Do you still have the old installation? If yes, could you please check if it works for you?

You can see the modifications here:

Best,
Jonas Raoni

1 Like

Hi @jonasraoni

Works like a charm.

Regards,
Tarcisio Pereira

1 Like