[Evergreen-admin] Evergreen 2.2.0-2.2.1 Upgrade Script - Actions Already Logged in config.upgrade_log

[Steve Wills]

Hi Elliot,

I just upgraded the Balsam Consortium from 2.0.9 up to 2.3.5. I can tell you that there are half a dozen places where that kind of thing happens. I suspect the moral is that we need to stay on top of our upgrades but, that said, my solution was to take snapshots after each major upgrade and hack out the offending duplicates when I encountered them. It took several restores and restarts to get thorugh all of the scripts but frequent snapshots made it much less painful.

I am not enough of a SQL wonk to know how one writes a script that checks update log first and conditionally executes the patch. In later scripts the devs have mercifully added notices that say, "this upgrade may fail since it is intended to provide a patch that may already exist" before they do the patch in a Begin transaction block. That is not consistant as you walk through the upgrade scripts however so don't count on it being "okay" if a block fails. Ultimately I found that, as painful as it was, walking through each upgrade script in sequence, restarts not withstanding, yields the best schema at the end of the day.

In sum, if misery loves company, you are well loved. :)
Steve Wills

-----Original Message-----
From: Elliot Voris [mailto:evoris at slcconline.edu]
Sent: Wednesday, April 17, 2013 07:50 PM
To: open-ils-dev at list.georgialibraries.org, 
evergreen-admin at list.evergreen-ils.org
Subject: [Evergreen-admin] Evergreen 2.2.0-2.2.1 Upgrade Script - Actions Already Logged in config.upgrade_log

Hey, All

I?m working on upgrading my (currently 2.1.1 in production) Evergreen server up to the latest version 2.3.5, as of this morning when I started. I?m having a bit of trouble with one of the version-upgrade scripts (Open-ILS/src/sql/Pg/version-upgrade/2.2.0-2.2.1-upgrade-db.sql). All the previous scripts worked (2.1.1-2.1.2, 2.1.2-2.1.3, 2.1-2.2) without anything of concern. When I try to run this script, I get the following output:

postgres at evergreen-test:~/scripts/version-upgrade$
postgres at evergreen-test:~/scripts/version-upgrade$ psql -U evergreen -h localhost -f ./2.2.0-2.2.1-upgrade-db.sql old_evergreen
Password for user evergreen:
BEGIN
INSERT 0 1
psql:./2.2.0-2.2.1-upgrade-db.sql:10: ERROR: duplicate key value violates unique constraint "upgrade_log_pkey"
DETAIL: Key (version)=(0722) already exists.
CONTEXT: SQL statement "INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to)"
PL/pgSQL function "upgrade_deps_block_check" line 17 at SQL statement
psql:./2.2.0-2.2.1-upgrade-db.sql:13: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:20: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:58: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:61: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:161: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:164: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:264: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:267: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:./2.2.0-2.2.1-upgrade-db.sql:365: ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
postgres at evergreen-test:~/scripts/version-upgrade$

I looked into it, and sure enough 0722 already does exist in config.upgrade_log. It is not the most recent entry in that table, and it was ?applied_to? NULL. As I?m looking through the rest of the script, It looks as though upgrade operations 0725 and 0726 would likely throw the same error.

What can I do to resolve the trouble with this particular script? My intention is to run through every one of the version-upgrade scripts between 2.1.1 and 2.3.5 (2.3.6 afterwards, as well), is that not the correct way to go about it?

Thanks!

Elliot J. Voris
IT Director
St. Louis Christian College

evoris at slcconline.edu
elliot at voris.me
314.837.6777 x1250



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-admin/attachments/20130418/2c23d197/attachment.html>