OJS 3.3.0.3 upgrade failing SQLSTATE(42S22)

Hello again,

Thanks to a bevy of information from the community and developers, I was able to solve my most recent problem and feature request from a faculty member we’re working with.

I have been following along the guide information when attempting the upgrade to the most recent version of OJS that’s in the main branch (including doing the upgrade through the command line rather than the web interface, which doesn’t seem like it’ll be fixed until the next update). Once I updated my installation, which is now on PHP 7.3 and the most recent version of OJS, I’m met with a landing page to my installation that won’t allow me to do anything. The issue that I’m seeing in the PHP error.log file reads as follows:

[01-Mar-2021 18:30:05 UTC] PHP Deprecated: Non-static method PKPApplication::getRequest() should not be called statically in …ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 135
[01-Mar-2021 18:30:05 UTC] PHP Warning: Use of undefined constant CDN_JQUERY_VERSION - assumed ‘CDN_JQUERY_VERSION’ (this will throw an Error in a future version of PHP) in …ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 152
[01-Mar-2021 18:30:05 UTC] PHP Warning: Use of undefined constant CDN_JQUERY_UI_VERSION - assumed ‘CDN_JQUERY_UI_VERSION’ (this will throw an Error in a future version of PHP) in …/ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 153

I cannot access the administrator interface to clear out any cache or access anything to move past this. I’m unsure what to do now (my upgrade went successfully and my backup that I had created on AWS is now gone, much to my chagrin…)

Any help would be appreciated @asmecher or anyone else.

Thank you.

To give slightly more context at the changes that I’m looking at, I can see that in the previous installation that I had for OJS, the file that is being referenced on line 152 from the old installation reads

  $request = Application::getRequest();

While the newer file on the current installation doesn’t have that line on that line at all, it’s on line 145 on the same file.

I tried to walk back some of the changes and updates I worked with in order to trace where I might have messed up but I’m not well enough versed in PHP and OJS in order to figure out what I might need to do.

Oh now I’m getting somewhere!
I cleaned out the entire cache folder on the OJS installation folder and now my error log is looking like it’s making more sense on what I need to be looking for.

[01-Mar-2021 19:08:43 UTC] ojs2: 404 Not Found
[01-Mar-2021 19:22:42 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘g.submission_file_id’ in ‘on clause’ in …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78
Stack trace:
#0 …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(78): PDO->prepare(‘SELECT sf., g…’, Array)
#1 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(361): Doctrine\DBAL\Driver\PDOConnection->prepare(Object(Illuminate\Database\Query\Expression))
#2 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\Query\Expression), Array)
#3 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCa in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
[01-Mar-2021 19:37:31 UTC] PHP Fatal error: Uncaught Error: Call to a member function getData() on null in …/ojs/plugins/generic/htmlArticleGalley/HtmlArticleGalleyPlugin.inc.php:70
Stack trace:
#0 …/ojs/lib/pkp/classes/plugins/HookRegistry.inc.php(107): HtmlArticleGalleyPlugin->articleViewCallback(‘ArticleHandler:…’, Array)
#1 …/ojs/pages/article/ArticleHandler.inc.php(311): HookRegistry::call(‘ArticleHandler:…’, Array)
#2 …/ojs/lib/pkp/classes/core/PKPRouter.inc.php(395): ArticleHandler->view(Array, Object(Request))
#3 …/ojs/lib/pkp/classes/core/PKPPageRouter.inc.php(246): PKPRouter->_authorizeInitializeAndCallRequest(Array, Object(Request), Array, false)
#4 …/ojs/lib/pkp/classes/core/Dispatcher.inc.php(144): PKPPageRouter->route(Object(Request))
#5 …/ojs/lib/pkp/classes/core/PKPApplication.inc.php(365): Dispatcher->dispatch(Object(R in …/ojs/plugins/generic/htmlArticleGalley/HtmlArticleGalleyPlugin.inc.php on line 70
[01-Mar-2021 19:41:21 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘g.submission_file_id’ in ‘on clause’ in …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78
Stack trace:
#0 …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(78): PDO->prepare('SELECT sf.
, g…’, Array)
#1 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(361): Doctrine\DBAL\Driver\PDOConnection->prepare(Object(Illuminate\Database\Query\Expression))
#2 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\Query\Expression), Array)
#3 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCa in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
[01-Mar-2021 19:42:27 UTC] ojs2: 404 Not Found

Now that I know it’s something I’m not familiar with at all, I feel better (oddly). I’m not familiar at all with the database running it all, which I can access on the phpAdmin page, but don’t know much about.

Any advice would be welcome!

EDIT 2021-03-01T06:00:00Z
When I turn the installation to installed = off, I can’t even access the Install webpage. Maybe it didn’t update the database correctly when I ran the script on the server.

EDIT 2 2021-03-01T06:00:00Z
The update did in fact fail when I attempted to run it but I didn’t get the script as to why it failed until now when I attempted it again.

[pre-install]
[load: upgrade.xml]
[version: 3.3.0.3]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::installDefaultNavigationMenus]

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]
[migration: PKPv3_3_0UpgradeMigration]
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)

