Pkp-lib: Test/fix PostgreSQL upgrades

[asmecher]

See http://forum.pkp.sfu.ca/t/upgrade-failure-postgresql/19215

[asmecher]

Patches to apply:

• In lib/pkp: https://github.com/pkp/pkp-lib/commit/7e33ad63f2ee4c2b3063923d70a36e3fab23f07e
• In root: https://github.com/pkp/ojs/commit/69ed23aba986a0206ff9eb4f92dd21f30b6edccd
• In root: https://github.com/pkp/ojs/commit/625a73622375ff07242452a32fc939dfbcca5496
• To correct ERROR: type "serial" does not exist: Apply the patch https://gist.github.com/asmecher/d1013624ff5325eae97384ae5adc88c4 in the OJS/OMP installation directory.
  
  
  
  • On *NIX-based systems, you can do this by running...
    
    wget -q -O - "https://gist.github.com/asmecher/d1013624ff5325eae97384ae5adc88c4/raw/c636dded71570c195078c68fe2608f0efc552bdd/postgresql-serials.diff" | patch -p1
    
    You should see the following output:
    
    checking file lib/pkp/lib/adodb/adodb-datadict.inc.php checking file lib/pkp/lib/adodb/datadict/datadict-postgres.inc.php
  
  

[asmecher]

Closing for now.

[mittinatten]

The patch for "ERROR: type serial.." can't be applied as is, had to remove the last change in the set (which was just a new-line).

[asmecher]

