Pkp-lib: Upgrade error: Duplicate entry 'xxx-yyy' for key 'citations_publication_seq'

[asmecher]

During upgrade to OJS 3.2.0-0, users may receive the following database error:

Upgrade error: Duplicate entry 'xxx-yyy' for key 'citations_publication_seq'

Reported here:

• https://forum.pkp.sfu.ca/t/ojs3-2-upgrade-error-please-help/58977
• https://forum.pkp.sfu.ca/t/upgrade-database-failed-ojs-3-1-2-4-to-3-2/59250

[asmecher]

To fix this, apply https://github.com/pkp/ojs/commit/47ea8dbd229829de10c7b391a72d4220776b137e to your installation before running the upgrade script. Note that you must restore your database from backup before re-running a failed upgrade.

[rogersantosferreira]

Hi!
I'm trying to update from version 2.4.2 to 3.2.0-1 and still receiving this same error.
Can you help me?

[asmecher]

@rogersantosferreira, just to confirm, are you using MySQL or PostgreSQL?

[asmecher]

...and @rogersantosferreira, what is the exact error message you're getting?

[jpcv222]

Hi @asmecher,
I am trying to upgrade from version 3.1.1-4 to 3.2.0. I am getting

ERROR: Upgrade failes: DB: ERROR: duplicate key value violates unique constraint "citations_publication_seq" DETAIL: Key (publication_id, seq)=(xx, yy) already exists.

I can not apply https://github.com/pkp/ojs/commit/47ea8dbd229829de10c7b391a72d4220776b137e because I am using PostgreSQL.

[asmecher]

@jpcv222, can you check this query? https://github.com/pkp/pkp-lib/issues/5583#issuecomment-601397764

[jpcv222]

@asmecher, the query from https://github.com/pkp/pkp-lib/issues/5583#issuecomment-601397764 returns 0 rows for me.

[adrianojam]

@jpcv222 , the same mistake happens to me. The query from #5583 (comment) returns 0 rows for me too. I use postgres7. Upgrade ojs-3.1.2-4 to 3.2.0-2.

[asmecher]

@adrianojam and @jpcv222, I've filed a new issue for the PostgreSQL fix over at https://github.com/pkp/pkp-lib/issues/5795; please watch there for a proposed patch, and if you're able to try it out, your confirmation would be very valuable!

[jpcv222]

@asmecher and @adrianojam, the patch in https://github.com/pkp/ojs/pull/2722/commits/bbc7686ac330a28c8e8715f2264ce62a47d2a03b fix the issue https://github.com/pkp/pkp-lib/issues/5795 on upgrade with PostgreSQL setup. Thanks!

[lucasdiedrich]

@asmecher i'm having this issue upgrading from 3.1.2.4 to 3.2.0.3.
Running your querie did not find anything:

Citations debug error:

PKP-Database-Logger 1588961855.7547: Query:
                        UPDATE citations as c
                        SET c.publication_id = (
                                SELECT s.current_publication_id
                                FROM submissions as s
                                LEFT JOIN temp_citations as tc
                                        ON s.submission_id = tc.submission_id
                                WHERE c.citation_id = tc.citation_id
                        ) ORDER BY c.publication_id ASC
                 failed. Duplicate entry '217-1' for key 'citations_publication_seq'
PKP-Database-Logger 1588961855.7547: 1062: Duplicate entry '217-1' for key 'citations_publication_seq'
                                                        ADOConnection._Execute(                         UPDATE citations as c                   SET c.publication_id = (         SELECT s.current_publication_id                          FROM submissions as s                           LEFT JOI..., false)% line 1246, file: /var/www/html/lib/pkp/lib/vendor/adodb/adodb-php/adodb.inc.php
                                                ADOConnection.Execute(                  UPDATE citations as c                   SET c.publication_id = (                         SELECT s.current_publication_id                          FROM submissions as s                           LEFT JOI...)% line  442, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
                                        Installer.executeSQL(                   UPDATE citations as c                   SET c.publication_id = (                                SELECT s.current_publication_id                           FROM submissions as s                           LEFT JOI...)% line  437, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
                                Installer.executeSQL(Array[30])% line  395, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
                        Installer.executeAction(Array[3])% line  263, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: Duplicate entry '217-1' for key 'citations_publication_seq'
PKP-Database-Logger 1588961855.7547: -----

How to proceed? thanks.

[asmecher]

@lucasdiedrich, another solution would be to execute the following SQL query before running the upgrade:

DELETE FROM citations;

Then, after the upgrade completes, re-generate that table's contents using:

php lib/pkp/tools/parseCitations.php all

[lucasdiedrich]

YEP! it worked, thanks a lot!

[sworld-guy]

During upgrade from OJS 3.1.2.4 to OJS 3.2.1.2 I receive the following database error:
Upgrade error: Duplicate entry '0-1' for key 'citations_publication_seq'
Linux, 7.2.34 Apache/2.4.6 (CentOS) OpenSSL/1.0.2k-fips mod_fcgid/2.3.9 PHP/5.4.16 mysqli 5.5.68-MariaDB

Explain step by step what I should do to update our scientific journal. Something I didn't succeed.

with respect
Ph.D. Kuprienko Sergey

[asmecher]

@sworld-guy, see: https://github.com/pkp/pkp-lib/issues/5626#issuecomment-625953927

[sworld-guy]

You mean this message:
DELETE FROM citations;
Then, after the upgrade completes, re-generate that table's contents using:
php lib/pkp/tools/parseCitations.php all

I tried clean citations. Then I updated the site. Then I loaded citations in phpmyadmin (sql). After that, no one article was opened.
My attempts failed

To upgrade I use web.
I can DELETE FROM citations in phpmyadmin.
How do I do this:
php lib/pkp/tools/parseCitations.php all

Explain step by step with example, please

Help a newbie in this matter

[asmecher]

The command:

php lib/pkp/tools/parseCitations.php all

...needs to be run from a command line, e.g. by connecting to your server via SSH.

[sworld-guy]

Something went wrong again

[asmecher]

@sworld-guy, could you continue this conversation via a post on our support forum (https://forum.pkp.sfu.ca)? That's the best place to get general help.

[sworld-guy]

No help from you. I reread other messages - no one answered normally. Promised to fix it in 3.2.01. nothing has been fixed

[asmecher]

Conversation continues: https://forum.pkp.sfu.ca/t/upgrade-from-ojs-3-1-2-4-to-ojs-3-2-1-2-error-duplicate-entry-0-1-for-key-citations-publication-seq/64349