Error: duplicate key value violates unique constraint "submission_settings_pkey"

Software Support
1

Error: duplicate key value violates unique constraint “submission_settings_pkey”

I 'm upgrading OMP from version 3.1.2.4 to version 3.2.1.1 and got the following Error:

PHP Fatal error:  Uncaught Exception: DB Error: ERROR:  duplicate key value violates unique constraint "submission_settings_pkey"

Could you help me?

2

Are you attempting to perform an upgrade on a database that was already partially upgraded?
If so, you’ll need to restore from backup to the time before the upgrade was first attempted.

Cheers,
m.

3

The original version is 3.1.1.1, my idea is to update to the latest version 3.3.0.13. I tried to update directly to the latest version but many errors appeared.
I decided to update from version 3.1.1.1 to 3.1.2.4, I got several errors in the process that I was able to solve and now I have the OMP in version 3.1.2.4.

What recommendation do you give me to update to the latest version (3.3.0.13)?

Thanks.-

4

Hello, that problem may be due to the fact that your storage in your database manager (assuming you use MySQL), is MyISAM, in order to upgrade to OMP 3.2. X or higher, you will have to change the storage type from MyISAM to InnoDB first. Before you will have to reimport all the OMP installation (OMP, files, database) to later change the storage to InnoDB. I leave you a link with more information about it:

https://kinsta.com/knowledgebase/convert-myisam-to-innodb/

Cheers,
i.

5

Thanks for the reply @ Isai_Campos

Didn’t mention it before, but I’m using postgres 9.6.22 on Rocky Linux 8.7
Would what you mention also apply to postgres?

Regards.-

6

I understand, try to update from version 3.1.1.1 to 3.1.2.4, but to OJS version 3.1.2.4 apply this patch:

https://github.com/pkp/pkp-lib/commit/7e33ad63f2ee4c2b3063923d70a36e3fab23f07e

7

I understand, try to update from version 3.1.1.1 to 3.1.2.4,

I’m already in OMP version 3.1.2.4.
I want to advance to reach version 3.3.0.13.
Now, I’m trying to update from version 3.1.2.4 to 3.2.0.3 but I have the following error:

ERROR:  syntax error at or near "email_templates_assoc"

Any idea?

8

Try to upgrade from the version you have (3.1.2.4) to version 3.2.1.4 (the latest version of OJS 3.2), remember to make a backup of the version you managed to upgrade (in this case it is 3.1.2.4).

9

Hello again,

I am upgrading from version 3.1.2.4 to 3.2.1.4 as you suggested @Isai_Campos and now I have the following error:

ERROR:  column "publication_id" contains null values

How can it be solved?

Thanks for the collaboration.-

10

Try this:

11

following your recommendation @Isai_Campos, I tried the following:

SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
 citation_id
-------------
(0 rows)

When I try to update the log it shows the following:

[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): CREATE TABLE publications (
publication_id           SERIAL,
date_published           DATE,
last_modified            TIMESTAMP,
locale                   VARCHAR(14),
primary_contact_id       INT8,
publication_date_type    VARCHAR(32) DEFAULT 'pub',
publication_type         VARCHAR(32) DEFAULT 'publication',
seq                      FLOAT8 DEFAULT 0 NOT NULL,
series_id                INT8,
series_position          VARCHAR(255),
submission_id            INT8 NOT NULL,
status                   SMALLINT DEFAULT 1 NOT NULL,
url_path                 VARCHAR(64),
version                  INT8,
                 PRIMARY KEY (publication_id)
)  


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): CREATE INDEX publications_submission_id ON publications (submission_id)  


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): CREATE INDEX publications_section_id ON publications (series_id)  


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): DROP INDEX publication_format_submission_id  


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): ALTER TABLE publication_formats ALTER COLUMN publication_format_id TYPE INT8   


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): ALTER TABLE publication_formats ALTER COLUMN publication_format_id SET NOT NULL  


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): ALTER TABLE publication_formats ADD COLUMN publication_id INT8   


[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834:
(postgres8): ALTER TABLE publication_formats ALTER COLUMN publication_id SET NOT NULL  


[30-Nov-2022 18:38:54 UTC] PHP Warning:  pg_query(): Query failed: ERROR:  column "publication_id" contains null values in /var/www/html/omp-3.2.1-4/lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-postgres7.inc.php on line 259
[30-Nov-2022 18:38:54 UTC] PKP-Database-Logger 1669833530,834: -1: ERROR:  column "publication_id" contains null values
[30-Nov-2022 18:38:54 UTC] ERROR:  column "publication_id" contains null values

Do you have any idea?

12

Try this query:

SELECT COUNT(*) FROM authors a LEFT JOIN submissions s ON (a.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
13

Hello @Isai_Campos,

I tried:

SELECT COUNT(*) FROM authors a LEFT JOIN submissions s ON (a.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
 count
-------
     0
(1 row)