@mittinatten OK, thanks for the heads-up (it's quite an old patch). Unfortunately that's a patch to the ADODB library which is getting very long in the tooth; I suspect we're going to have to replace it, which is not going to be exciting. Otherwise, did the set of patches above fix the upgrade process for you?

[mittinatten]

Unfortunately not, posting my other issues here (don't know if you'd rather have it in the PKP forum). I am pretty new to OJS, so I'm open to the possibility that I missed something simple.

System: RedHat 7.2, php 5.4.16, PostgreSQL 9.2.15, upgrading from OJS 2.4.2 (database has several journals)

After modifying my installation with the patches (and modifying the patch as mentioned) the above error disappeared, using the latest version of ojs-stable-3_0_0m. Now it completes quite a few steps but I get a bunch of warnings and it finally crashes at [code: Installer Installer::migrateTimedViewsUsageStatistics] with the following error

DB Error: ERROR:  relation "usage_stats_temporary_records" does not exist
LINE 1: DELETE from usage_stats_temporary_records WHERE load_id =  $...
                    ^</h1>ojs2: DB Error: ERROR:  relation "usage_stats_temporary_records" does not exist
LINE 1: DELETE from usage_stats_temporary_records WHERE load_id =  $...

This is preceded by these warnings (the 'no_NO' thing might a problem with our old database, should be 'nb_NO')

PHP Warning:  Cannot use a scalar value as an array in lib/pkp/classes/core/DataObject.inc.php on line 133
PHP Warning:  Cannot use a scalar value as an array in lib/pkp/classes/db/SettingsDAO.inc.php on line 41
PHP Warning:  Illegal string offset 'no_NO' in lib/pkp/classes/core/DataObject.inc.php on line 133
PHP Warning:  Illegal string offset 'no_NO' in lib/pkp/classes/db/SettingsDAO.inc.php on line 41
PHP Warning:  Cannot use a scalar value as an array in lib/pkp/classes/core/DataObject.inc.php on line 133
PHP Warning:  Cannot use a scalar value as an array in lib/pkp/classes/core/DataObject.inc.php on line 133
PHP Warning:  Illegal string offset 'nb_NO' in lib/pkp/classes/core/DataObject.inc.php on line 133
PHP Notice:  Use of undefined constant USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_HTML - assumed 'USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_HTML' in classes/install/Upgrade.inc.php on line 483
PHP Notice:  Use of undefined constant USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_PDF - assumed 'USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_PDF' in classes/install/Upgrade.inc.php on line 484
PHP Notice:  Use of undefined constant USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_OTHER - assumed 'USAGE_STATS_REPORT_PLUGIN_FILE_TYPE_OTHER' in classes/install/Upgrade.inc.php on line 486
PHP Notice:  Use of undefined constant OJS_METRIC_TYPE_LEGACY_COUNTER - assumed 'OJS_METRIC_TYPE_LEGACY_COUNTER' in classes/install/Upgrade.inc.php on line 500

[asmecher]

@mittinatten, thanks for the details. What version are you upgrading from? Would you be willing to provide me with a database dump from the old version for local testing over here? (If so, feel free to alter whatever you want before sending it, particularly users.password, users.email, and authors.email.)

[mittinatten]

Upgrading from 2.4.2. Can see what I can do with the dump when I'm back at work tomorrow, where can I send it?

[asmecher]

I'm alec at smecher point bc point ca, thanks!

[mittinatten]

Sent it yesterday, just checking to verify the email didn't get caught in any spam filters.

[mittinatten]

I tried upgrading the same database from 2.4.2 to 2.4.8 and that worked without error messages.

[asmecher]

@mittinatten, just a heads-up that I received the database, thanks -- just trying to scrape together the time to give it a test.

[vvkorotkikh]

I can't download last patch from the list. All browsers show Error: ERR_TOO_MANY_REDIRECTS.
How can I download this file?

Patches to apply:

In lib/pkp: 7e33ad6
In root: pkp/ojs@69ed23a
In root: pkp/ojs@625a736
To correct ERROR: type "serial" does not exist: Download patch from https://pkp.sfu.ca/support/forum/viewtopic.php?t=11896#p45922

[mittinatten]

If it helps the same patch is available here https://github.com/ubbdst/pkp-lib/commit/b29444da6d1c2f130bcab17ac2edfac3129d6109

[eniocarboni]

Why is this patch not made in version 3 of Ojs and even better in 3.1.0?
While I was upgrading from version 3.0.2 to 3.1.0 I had this error and solved only by putting the @mittinatten patch.
Database: PostgreSQL 9.3
Error: ERROR: Upgrade failed: DB: ERROR: type "serial" does not exist

[asmecher]

@eniocarboni, the patch is to third-party code (ADODB) and we try as hard as we can not to maintain local forks. Longer term, we're looking at assessing replacements for that library (https://github.com/pkp/pkp-lib/issues/2493).

[eniocarboni]

It would be useful to put a note on this issue in the README file or in docs/UPGRADE for those who use PostgreSQL

[asmecher]

@eniocarboni, I've added this: https://github.com/pkp/ojs/commit/6da9200d7806471adea02a34b53d4d389a4964ad

[progerjkd]

I got a similar error when upgrading from ojs 2.4.8.3 to 3.1.1.0.

My environment:
PHP 7.0.15-1
postgresql-9.1.7-1
Debian 8.7 amd64

The patch that @mittinatten and @asmecher ubbdst/pkp-lib@b29444d provided fixed the "ERROR: type "serial" does not exist".
But now i'm getting the following error:

$ time php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.0.1.0]
[code: Installer Installer::syncSeriesAssocType]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics2.xml]
[data: dbscripts/xml/upgrade/3.0.0_preupdate.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptTimedViews.xml (skipped)]
[data: dbscripts/xml/upgrade/3.0.0_adaptReferrals.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptBooksForReview.xml]
[data: dbscripts/xml/upgrade/remove_timed_views_bots.xml (skipped)]
[code: Installer Installer::migrateCounterPluginUsageStatistics (skipped)]
[code: Installer Installer::migrateTimedViewsUsageStatistics (skipped)]
[code: Installer Installer::migrateDefaultUsageStatistics (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_postUsageStatsMigration.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_postUsageStatsMigration2.xml]
[data: dbscripts/xml/upgrade/2.4.0_postCounterMigration.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_postTimedViewsMigration.xml (skipped)]
[schema: lib/pkp/xml/schema/common.xml]
[schema: lib/pkp/xml/schema/log.xml]
[schema: lib/pkp/xml/schema/announcements.xml]
[schema: lib/pkp/xml/schema/scheduledTasks.xml]
[schema: lib/pkp/xml/schema/temporaryFiles.xml]
[schema: lib/pkp/xml/schema/metadata.xml]
[schema: lib/pkp/xml/schema/reviews.xml]
[schema: lib/pkp/xml/schema/reviewForms.xml]
[schema: lib/pkp/xml/schema/controlledVocab.xml]
[schema: lib/pkp/xml/schema/submissions.xml]
[schema: lib/pkp/xml/schema/submissionFiles.xml]
[schema: lib/pkp/xml/schema/notes.xml]
[schema: lib/pkp/xml/schema/views.xml]
[schema: lib/pkp/xml/schema/genres.xml]
[schema: lib/pkp/xml/schema/gifts.xml]
[schema: lib/pkp/xml/schema/mutex.xml]
[schema: lib/pkp/xml/schema/tombstone.xml]
[schema: lib/pkp/xml/schema/rolesAndUserGroups.xml]
[schema: lib/pkp/xml/schema/metrics.xml]
[schema: lib/pkp/xml/schema/views.xml]
[schema: lib/pkp/xml/schema/libraryFiles.xml]
[schema: dbscripts/xml/ojs_schema.xml]
[data: dbscripts/xml/indexes.xml]
[data: dbscripts/xml/upgrade/3.0.0_change_assoc_type.xml]
[data: dbscripts/xml/upgrade/3.0.0_change_assoc_type_metrics.xml]
[data: dbscripts/xml/upgrade/3.0.0_settings.xml]
[code: Installer Installer::migrateArticleMetadata]
[data: dbscripts/xml/upgrade/3.0.0_update.xml]
[code: Installer Installer::clearDataCache]
[code: Installer Installer::migrateUserRoles]
DB Error: ERRO: duplicar valor da chave viola a restrição de unicidade "stage_assignment"
DETALHE: Chave (submission_id, user_group_id, user_id)=(185, 16, 757) já existe.

Any tips on solving this?

Thanks in advance.

[asmecher]

@progerjkd, this is a closed issue -- could you post on the forum at http://forum.pkp.sfu.ca/ instead?

[celorodovalho]

@mittinatten could you solve the bellow issue?

[code: Installer Installer::migrateCounterPluginUsageStatistics]
[code: Installer Installer::migrateTimedViewsUsageStatistics]
<h1>DB Error: ERROR:  relation &quot;usage_stats_temporary_records&quot; does not exist
LINE 1: DELETE from usage_stats_temporary_records WHERE load_id =  $...
                    ^</h1>ojs2: DB Error: ERROR:  relation "usage_stats_temporary_records" does not exist
LINE 1: DELETE from usage_stats_temporary_records WHERE load_id =  $...

Thx

[mittinatten]

Hi @celorodovalho,
We managed to perform the update without errors in the end (late 2016, early 2017), but there were several postgres-related patches that had to be applied to the update scripts, and I don't remember how this particular error was solved (not sure if we had an error in our database, or if there was a bug in an update script). I think all those patches have been part of the standard codebase for a while now. Are you sure you have an up to date version of the code?

[celorodovalho]

@mittinatten I got the version 3.1.1-4, there is another version with the fixes?

I'm migrating from version 2.4.2-0, I made a search for "usage_stats_temporary_records" in this version and don't exists any reference to it.

[mittinatten]

3.1.1-4 is the newest version, most postgress issues were fixed in 3.0.1 or 3.0.2, as far as I know. We did have to upgrade in two steps 2.4.2 -> 2.4.8 -> 3.0.2, maybe that's worth a try?

[celorodovalho]

@mittinatten I'll try it, thanks!
If it works i'll update my answer here.

[rogaz]

I got the version 3.1.2.1 and I'm upgrading from version 3.1.1.2
I backed up the database in postgres from the version 3.1.1.2 and restore the database to the version 3.1.2.1 and I was struggling with the command:
$ php tools/upgrade.php upgrade
Because I was getting the error:
ERROR: type "serial" does not exist

But finally worked applying the patch with the next command from ojs directory:
wget -q -O - "https://gist.github.com/asmecher/d1013624ff5325eae97384ae5adc88c4/raw/c636dded71570c195078c68fe2608f0efc552bdd/postgresql-serials.diff" | patch -p1

At first glance I noted the global site name and logo was override to the default values but was very easy to update, the next days I will be verifying that everything is fine, If I found out another override I will tell you

@asmecher Thanks so much for the patch.