I’m not good at anything with SQL so all help would be appreciated.

I’ve tried for a while now trying to figure out what the deal is. I went into the PHPmyAdmin and checked all my datatypes to make sure they were all correctly set to InnoDB with the right collation and they are. I’ve also created the backup of the DB from the production SQL database and attempted to follow this particular Forum Post in order to try and fix my issue - Problem with upgrading to OJS 3.3.0.3 - #11 by potravinarstvo

Nothing seems to be addressing my issue. Not sure what my next step is @asmecher

I was able to get the site up (Journals @UD) however I’m now back to the original issue I was encountering, which is that there’s no theme being applied. The console shows when I attempt to access the site the following:

The resource from “Journals @UD was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff). ojs
The resource from “Journals @UD was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff). ojs
Loading failed for the with source “Journals @UD. ojs:257:1
The resource from “Journals @UD was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff). ojs
Loading failed for the with source “Journals @UD. ojs:257:1
Uncaught ReferenceError: jQuery is not defined
jQuery
jquery.tag-it.js:590:1
Uncaught ReferenceError: jQuery is not defined
https://digitalud.dbq.edu/ojs/plugins/themes/default/js/lib/bootstrap/util.js?v=3.2.1.1:151
util.js:151:1
Uncaught ReferenceError: jQuery is not defined
https://digitalud.dbq.edu/ojs/plugins/themes/default/js/lib/bootstrap/dropdown.js?v=3.2.1.1:430
dropdown.js:430:1
Uncaught ReferenceError: jQuery is not defined
https://digitalud.dbq.edu/ojs/plugins/themes/default/js/main.js?v=3.2.1.1:198
main.js:198:1
GEThttps://digitalud.dbq.edu/favicon.ico
[HTTP/1.1 404 Not Found 0ms]

Source map error: Error: JSON.parse: unexpected character at line 1 column 1 of the JSON data
Resource URL: https://digitalud.dbq.edu/ojs/plugins/themes/default/js/lib/popper/popper.js?v=3.2.1.1
Source Map URL: popper.js.map
Source map error: Error: JSON.parse: unexpected character at line 1 column 1 of the JSON data
Resource URL: https://digitalud.dbq.edu/ojs/plugins/themes/default/js/lib/bootstrap/dropdown.js?v=3.2.1.1
Source Map URL: dropdown.js.map 2
Source map error: Error: JSON.parse: unexpected character at line 1 column 1 of the JSON data
Resource URL: https://digitalud.dbq.edu/ojs/plugins/themes/default/js/lib/bootstrap/util.js?v=3.2.1.1
Source Map URL: util.js.map 2

Things aren’t looking very good - I think I’ve reached the max of my abilities to figure things out without some assistance @asmecher

EDIT 2021-03-01T06:00:00Z

I was able to get the console JSON errors fixed (the themes weren’t updated correctly) and now I’m stuck with just the following errors on the main landing page with no recourse.

The resource from “Journals @UD was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff). login
The resource from “Journals @UD was blocked due to MIME type (“text/html”) mismatch (X-Content-Type-Options: nosniff). login
GEThttps://digitalud.dbq.edu/favicon.ico
[HTTP/1.1 404 Not Found 0ms]

Whatever help someone could provide I’d appreciate.

Thank you.

Based on your description, I think this is caused by the failed upgrade.
The symptoms such as never-ending loading on some of the tab on the admin page, and the JSON on that request that show some error json format.

