Error OJS Upgrade from 3.3.0.19 to 3.4.0.9

[Tiziano]

Hi at all, I had an error during upgrading 3.3.0.19 to 3.4.0.9
Errors occurred during installation:

2025-07-08 10:49:00 [error while downgrading “APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys”: SQLSTATE[HY000]: General error: 1553 Cannot drop index ‘review_form_responses_unique’: needed in a foreign key constraint (SQL: alter table review_form_responses drop index review_form_responses_unique)]
ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1005 Can’t create table confinia_db.publications (errno: 150 “Foreign key constraint is incorrectly formed”) (SQL: alter table publications add constraint publications_section_id foreign key (section_id) references sections (section_id) on delete set null)
[confinia_ftp@server site]$

Reading through the various similar threads they recommend checking that the tables are InnoDB, but in my case they are all already so.

Do you have any suggestions how to proceed?

Thanks for the support
Bye
Tiziano

[asmecher]

Hi @Tiziano,

After the upgrade fails, run the following queries:

SHOW CREATE TABLE sections;
SHOW CREATE TABLE publications;

These should help indicate the problem.

Regards,
Alec Smecher
Public Knowledge Project Team

[Tiziano]

Hi @asmecher ! Thanks for reply, I runned the query and this is the result:

How can I proceed?
Thanks

Bye
Tiziano

[asmecher]

Hi @Tiziano,

That’s only part of the information I need; follow these instructions to get the full output.

Thanks,
Alec Smecher
Public Knowledge Project Team

[Tiziano]

Hi @asmecher I followed your advice I’ll copy what it displayed to me:

Table
Create Table
sections
CREATE TABLE sections (
section_id bigint(20) NOT NULL AUTO_INCREMENT,
journal_id bigint(20) NOT NULL,
review_form_id bigint(20) DEFAULT NULL,
seq double NOT NULL DEFAULT 0,
editor_restricted tinyint(4) NOT NULL DEFAULT 0,
meta_indexed tinyint(4) NOT NULL DEFAULT 0,
meta_reviewed tinyint(4) NOT NULL DEFAULT 1,
abstracts_not_required tinyint(4) NOT NULL DEFAULT 0,
hide_title tinyint(4) NOT NULL DEFAULT 0,
hide_author tinyint(4) NOT NULL DEFAULT 0,
abstract_word_count bigint(20) DEFAULT NULL,
is_inactive smallint(6) NOT NULL DEFAULT 0,
PRIMARY KEY (section_id),
KEY sections_journal_id (journal_id),
KEY sections_review_form_id (review_form_id),
CONSTRAINT sections_journal_id FOREIGN KEY (journal_id) REFERENCES journals (journal_id) ON DELETE CASCADE,
CONSTRAINT sections_review_form_id FOREIGN KEY (review_form_id) REFERENCES review_forms (review_form_id) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=509 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

Table
Create Table
publications
CREATE TABLE publications (
publication_id bigint(20) NOT NULL AUTO_INCREMENT,
access_status bigint(20) DEFAULT 0,
date_published date DEFAULT NULL,
last_modified datetime DEFAULT NULL,
primary_contact_id bigint(20) DEFAULT NULL,
section_id bigint(20) NOT NULL DEFAULT 3,
seq double NOT NULL DEFAULT 0,
submission_id bigint(20) NOT NULL,
status tinyint(4) NOT NULL DEFAULT 1,
url_path varchar(64) DEFAULT NULL,
version bigint(20) DEFAULT NULL,
PRIMARY KEY (publication_id),
KEY publications_submission_id (submission_id),
KEY publications_section_id (section_id),
KEY publications_url_path (url_path)
) ENGINE=InnoDB AUTO_INCREMENT=20484 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

Thanks
Bye
Tiziano

[Tiziano]

Hi, any suggestion about the question?!

Thanks!
Bye
Tiziano

[Tiziano]

Hi, any suggestion about the question?!

Thanks!
Bye
Tiziano

[asmecher]

Hi @Tiziano,

It looks like there are some manual modifications to your database structure; in the publications table I see…

section_id bigint(20) NOT NULL DEFAULT 3,

…when it should be…

`section_id` bigint DEFAULT NULL,

This is probably related to the upgrade issue. Without knowing your installation’s history, or why the change was made, it’s hard for me to suggest specifics.

I would suggest also running the following query on your OJS 3.3.0 database to see how many publications are assigned to non-existent sections. There shouldn’t be any.

SELECT COUNT(*) FROM publications p LEFT JOIN sections s ON (p.section_id = s.section_id) WHERE s.section_id IS NULL;

Regards,
Alec Smecher
Public Knowledge Project Team

[Tiziano]

Hi @asmecher , Yes, after the running query, we found 5474 publications. How can I fix the database?

Bye
Tiziano

[asmecher]

Hi @Tiziano,

That’s a lot of missing section assignments! Every submission should be associated with a section. Perhaps at some point along the way someone deleted one of the sections that was heavily used?

In any case, you’ll need to work in the database directly to ensure all entries in publications point to an existing entry in sections via the section_id column.

You might look to see if there’s any hint of what nonexistent section IDs are used:

SELECT p.section_id, COUNT(*) FROM publications p LEFT JOIN sections s ON (p.section_id = s.section_id) WHERE s.section_id IS NULL GROUP BY p.section_id;

…then assign them to another section in batches:

UPDATE publications SET section_id = 123 WHERE section_id = 456;

…using the new section ID in place of 123, and the old (nonexistent) one in place of 456.

This will require knowledge of your submissions that I don’t have – but hopefully it’ll get you started.

As always, take a good backup before working in the database directly.

Regards,
Alec Smecher
Public Knowledge Project Team