Upgrade to OJS 3.3.0.6 foreign key constraint failure

Software Support
1

Hello, I have looked at the 4 other relevant threads on the forum and tried the recommendations and I think that I have a different issue. The closest threads are:

I am using OJS hosted by Kualo using softaculous. When I try to upgrade from OJS 3.2.1.1 to 3.3.0.6 I get this error:

ERROR: Upgrade failed: DB: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `submission_files` add constraint `submission_files_file_id_foreign` foreign key (`file_id`) references `files` (`file_id`))

Here’s what I suspect is happening:

  1. The OJS upgrade is run (either by the Softaculous upgrade button, or from the command line)
  2. The script creates a new table ‘files’
  3. Since the default table engine for mysql is pre-set to MyISAM by Kualo, files is created with as MyISAM
  4. The script fails with an error because the MyISAM engine cannot be mixed with the InnoDB engine for foreign key constraints (Upgrade to 3.3: default database engine doesn't support foreign key constraint · Issue #6732 · pkp/pkp-lib · GitHub)

Problem is that Kualo cannot/will not change the mysql engine default to InnoDB due to others on the same infrastructure.

Therefore, I would like to follow the advice in the github issue It seems that forcing to use InnoDB engine by passing additional setting to a database connection ('engine' => 'InnoDB') solves the issue.

Please can someone explain the best way to pass this setting as part of the OJS database connection? Can it be set in config.inc.php?

~Steve

PS. There is nothing Fatal / obvious in the error_log, which contains:

[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:43 UTC] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 2 with submission_id 1
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 3 with submission_id 1
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 4 with submission_id 1
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 6 with submission_id 2
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 8 with submission_id 3
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 9 with submission_id 3
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 10 with submission_id 3
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 22 with submission_id 11
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 23 with submission_id 11
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 24 with submission_id 11
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 25 with submission_id 11
[13-May-2021 09:11:56 UTC] Removing orphaned submission_files entry ID 1950 with submission_id 1955```
2

Hi folks - just to communicate that my posting provider Kualo has agreed to change the default MySQL table engine to InnoDB - and we have altered the MyISAM tables accordingly. Now the ./tools/upgrade.php upgrade script and web UI upgrade form work fine. (No need for memory limit to be increased)

3

Hi,
The problem here is the storage Engine of the table “files” that will be created during upgrade.
The solution is to set the Defaullt Engine of Mysql or other SGBD to InnoDb (the storage engine recommanded for OJS) instead of MYISAM, before proceeding with the UPGRADE process.
I had the same problem when I upgraded from 3.2.1-1 to 3.3.0-6.
Good Luck

4

Thank you azzedine - this fixed my problem (but I had to get my hosting provider to change the default)…

5

I’m attempting to upgrade from 3.2.0.1 to 3.3.0.8 and have had the same issue with some of my tables being MYISAM while most of them were InnoDB. I modfied all of the MYISAM tables in our production database and then copied the entire DB into my separate clone to be used for upgrading. Alan Smecher had stated elsewhere that if you run into an error during the upgrade process, you need recopy the entire DB and start over from the beginning, which is what I did. But when I restarted to upgrade process, but I still get the same error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘url’ in ‘field list’ (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)
Any idea of what I should do about this?

Please see my more detailed posting here.

6

Hi @trobb49,

Would you mind creating a new post describing your issue? Feel free to link back to this post, as it is still relevant This way we can bring it to the attention of our team who can potentially help you troubleshoot this issue.

Best regards,

Roger
PKP Team