OJS Upgrade from 3.3 to 3.4 : upgrade failed ("General error - Can't create table")

Software Support
,
1

I’m currently trying to upgrade from OJS 3.3.0-20 to 3.4.0-8, but the database upgrade fails. I’ve tried a few times, starting each time with a fresh copy of the database, but the process always stops at the same point.

I browsed around the forum and found vaguely similar upgrade problems, so I went ahead and made sure all my tables were running with the InnoDB engine, and that the collation was set to utf8_general_ci for all my tables and columns (some where initially utf8_unicode_ci). My config.inc.php is also specifying that collation:

; Database collation
collation = utf8_general_ci

Our stack has been made compliant to 3.4 requirements months ago, including MariaDB:

mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1

The trace goes as follow (somes line and values were removed for clarity):

2025-05-07 13:38:59 [pre-install]
2025-05-07 13:38:59 [load: upgrade.xml]
2025-05-07 13:38:59 [version: 3.4.0.8]
2025-05-07 13:38:59 [code: Installer Installer::checkPhpVersion]
2025-05-07 13:38:59 [code: Installer Installer::installDefaultNavigationMenus]

WARNING: The NavigationMenu (ContextId: 25, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
(... several more lines like that ...)

2025-05-07 13:39:00 [code: Installer Installer::migrateStaticPagesToNavigationMenuItems]
2025-05-07 13:39:00 [migration: PKP\migration\upgrade\v3_4_0\I10249_FixProfileImageDataLoss]
2025-05-07 13:39:00 [migration: APP\migration\upgrade\v3_4_0\PreflightCheckMigration]

2025-05-07 13:39:05 [Removing orphaned entries from "submissions" with an invalid value for the required column "current_publication_id". The following IDs do not exist at the reference table "publications": ]
2025-05-07 13:39:05 [2 entries removed]
2025-05-07 13:39:05 [Cleaning orphaned entries from "submission_files" with an invalid value for the column "uploader_user_id". The following IDs do not exist at the reference table "users" and will be reset to NULL: (...)]
2025-05-07 13:39:05 [4 entries updated]
(... several more lines like that ...)

2025-05-07 13:39:05 [The publication ID (437) for the submission ID 470 is assigned to an invalid issue ID "0", its value will be updated to NULL]
(... several more lines like that ...)

2025-05-07 13:39:06 [Removing orphaned entries from "stage_assignments" with an invalid value for the required column "user_id". The following IDs do not exist at the reference table "users":(...)]
2025-05-07 13:39:06 [6 entries removed]
(... several more lines like that ...)

2025-05-07 13:39:09 [migration: PKP\migration\upgrade\v3_4_0\I8060_UpdateUserLocalesDefaultToEmptyArrayFromNull]
2025-05-07 13:39:09 [migration: PKP\migration\upgrade\v3_4_0\I7245_UpdateUserLocaleStringToParsableJsonString]
2025-05-07 13:39:14 [migration: APP\migration\upgrade\v3_4_0\I7129_IssueEntityDAORefactor]
2025-05-07 13:39:14 [migration: APP\migration\upgrade\v3_4_0\I6091_AddFilterNamespaces]
2025-05-07 13:39:14 [migration: APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys]
2025-05-07 13:40:00 [migration: APP\migration\upgrade\v3_4_0\I6093_AddForeignKeys]
2025-05-07 13:40:17 [revert migration: APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys]
2025-05-07 13:41:12 [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 `ojs_dev`.`#sql-3b90_b1a73` (errno: 121 "Duplicate key on write or update") (SQL: alter table `submission_search_object_keywords` add constraint `submission_search_object_keywords_object_id_foreign` foreign key (`object_id`) references `submission_search_objects` (`object_id`) on delete cascade)

I don’t know from which angle I could try and crack that nut. Any hint for me? Am I missing some obvious point?

2

Hi @nicolasd,

There appears to be an index collision in your article search index. I would suggest…

  1. Before upgrading, empty the search index using these SQL statements:
    DELETE FROM submission_search_object_keywords;
DELETE FROM submission_search_objects;
DELETE FROM submission_search_keyword_list;
  2. Run the upgrade
  3. Re-generate the search index using:
    php tools/rebuildSearchIndex.php

Regards,
Alec Smecher
Public Knowledge Project Team

4

Hi @asmecher,

I flashed the database and ran the three DELETE statements before running the upgrade again, but the result remains the same:

2025-05-08 12:14:23 [revert migration: APP\migration\upgrade\v3_4_0\I3573_AddPrimaryKeys]
2025-05-08 12:14:48 [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 `ojs_dev`.`#sql-3b90_160b7d` (errno: 121 "Duplicate key on write or update") (SQL: alter table `submission_search_object_keywords` add constraint `submission_search_object_keywords_object_id_foreign` foreign key (`object_id`) references `submission_search_objects` (`object_id`) on delete cascade)

Just in case, I also tried to rebuild the index before upgrading (using the 3.3. script), but it made no difference…

5

The problem lies with the database itself. I’ve read a few posts on other forums, and some people are talking about cached indexes that remains even after tables are dropped, and how they had to drop the entire database to solve the problem, so I’ve tried to use another dev database we have – and the upgrade works.

So now I have to figure what the problem is with that database…

1 Like
6

This topic was automatically closed after 11 days. New replies are no longer allowed.