OJS3.2 Upgrade Error - Please help

Software Support
,
1

I’m testing my upgrade procedure from OJS 3.1.1-4 to 3.2 on Ubuntu LTS, php7.2, mysql 5.0.12,
Package from https://pkp.sfu.ca/ojs/ojs_download/
using sudo php tools/upgrade.php upgrade
I’m getting
ERROR: Upgrade failed: DB: Duplicate entry '471-1' for key 'citations_publication_seq'

This happens upon dbscripts/xml/upgrade/3.2.0_versioning.xml line 163

The data itself in publication_id = 471 is fine and seq = 1 is unique for 471 (table: citations)

The error seems to be the same as in issue OJS 3.2 citations error on upgrade · Issue #5465 · pkp/pkp-lib · GitHub

Can someone please help? Thank you
Klaus

2

To keep you updated:
I finally commented out lines 40 to 44 in /lib/pkp/xml/schema/metadata.xml

Afterwards the install script runs smoothly

I suspect that the temporary column submissions.current_publication_id created a shift / an offset in the course of renaming submissions to publications. That caused a double entry in the index citations_publication_seq. I am not sure about the consequences, yet.

3

Hi @klausru,

Hmm, it sounds to me like this change is not working as expected: pkp/pkp-lib#4856 pkp/pkp-lib#5569 Make index drop depend on index exi… · pkp/pkp-lib@1037bce · GitHub

Can you try reversing that change, loading everything from backup from before you upgraded to 3.2.x, and running the upgrade again to confirm whether that prevents the error?

Regards,
Alec Smecher
Public Knowledge Project Team

4

Hi @asmecher
thanks for having a look at this. Your proposed change doesn’t do the trick. The error remains.

I am currently trying to learn how publication_id is constructed during the upgrade. I suspect there is a slight mistake in my database that causes the offset(s). Maybe you could explain that query.

Deleting the index from the citations table caused a mess: some publications were missing their citations and some might have been associated with the wrong submissions. But that confirmed my suspicion.

Best
Klaus

5

Hi @klausru,

Hmm, interesting. Can you check what indexes are on the citations table in your OJS table before the upgrade?

SHOW INDEXES FROM citations;

Regards,
Alec Smecher
Public Knowledge Project Team

6
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
citations 0 PRIMARY 1 citation_id A 6218 NULL NULL BTREE
citations 0 citations_submission_seq 1 submission_id A 388 NULL NULL BTREE
citations 0 citations_submission_seq 2 seq A 6218 NULL NULL BTREE
citations 1 citations_submission 1 submission_id A 388 NULL NULL BTREE
7

Hi @asmecher
It turned out that the data is there and it’s all fine.
I was confused because article_details.tpl now reads the references in a different way.
The situation is that I have bunch of submissions/publications that do have entries in the table citations but NULL in submissions.citations (old naming).
Those entries that are empty in submissions.citations get an empty entry in new: publication_setting.setting_name->citationsRaw
Those that used to be NULL in submissions.citations do not get this setting at all.
It turns out that in order to display references from citations the setting “citationsRaw” needs to be present and must not be empty.
Maybe that helps others too.

As for the index mentioned above: It was no problem to set the index citations_publication_seq by hand after the upgrade.

All the best
Klaus

8

Hi @klausru,

Can you describe how you ended up with citations entries that didn’t come from a corresponding citationsRaw? Was this something caused by the upgrade process?

Thanks,
Alec Smecher
Public Knowledge Project Team

9

Hi @asmecher
These orphaned pairs were already in the original database(s), so back in version 2.4.8 or in 3.1.1.
This is how I could compare.

Regards
Klaus

10

Hi all,

See Upgrade error: Duplicate entry 'xxx-yyy' for key 'citations_publication_seq' · Issue #5626 · pkp/pkp-lib · GitHub for a solution to this issue. The fix will be included in OJS 3.2.0-1, which we plan to release later this week.

Regards,
Alec Smecher
Public Knowledge Project Team