OJS 3.3.0.3 upgrade failing SQLSTATE(42S22)

After running the upgrade script, I received the following:

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

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::installDefaultNavigationMenus]

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::addPluginVersions]
[post-install]
Successfully upgraded to version 3.3.0.3

However, visiting the site still results in the page looking off. The error log presents the following:

PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ in …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78

EDIT
After that error message, I’m getting the null value error again:

[05-Mar-2021 20:27:35 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
Stack trace:
#0 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php(200): Illuminate\Database\Capsule\Manager::connection()
#1 …/ojs/lib/pkp/classes/db/DAO.inc.php(65): Illuminate\Database\Capsule\Manager::__callStatic(‘raw’, Array)
#2 …/ojs/lib/pkp/classes/context/ContextDAO.inc.php(63): DAO->retrieve(‘SELECT * FROM j…’, Array)
#3 …/ojs/lib/pkp/classes/core/PKPRouter.inc.php(252): ContextDAO->getByPath(‘conference’)
#4 …/ojs/lib/pkp/classes/core/PKPRequest.inc.php(760): PKPRouter->getContext(Object(Request), 1)
#5 …/ojs/classes/core/Request.inc.php(64): PKPRequest->_delegateToRouter(‘getContext’, 1)
#6 in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php on line 74

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

I started digging farther back in the error.log file for anything I could’ve missed and when I ran the upgrade script through CLI, although the output said that the update was completed, I just saw this:

[05-Mar-2021 20:08:11 UTC] PHP Fatal error: Uncaught Error: Call to a member function connection() on null in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php:98
Stack trace:
#0 …/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php(25): Illuminate\Database\Capsule\Manager::schema()
#1 …/ojs/lib/pkp/tools/migration.php(64): OJSv3_3_0UpgradeMigration->up()
#2 …/ojs/lib/pkp/tools/migration.php(73): migrationTool->execute()
#3 {main}
thrown in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php on line 98

From what I can gather, when I ran the upgrade migration script, it didn’t work.

I restored the backup before I ran through the fix recommended and again got the “Successfully upgraded” notice on the CLI but the error log throws the following:

[05-Mar-2021 21:31:37 UTC] PHP Fatal error: Uncaught Error: Call to a member function connection() on null in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php:98
Stack trace:
#0 …/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php(25): Illuminate\Database\Capsule\Manager::schema()
#1 …/ojs/lib/pkp/tools/migration.php(64): OJSv3_3_0UpgradeMigration->up()
#2 …/ojs/lib/pkp/tools/migration.php(73): migrationTool->execute()
#3 {main}
thrown in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php on line 98
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 21:31:42 UTC] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.

Hi @charlescmyers,

Is installed set to Off in config.inc.php? If not, set it to On and try the migration tool again.

Regards,
Alec Smecher
Public Knowledge Project Team

I set installed to on on the config but it still returned the same error.

I’m restoring an slightly earlier backup-volume that I created in order to try it again.

Ok I restored the earlier, earlier backup and ran through the scripts again after verifying I fixed and had the correct files updated.
The CLI output is as follows:

[@@ ojs]$ php lib/pkp/tools/migration.php classes.migration.upgrade.OJSv3_3_0UpgradeMigration up
[@@ ojs]$ php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.3]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::installDefaultNavigationMenus]

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::addPluginVersions]
[post-install]
Successfully upgraded to version 3.3.0.3

Error log looks like the following:

[05-Mar-2021 22:00:05 UTC] PHP Fatal error: Uncaught Error: Call to a member function connection() on null in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php:98
Stack trace:
#0 …/ojs/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php(25): Illuminate\Database\Capsule\Manager::schema()
#1 …/ojs/lib/pkp/tools/migration.php(64): OJSv3_3_0UpgradeMigration->up()
#2 …/ojs/lib/pkp/tools/migration.php(73): migrationTool->execute()
#3 {main}
thrown in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Capsule/Manager.php on line 98
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 8, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 7, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 6, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 4, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 3, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 5, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:00:46 UTC] WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
[05-Mar-2021 22:01:07 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ 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 locale…’, Array)
#1 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): Doctrine\DBAL\Driver\PDOConnection->prepare(‘select locale…’)
#2 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘select locale…’, Array)
#3 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘select locale…’, Array, Object(Closure))
#4 /home in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

Hi @charlescmyers,

I think there’s still a failure when you run php lib/pkp/tools/migration.php classes.migration.upgrade.OJSv3_3_0UpgradeMigration up. Can you isolate just the log entries that result from that? (You’ll need to restore from backup first.)

Regards,
Alec Smecher
Public Knowledge Project Team

When I run the migration script in CLI, I don’t get any output from the shell. Would I need to run it in -v or something in order to get the verbose output or something?

The only log information that I get is from the error.log when it comes up with the information listed above.

Hi @charlescmyers,

The log entries you quoted are a mix of things from the migrate.php tool and the subsequent upgrade script attempt. I’m looking for just the entries that relate to the migrate.php tool. You’ll probably have to check the log to make note of what’s already present, try the tool, then check the log immediately afterwards.

Regards,
Alec Smecher
Public Knowledge Project Team

@asmecher,

