[OPEN-ILS-DEV] 2.1 RC2 upgrade script testing

[Ben Shum]

Continuing along the lines of what Robert has, these were the results from our attempts to use the existing 2.0-2.1 upgrade script.  Our test system is based on 2.0.6, but contains database patches up through 2.0.8 release.

Line 13 - CREATE SCHEMA evergreen

This fails and subsequently kills the rest of the script if you already have the "evergreen" schema for some reason or another.  I'm not sure why we have one, but we do, so I commented out the line to create the schema and kept moving on.

Next issue:

2.0-2.1-upgrade-db.sql:960: ERROR:  cannot ALTER TABLE "circ_matrix_matchpoint" because it has pending trigger events

Line 960 - adding the renew column fails

tsbere suggested in IRC to put a COMMIT; and BEGIN; before the alter table for config.circ_matrix_matchpoint helps to move this along.  So somewhere earlier there must be unresolved changes that affect the table alterations.

Next issue:

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:2623: ERROR:  could not create unique index "asset_call_number_label_once_per_lib"
DETAIL:  Key (record, owning_lib, label, prefix, suffix)=(39827, 5, DVD 613.71 PIL, -1, -1) is duplicated.

Line 2623 - create unique call number index issues

This turned out to be a purely local issue for us whereby our system contained multiple call numbers with the same label per library and record (which is not allowed by the unique index).  We're cleaning up this data by hand for now (not sure how it occurred in the first place, but assuming it to be migration-related quirks).  In the meantime, to move the process along, I deleted all the call numbers affected in the test databases.

Next issue:

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:2663: ERROR:  trigger "facet_force_nfc_tgr" for relation "facet_entry" already exists

I added a drop trigger before the create trigger in for this to proceed further.  

Next issue:

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:3460: ERROR:  trigger "action_circulation_target_copy_trig" for relation "circulation" already exists

Same as previous, needed a drop trigger before the create trigger.  In this case, I just decided to comment out the line to move things along.

Next issue:

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:5751: ERROR:  duplicate key value violates unique constraint "org_unit_setting_type_label_key"
DETAIL:  Key (label)=(Circ:  Patron Merge Address Delete) already exists.

Commented out all the patron merge org unit settings for now, it seems we already have them on our system.

Next issues:

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:6043: ERROR:  duplicate key value violates unique constraint "perm_list_code_key"
DETAIL:  Key (code)=(MARK_ITEM_MISSING_PIECES) already exists.

psql:Evergreen-ILS-2.1-RC2/Open-ILS/src/sql/Pg/2.0-2.1-upgrade-db.sql:6043: ERROR:  duplicate key value violates unique constraint "perm_list_code_key"
DETAIL:  Key (code)=(UPDATE_HOLD_REQUEST_TIME) already exists.

Both of these are part of a mass update that includes a bunch of new permissions.  In our case, we already had these two already in place since we discovered them missing for certain functionality in 2.0.  Commented them out for now.

Conclusions:

Overall, a pretty smooth upgrade script for us.  We'll keep testing 2.0-2.1 while we work towards our actual upgrade.

Thanks!

-- Ben

Benjamin Shum
Open Source Software Coordinator
Bibliomation, Inc.
32 Crest Road
Middlebury, CT 06762
203-577-4070, ext. 113