Error upgrade failed OJS 3.1.2-4 to OJS 3.3.0-8

Software Support
,
1

Description of issue or problem I’m having:

The command php tools/upgrade.php upgrade fails with error:

ERROR: Upgrade failed: DB: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'publication_id' at row 2020 (SQL: ALTER TABLE authors CHANGE publication_id publication_id BIGINT NOT NULL)

Steps I took leading up to the issue:
I read the relevant forums, which led to [OMP] Error upgrading from version 3.1.2-4 to 3.2.0 #5583

I ran the relevant SQL command:
SELECT COUNT(*) FROM authors a LEFT JOIN submissions s ON (a.submission_id = s.submission_id) WHERE s.submission_id IS NULL;

And got 128 records but the majority of them were just recently added authors/articles. The recently added authors/articles had NULL values for submission_id; while the 5 authors with submission_id values had NULL values for publication_id.

What I tried to resolve the issue:
I did not delete any author records because they seemed to be linked.

Application Version - e.g., OJS 3.1.2:
Current: 3.1.2-3 but trying to update to 3.3.0-8

Additional information, such as screenshots and error log messages if applicable:
Query results…

author_id submission_id seq user_group_id include_in_browse publication_id submission_id locale context_id section_id date_submitted last_modified status submission_progress stage_id current_publication_id date_last_activity work_type
2576 2085 1 1
2577 2086 1 1
2578 2087 1 1
2579 2088 1 1
2580 2089 1 1
7302 0 31 1 8192
7303 0 31 1 8194
7304 0 31 1 8195
7305 1 31 1 8195
7306 2 31 1 8195
7307 3 31 1 8195
7308 0 31 1 8196
7309 1 31 1 8196
7310 2 31 1 8196
7311 3 31 1 8196
7312 0 31 1 8197
7313 1 31 1 8197
7314 2 31 1 8197
7315 0 31 1 8198
7316 0 31 1 8199
7317 1 31 1 8199
7318 0 31 1 8200
7319 0 31 1 8201
7320 0 31 1 8202
7321 0 31 1 8203
7322 1 31 1 8203
7323 0 31 1 8204
7324 0 31 1 8205
7325 0 31 1 8206
7326 0 31 1 8207
7327 0 31 1 8208
7328 1 31 1 8208
7329 0 31 1 8209
7330 0 31 1 8210
7331 0 31 1 8211
7332 0 31 1 8212
7333 0 31 1 8213
7334 0 31 1 8214
7335 1 31 1 8214
7336 2 31 1 8214
7337 0 31 1 8215
7338 0 31 1 8216
7339 0 31 1 8217
7340 1 31 1 8217
7341 0 31 1 8218
7342 0 31 1 8219
7343 0 31 1 8220
7344 0 31 1 8221
7345 0 31 1 8222
7346 1 31 1 8222
7347 0 31 1 8223
7348 0 31 1 8224
7349 0 31 1 8225
7350 1 31 1 8225
7351 0 31 1 8226
7352 0 31 1 8227
7353 0 31 1 8228
7354 0 31 1 8229
7355 0 31 1 8230
7356 0 31 1 8231
7357 1 31 1 8231
7358 0 31 1 8232
7359 0 31 1 8233
7360 1 31 1 8233
7361 0 31 1 8234
7362 0 31 1 8235
7363 0 31 1 8236
7364 0 31 1 8237
7365 1 31 1 8237
7366 2 31 1 8237
7367 0 31 1 8238
7368 0 31 1 8239
7369 0 31 1 8240
7370 0 31 1 8241
7371 0 31 1 8242
7372 0 31 1 8243
7373 0 31 1 8244
7374 0 31 1 8245
7375 0 31 1 8246
7376 0 31 1 8247
7377 1 31 1 8247
7378 0 31 1 8248
7379 0 31 1 8249
7380 0 31 1 8250
7381 0 31 1 8251
7382 1 31 1 8251
7383 2 31 1 8251
7384 0 31 1 8252
7385 0 31 1 8253
7386 1 31 1 8253
7387 0 31 1 8254
7388 1 31 1 8254
7389 0 31 1 8255
7390 0 31 1 8256
7392 0 31 1 8258
7393 0 31 1 8259
7394 1 31 1 8259
7395 2 31 1 8259
7396 0 31 1 8260
7397 1 31 1 8260
7398 2 31 1 8260
7399 0 31 1 8261
7400 0 31 1 8262
7401 0 31 1 8263
7402 0 31 1 8264
7403 0 31 1 8265
7404 0 31 1 8266
7405 1 31 1 8266
7406 2 31 1 8266
7407 0 31 1 8267
7408 0 31 1 8268
7409 1 31 1 8268
7410 0 31 1 8269
7411 0 31 1 8270
7412 0 31 1 8271
7413 0 31 1 8272
7414 0 31 1 8273
7415 0 31 1 8274
7416 1 31 1 8274
7417 2 31 1 8274
7418 0 31 1 8275
7419 0 31 1 8276
7420 1 31 1 8276
7421 0 31 1 8277
7422 0 31 1 8278
7423 0 31 1 8279
7424 0 31 1 8280
7425 0 31 1 8281
2

Just a quick update on this: I decided to try and upgrade to OJS 3.2.1-4 because my main concern was the security issues. Thankfully that update worked without a hitch.

However, I just tried it again after the successful upgrade to OJS 3.2.1-4 and got the same error:
ERROR: Upgrade failed: DB: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'publication_id' at row 2020 (SQL: ALTER TABLE authors CHANGE publication_id publication_id BIGINT NOT NULL)

To be clear: I have made sure to restore the database from a backup each time I attempt to re-run php tools/upgrade.php upgrade.

3

Hi @jamilj,

Your error message looks different from the one you are referring to. Does it appear in the same place of the migration script (data: dbscripts/xml/upgrade/3.2.0_versioning.xml)?

5

Hmm, in this case, it’s definitely another place. I suspect it’s this line: https://github.com/pkp/pkp-lib/blob/334874f12c3cc46324dc0b351376fa98a6067c3e/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php#L116
Can you check or share your migration logs to be sure?

6

Hi @Vitaliy,

I’m sorry for the late reply. I’m not sure where the migration log lives. I just checked the two logs available to me on my server error.log and `web.log. The error log file had some PHP exceptions but none that referred to anything about the ojs-3.3.0-8 migration attempt.

7

You can just run another upgrade from a backup locally from the command line, its output will contain information about running scripts.
In your case, the problem appears to arise later than in the referenced issue but with the same table.

8

Hi @jamilj ,
I had the same error message and I could to resolv with this code belong:

CREATE TEMPORARY TABLE IF NOT EXISTS authors2delete AS (
    SELECT author_id
    FROM authors
    WHERE publication_id IS NULL
);

DELETE FROM author_settings          WHERE author_id IN ( SELECT author_id FROM authors2delete );
DELETE FROM books_for_review_authors WHERE author_id IN ( SELECT author_id FROM authors2delete );
DELETE FROM submission_comments      WHERE author_id IN ( SELECT author_id FROM authors2delete );

DELETE FROM authors                  WHERE author_id IN ( SELECT author_id FROM authors2delete );

DROP TABLE authors2delete;

Try it.

1 Like
9

Hello.
Sorry for my response above in 2021(october).
I had some problem and maybe was becouse with my solution above.
So, if someone still need this solution, today (august/2023) I think different and believe the solution below is better:

update authors SET publication_id = 0 WHERE publication_id IS NULL;

ps.: in my case, I resolved my problem that I caused with my previous answer, through upgrading to 3.3.0.15.