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