After running the upgrade migration tool, CLI outputs:

[@@ ojs]$ php lib/pkp/tools/migration.php classes.migration.upgrade.OJSv3_3_0UpgradeMigration up
[@@ ojs]$

And the error log doesn’t show any changes from the last entry that I restored it to.

EDIT

After checking the logs again and again when I ran the script, the following error posted to the log:

[05-Mar-2021 22:30:16 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ 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 locale…’, Array)
#1 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): Doctrine\DBAL\Driver\PDOConnection->prepare(‘select locale…’)
#2 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘select locale…’, Array)
#3 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘select locale…’, Array, Object(Closure))
#4 /home in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
[05-Mar-2021 22:30:17 UTC] PHP Warning: require_once(…/ojs/plugins/generic/pln/classes/tasks/Depositor.inc.php): failed to open stream: No such file or directory in …/ojs/lib/pkp/includes/functions.inc.php on line 25
[05-Mar-2021 22:30:17 UTC] PHP Fatal error: require_once(): Failed opening required ‘…/ojs/plugins/generic/pln/classes/tasks/Depositor.inc.php’ (include_path=‘.:/opt/cpanel/ea-php73/root/usr/share/pear’) in …/ojs/lib/pkp/includes/functions.inc.php on line 25

EDIT 2

It looks like the error.log file is throwing the same errors related to the ones posted above:

[05-Mar-2021 22:38:47 UTC] PHP Warning: require_once(…/ojs/plugins/importexport/medra/MedraInfoSender.inc.php): failed to open stream: No such file or directory in …/ojs/lib/pkp/includes/functions.inc.php on line 25
[05-Mar-2021 22:38:47 UTC] PHP Fatal error: require_once(): Failed opening required ‘…/ojs/plugins/importexport/medra/MedraInfoSender.inc.php’ (include_path=‘.:/opt/cpanel/ea-php73/root/usr/share/pear’) in …/ojs/lib/pkp/includes/functions.inc.php on line 25
[05-Mar-2021 22:38:48 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ 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 locale…’, Array)
#1 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): Doctrine\DBAL\Driver\PDOConnection->prepare(‘select locale…’)
#2 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘select locale…’, Array)
#3 …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘select locale…’, Array, Object(Closure))
#4 /home in …/ojs/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

To update a little more information, I’m not sure what the ojs/plugins/importexport/medra... information even is. I don’t have that located anywhere on the installation of OJS that I’m using so I’m not surprised that it’s returning an error with that. I’m guessing that has to do with the database issues as well.

@asmecher

I found this thread from years ago (DB Error: Unknown column 'Array' in 'field list' - #7 by franciscosatiro) that looks like it’s similar to the issue I’m running into right now with the database. The solution you gave was figuring out which value should’ve been a string rather than an array. In the stack trace above, #0 shows that it’s preparing ('select locale...', Array) in a similar fashion. There’s also the solution the user found that was a reference to a setting that should’ve been deleted after the upgrade was finished.

Would either of those be worth looking into regarding the problem I’ve created?

Hi @asmecher

As you mention, in my case, the first error message when I ran into the first upgrade was:

[06-Mar-2021 02:56:01 UTC] SQLSTATE[HY000]: General error: 1005 Can’t create table u9461685_ojs202.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))

I tried again, then the only error message about SQLSTATE(42S22) comes up

[06-Mar-2021 02:56:29 UTC] 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? or how the complete steps for fixing this issue
Thanks

Best regards.

I can suggest a read at Problem with upgrading to ojs 3-3-0-3

@asmecher I’m working with my IT department to determine at what point of the upgrade script the database seems to fail to move forward. Mirroring your thought earlier in the thread, it seems that the versions table is not being updated and applying the 3.x active column, even after editing the upgrade.xml script and commenting out that last section.

If he figures something out, I will come seeking further help in getting the upgrade working and getting OJS back up and running.

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

From the IT database manager, he mentioned this to me -

I would go back to the developer you were corresponding with and ask specifically what the values should be in the versions table for the upgrade. As if manually inserting that record would make a difference. If so, and we know what those values should be I can get that record inserted into the table for you.
The other thing I would ask is specifically about the error message about not finding the column called locale. As what table that is referencing and if that column should or should not be in the table. If it should, we’ll need to know the data type of it.

I know the error in the error_log mentions specifically the SQL value below:

[05-Mar-2021 22:38:48 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ in …/ojs/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78

Any information that I can pass on to him would be wonderful.
Thank you.

I concur with the issue highlighted. Earlier when I did upgrading from v 2.x to 3.2 it upgraded without any issue. But the new file 49MB of OJS3.3.x has some issues. I suggest try with earlier than this new version that works fine.

Hello everyone,

I’ve run out of time and options with my prior (failed) database and have spun up a fresh, new install of OJS. I will now begin the process of migrating the data over from the previous installation and working with someone proficient in SQL to merge the old data from the previous instance into the new SQL database. I realize this is not an elegant solution and any tips would be helpful - I’ve already created three backups (one virtual, one backup of the database and file system on a local storage, and one that’s located in GitHub of the prior installation and configuration) so if I fail on something I have instances to roll-back to.

Thank you for your help with this.