That makes sense. Since the backup I created of the OJS database is gone now (due to an AWS rule that was created) I either have the option of waiting for the next update to fix things or rebuild it from a brand new install and starting from scratch.

@asmecher does that sound about right?

Hi @charlescmyers,

This message…

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)

…arises from an attempt to run the upgrade script on an installation that’s already had an upgrade partially run.

Working with a backup taken before the first upgrade attempt failed is definitely the best way to recover. If that’s not possible, do you know what the error message was when you ran into your first upgrade failure?

Regards,
Alec Smecher
Public Knowledge Project Team

Hello,

Unfortunately, when I attempted to run the upgrade script from the CLI, I didn’t get a message indicating anything had failed. Normally, I process the upgrade through the web interface but after applying all the patches listed from other posts regarding the same issue I was running into, I still couldn’t get the web interface to show up.

Without a backup, what options do I have?

Thank you.

Hi @charlescmyers,

Was there anything captured in the PHP error log at the time of the failed upgrade?

The key to recovering this easily will be knowing what state your database is in – finding out what step of the upgrade failed the first time, and knowing what’s been done to it since then (i.e. subsequent attempts to upgrade or other interventions).

Regards,
Alec Smecher
Public Knowledge Project Team

From the date of the upgrade, here’s what I found in the PHP error log:

[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:14 UTC] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[25-Feb-2021 19:40:15 UTC] Removing orphaned submission_files entry ID 188 with submission_id 65
[25-Feb-2021 19:40:15 UTC] Removing orphaned submission_files entry ID 189 with submission_id 65
[25-Feb-2021 19:40:15 UTC] Removing orphaned submission_files entry ID 207 with submission_id 80
[25-Feb-2021 19:40:15 UTC] Removing orphaned submission_files entry ID 211 with submission_id 81
[25-Feb-2021 19:40:15 UTC] Removing orphaned submission_files entry ID 212 with submission_id 89
[25-Feb-2021 19:40:15 UTC] A submission file was expected but not found at journals/1/articles/1/submission/1-13-1-1-2-20160227.pdf.
[25-Feb-2021 19:40:15 UTC] A submission file was expected but not found at journals/1/articles/1/submission/copyedit/1-13-13-1-9-20160227.pdf.
[25-Feb-2021 19:40:17 UTC] PHP Fatal error: Cannot declare class PKPv3_3_0UpgradeMigration, because the name is already in use in …/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php on line 655
[25-Feb-2021 19:40:23 UTC] PHP Fatal error: Uncaught Error: Call to a member function getConnection() on null in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php:74

There was also this information from the same date:

[25-Feb-2021 19:40:41 UTC] PHP Deprecated: Non-static method PKPApplication::getRequest() should not be called statically in …/ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 135
[25-Feb-2021 19:40:41 UTC] PHP Warning: Use of undefined constant CDN_JQUERY_VERSION - assumed ‘CDN_JQUERY_VERSION’ (this will throw an Error in a future version of PHP) in …/ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 152
[25-Feb-2021 19:40:41 UTC] PHP Warning: Use of undefined constant CDN_JQUERY_UI_VERSION - assumed ‘CDN_JQUERY_UI_VERSION’ (this will throw an Error in a future version of PHP) in …/ojs/plugins/themes/default/DefaultThemePlugin.inc.php on line 153
[25-Feb-2021 19:40:41 UTC] PHP Fatal error: Uncaught Error: Call to undefined function mb_split() in …/ojs/lib/pkp/lib/vendor/smarty/smarty/libs/plugins/shared.mb_str_replace.php:47

Later on I saw some errors about session destroy not working and then this information:

