ERROR: Upgrade failed: DB: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'url' in 'field list'

Software Support
1

Hello Sir
During upgrading of ojs 3.3.0.2 from 3.2.1.3 i am getting following error:
ERROR: Upgrade failed: DB: 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) [canadia8@nyc007 tools]$ ERROR: Upgrade failed: DB: 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)
Please help to solve the issue…
Thanks
Regards
Faisal Khan

2

Hi @cosmosscholars,

This is probably caused by a second attempt to run the upgrade script after a first attempt has failed. If an upgrade attempt fails, you’ll need to restore your database from backup before trying again. Note that OJS 3.3.0-3 has just been released, so I’d recommend working with that when you re-run your upgrade.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
3

Hi
Thanks really sir for your reply, i have at last upgraded my version of OJS from 3.2.1-3 to 3.3.0.3. As firstly i was getting “Upgrade failed: DB: SQLSTATE[42S22]:” error and i was attempting to upgrade it from CLI. As i was searching the forum regarding to this threat, i found a very useful post

Serious issue with Database upgrade (failed from 3.2.1.3 to 3.3.0.2) ", it helped me a lot to resolve this issue, i applied the this patch pkp/pkp-lib#6710 Unserialize journal settings depending on a value data type by Vitaliy-1 · Pull Request #6715 · pkp/pkp-lib · GitHub. from the mentioned post…

after applying the patch, i re-run the command of upgrading from my CLI Client that is Putty. and i successfully upgraded to new version.
After upgrading to 3.3.0.2 , i once again repeated the upgrading procedure as per pkp.sfu.ca instruction and also “Asmecher” helped me regarding to the upgrade process by recommending me the re-upgrading by 3.3.0.3.
And by whole one day activity at last but not least , i saw in my command line program, that…
Successfully upgraded to version 3.3.0.3.
Thanks for every one, who helped me…
Regards
Faisal

6

Hi,
I have encountered the same error when upgrading from 3.2.1.1 to 3.3.0.11.
I get the same error:
A database error has occurred: 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)

I have tried, the solutions posted above but to no avail.

I have also tried adding:
‘engine’ => ‘InnoDB’ to the PKPApplication file.

Any help would be much appreciated.

UPDATED:
I also got this from the PHP Log
[16-Aug-2022 14:35:24] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:24] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:24] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:24] Removing orphaned submission_files entry ID 93 with submission_id 20
[16-Aug-2022 14:35:27] SQLSTATE[HY000]: General error: 1005 Can’t create table s_ojs.submission_files (errno: 150 “Foreign key constraint is incorrectly formed”) (SQL: alter table submission_files add constraint submission_files_file_id_foreign foreign key (file_id) references files (file_id))
[16-Aug-2022 14:35:44] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:44] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:44] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[16-Aug-2022 14:35:44] 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)
[16-Aug-2022 14:35:47] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu

Question should I try
SELECT DISTINCT rf.file_id FROM review_files rf LEFT JOIN submission_files sf ON (rf.file_id = sf.file_id) WHERE sf.file_id IS NULL;

And then:
DELETE FROM review_files WHERE file_id IN (16827, 24889, 28635);

Thoughts?

Thanks in advance!

7

@asmecher, what would be the best way to go about restoring the a database. I have the SQL backup file.

Thanks in advance!

8

Hi again,

One other avenue I explored:

  1. create an empty database in phpMyAdmin
  2. Upload the backup of the database
  3. Change config.inc.php to reflect the new database
  4. Try the upgrade
  5. HTTP ERROR 500 Log error: PHP Fatal error: Cannot declare class PKPv3_3_0UpgradeMigration, because the name is already in use in /home/folder/public_html/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php on line 0
  6. Try command line upgrade. No effect

Any other avenues I can try?

UPDATED:
Ok, I seem to have got the OJS version updated.
My approach

  1. create an empty database in phpMyAdmin
  2. Upload the backup of the database
  3. Change config.inc.php to reflect the new database
  4. Copy the an older version of the OJSv3_3_0UpgradeMigration.inc.php from before putting in the fix
  5. Copy OJSv3_3_0UpgradeMigration.inc.php to the folder classes/migration/upgrade
  6. Try the upgrade.
  7. Works

However, the sidebar in the Dashboard seems a bit odd. The submenus are now listed and not in the form of a flyout.
Screen Shot 2022-08-16 at 21.14.41

Also submissions are no longer clickable via the title.

Any ideas?

Thanks!

Thanks!