Issue when upgrading 3.2.1.2 to 3.3.0.5

Software Support
1

Hi

I tried to upgrade from 3.2.1.2 to 3.3.0.5 but it failed.

I have had a look through the forums for a solution but each one seems to indicate a need to restore the old database. Unfortunately I did not back up the database (big mistake, I know!), does this mean there is no way to restore the previously functioning journal site?

Any help or advice that could be offered would be gratefully received.

Thanks

2

Hi @ijmtl_admin

It’s hard to say, honestly, because it depends on where the upgrade failed. Have you tried using your current database with your OJS 3.2.1 installation? If you have, and there are errors, can you share them?

If you have access to a proper developer resource that would be very helpful here.

The saving grace is that the 3.3 upgrade does not modify the files tree so at least you’ll not have to rename or move submission files.

Jason

3

Hi @jnugent thanks for coming back to me so quickly.

The error log for when I try to use the 3.2.1 installation with the 3.3 database is given below:

[07-May-2021 08:42:53 UTC] PHP Deprecated: Non-static method Core::removeBaseUrl() should not be called statically in /home/cimtorgu/public_html/ijmtl/plugins/generic/usageStats/UsageStatsLoader.inc.php on line 777
[07-May-2021 08:42:53 UTC] PHP Deprecated: Non-static method Core::_getBaseUrlAndPath() should not be called statically in /home/cimtorgu/public_html/ijmtl/lib/pkp/classes/core/Core.inc.php on line 231
[07-May-2021 08:42:53 UTC] PHP Warning: require_once(/home/cimtorgu/public_html/ijmtl/plugins/importexport/medra/MedraInfoSender.inc.php): failed to open stream: No such file or directory in /home/cimtorgu/public_html/ijmtl/lib/pkp/includes/functions.inc.php on line 25
[07-May-2021 08:42:53 UTC] PHP Fatal error: require_once(): Failed opening required ‘/home/cimtorgu/public_html/ijmtl/plugins/importexport/medra/MedraInfoSender.inc.php’ (include_path=’.:/opt/cpanel/ea-php73/root/usr/share/pear’) in /home/cimtorgu/public_html/ijmtl/lib/pkp/includes/functions.inc.php on line 25
[07-May-2021 08:42:54 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ in /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78
Stack trace:
#0 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(78): PDO->prepare(‘select locale…’, Array)
#1 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): Doctrine\DBAL\Driver\PDOConnection->prepare(‘select locale…’)
#2 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘select locale…’, Array)
#3 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘select locale…’, Array, Object(Closure))
#4 /home/cimtorgu/public_htm in /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671
[07-May-2021 08:42:55 UTC] PHP Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘locale’ in ‘field list’ in /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:78
Stack trace:
#0 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php(78): PDO->prepare(‘select locale…’, Array)
#1 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(331): Doctrine\DBAL\Driver\PDOConnection->prepare(‘select locale…’)
#2 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘select locale…’, Array)
#3 /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘select locale…’, Array, Object(Closure))
#4 /home/cimtorgu/public_htm in /home/cimtorgu/public_html/ijmtl/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

Any way you can see of salvaging this would be massively helpful!

Thanks, Russell

4

Hi Russell,

There’s probably not an easy way to fix this, without technical knowledge. Errors like:

Unknown column ‘locale’ in ‘field list’

Mean that some of the schema adjustments for 3.3 have occurred and the SQL queries that are part of 3.2 are now missing columns. If you have a developer on hand, you could try enabling

debug = On

In the [database] section of your configuration file and then watching the queries and finding the one that is failing. It may be helpful to set up a brand new 3.2.1 installation and compare the database schemas between your broken one and that one, to see what’s been changed.

Fixing this will be a lot of work but it might not be a complete loss. I asked this in my last post, but do you know where the upgrade failed?

Finally, talk to your systems administrator. You may have not made a database backup before running the upgrade, but perhaps the server itself is backed up on a regular basis and there might be a way to restore it. That’s standard practice for us at PKP|PS.

Best
Jason

5

Hi Jason

Thanks again for your help. I have actually contacted the web host for our site to see if they have a backup of the database prior to the upgrade and it looks like they do!!

Hopefully if I can get the database restored to how it was prior to the upgrade then at least the site will be back to how it was.

I may well come back once I establish what the issue was with the original upgrade attempt.

Best wishes

Russell

6

Hi @jnugent, just to say that the old database has been restored and the site is working again which is a big relief. I have definitely learnt my lesson on making sure everything is backed up!

I will attempt to update again next week. I have noticed that my database tables are all MyISAM rather than InnoDB and the collation is set as latin1_swedish_ci. Could this have been the cause of the update not working (I saw mention of this on another thread about update issues).

Thanks, Russell

7

Hi Russell,

Yes, if you want to use 3.3 you’ll need to convert those tables to InnoDB. OJS 3.3 is the first version of OJS to make use of foreign keys and the only way to do that is to use InnoDB tables. As you say, there are other threads about this now.

The latin1 collation isn’t so much a problem, so long as everything is consistent. We recommend utf8_unicode_ci but converting table encodings can be a whole problem all of its own. I would at least recommend making sure the database settings are the same in the 3.3 configuration file to prevent corruption during the upgrade. If you’re basing your new config off of the template that comes with 3.3, be advised that it uses utf8 by default:

Best
Jason

8

Hi @jnugent

I have had another go at upgrading to 3.3 having made the changes to the database. It failed again and the error is given below:

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

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::installDefaultNavigationMenus]

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]
[migration: PKPv3_3_0UpgradeMigration]
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘922-submissionFileId’ for key ‘event_log_settings_pkey’ (SQL: update event_log_settings set setting_name = submissionFileId where setting_name = fileId)

I have had a look in the database but can’t find a duplicate entry for the table indicated so am a bit stuck on how to progress the update.

Best wishes

Russell

9

Hi Russell,

It took me a while to come back with an answer because I needed to look at the database schema in 3.2.1 versus 3.3. So, what happens during the upgrade to 3.3 is that some setting_name values get renamed in 3.3. The fileId setting name becomes submissionFileId, to match what is happening in the new files table.

The key that exists on the event_log_settings table mandates that there can only be a single combination of log_id and setting_name. So I suspect that if you look at your event_log_settings table prior to the upgrade to 3.3, you’ll see that if you run:

SELECT * from event_log_settings WHERE log_id = 922;

You’ll get a bunch of rows back, and perhaps two where the setting_name is fileId.

Cheers,
Jason

10

Hi Jason

Should there be any instances in the 3.2.1 event_log_settings table of setting_name as submissionFileId or should there only be fileId?

When I run the query for log_id = 922 I don’t get a duplicate of fileId

Best wishes

Russell

11

Hi Russell,

My understanding is that there shouldn’t be any submissionFileId fields present. If there are, the UPDATE that switches fileId to submissionFileId will hit the “key unique” error if you had entries that had both for the same log_id. In the 3.2 installations I have handy, I don’t see any entries in the table for submissionFileId.

Cheers,
Jason

12

Hi Jason

For some reason the backup database had some tables missing, one of which was event_log_settings so I copied them across from the damaged 3.3 database to get functionality back for 3.2.1 install. This then caused the duplicate entry issue when trying to update.

I have now removed all instances where submissionFileId and fileId exist for the same log_id and the update to 3.3 has been successful. I’m not entirely confident that there won’t be further issues but is seems to be working at the moment!

Really appreciate your patience and help in getting this sorted.

Best wishes

Russell

13

Hi Russell,

Glad to hear it. I’ll mark this thread as closed but if you have other questions feel free to start new ones.

Jason