[01-Mar-2021 19:22:42 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘g.submission_file_id’ in ‘on clause’ in …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78

I was hoping that I could workout what the issue was, maybe rebuild the database, sometime soon. I’m starting to get messages from the respective journal owners about the site being down.

Honestly, my next step if I can’t figure out how to undue my mistakes and try to fix things, is to setup an entirely new OJS database ( from the following link - Create a MySQL or PostgreSQL OJS user and database. The command we use is as follows…) and just start from scratch. I inherited this OJS installation on 2.4.8 and the updates/upgrades are all locally installed manually without using any git commands so if that’s an easier (relatively speaking…) I’m willing to spend hours doing that rather than attempting to salvage what I already seemed to have destroyed.

Please advise.
Thanks.

Hi @charlescmyers,

I’m confused about this first fatal error message –

[25-Feb-2021 19:40:17 UTC] PHP Fatal error: Cannot declare class PKPv3_3_0UpgradeMigration, because the name is already in use in …/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php on line 655

Is it possible that the contents of lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php were somehow copied into classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php? I can’t think of any other way this would have happened.

Do you know if this was the first attempt to run the upgrade?

Regards,
Alec Smecher
Public Knowledge Project Team

Yes, that was the first attempt to run the upgrade.

I’m not sure if they were copied over into the wrong file. I’m looking for the github issue that I originally followed with the patch to fix the access to the web install interface to see if that might have happened.

EDIT

Here’s the link I’m referring to - Unable to upgrade 3.3.0.2: Call to a member function getConnection() on null · Issue #6703 · pkp/pkp-lib · GitHub

That was one of the original issues that I was running into when attempting the upgrade.

Hi @charlescmyers,

That issue is fixed with this change:

I don’t think it would explain the error message you quoted.

If my suspicion about the file being incorrectly copied is correct, then your database would at that point have executed lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php but not classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php – but confirmation on this would be very helpful (i.e. check the file’s contents, if you’re not sure it’s correct, and any recollections about changes made to that file during your upgrade attempts). What happened to the database after that?

Regards,
Alec Smecher
Public Knowledge Project Team

You were correct, the OJSv3 file and the PKPv3 file were the same. I definitely remember making sure to update the PKP file and I must have mistakenly accessed the OJS file without even realizing the mistake. I have fixed that right now.

Once I ran that upgrade with the CLI command, it didn’t spit out an error the first time. That’s probably where the original database error stems from. Only when I couldn’t access the site post running the script did I attempt to look through the logs and notice the issues I ran into which are posted above.

Hi @charlescmyers,

OK, the next thing to do is make a complete backup of the database and files – if possible get in a time machine first, but if that’s not possible, now is the second best option.

Restore the OJSv3_3_0UpgradeMigration.inc.php file to the contents it should have from the .tar.gz package.

Then, you can use the migration.php tool to run the missing migration manually:

php lib/pkp/tools/migration.php classes.migration.upgrade.OJSv3_3_0UpgradeMigration up

Finally, you’ll need to upgrade your versions table to add a new entry for 3.3.0-3. Use the 3.2.x equivalent as a template. Make sure current is set to 1 for the new entry and 0 for the previous one.

It’s possible that this will be complicated by subsequent attempts to re-run the upgrade – again, that time machine might come in handy. We’ve established that this isn’t a bug, and upgrade debugging is time-consuming, so hopefully this works.

Regards,
Alec Smecher
Public Knowledge Project Team

I was able to run the php script without any output from the server.

How do I upgrade the versions table to add the new entry? That’s the part that I’m on right now.

I did create a separate backup of my entire instance on AWS, a dump of the SQL tables (even if they’re busted right now because of me), and have everything saved off-site on a local storage to redo and fix this as many times as I need to right now.

Thank you.

Hi @charlescmyers,

An option to complete the versions part of the upgrade without modifying the database manually is to edit dbscripts/xml/upgrade.xml and temporarily remove or comment out the following lines near the bottom:

<migration class="lib.pkp.classes.migration.upgrade.PKPv3_3_0UpgradeMigration" />
<migration class="classes.migration.upgrade.OJSv3_3_0UpgradeMigration" />

Then run the upgrade script again to update the versions entry.

[Note to anyone else who might be reading this advice: tricking your OJS installation into updating the versions table if you’ve only partially managed to run the migrations won’t solve your problem for you! More likely than not you’ll run into SQL errors as you try to use the system, and you’ll definitely garble your database further. As always when working through issues like these, good backups are invaluable.]

Regards,
Alec Smecher
Public Knowledge Project Team

The update script is the same one that you mentioned above or the general CLI update script?

In other words, running

php lib/pkp/tools/migration.php classes.migration.upgrade.OJSv3_3_0UpgradeMigration up

or running

php tools/upgrade.php upgrade