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

Fri Aug 19 15:52:20 EDT 2011

On Thu, Aug 18, 2011 at 2:06 AM, Ben Shum <bshum at biblio.org> wrote:
> 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.

I'm moving this to before the transaction starts, so it can fail (at
least, for this reason) without killing the rest.

>
> 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.
>

I'm moving this alter (and several more) up to before the stored proc
that fixes up the circ matrix.

> 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.
>

I don't know that there's much to do about this...

> 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.
>

Adding a DROP TRIGGER IF EXISTS.

> 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.
>

And, same fix.

> 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.
>

I've moved these to after the transaction, per Robert's LP bug.

> 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.
>

hrm... how about:

INSERT INTO permission.perm_list
    SELECT  np.*
      FROM  (VALUES
                (485, 'CREATE_VOLUME_SUFFIX', oils_i18n_gettext(485,
'Create suffix label definition.', 'ppl', 'description'))
                ,(486, 'UPDATE_VOLUME_SUFFIX', oils_i18n_gettext(486,
'Update suffix label definition.', 'ppl', 'description'))
                ,(487, 'DELETE_VOLUME_SUFFIX', oils_i18n_gettext(487,
'Delete suffix label definition.', 'ppl', 'description'))
                ,(488, 'CREATE_VOLUME_PREFIX', oils_i18n_gettext(488,
'Create prefix label definition.', 'ppl', 'description'))
                ,(489, 'UPDATE_VOLUME_PREFIX', oils_i18n_gettext(489,
'Update prefix label definition.', 'ppl', 'description'))
                ,(490, 'DELETE_VOLUME_PREFIX', oils_i18n_gettext(490,
'Delete prefix label definition.', 'ppl', 'description'))
                ,(491, 'CREATE_MONOGRAPH_PART', oils_i18n_gettext(491,
'Create monograph part definition.', 'ppl', 'description'))
                ,(492, 'UPDATE_MONOGRAPH_PART', oils_i18n_gettext(492,
'Update monograph part definition.', 'ppl', 'description'))
                ,(493, 'DELETE_MONOGRAPH_PART', oils_i18n_gettext(493,
'Delete monograph part definition.', 'ppl', 'description'))
                ,(494, 'ADMIN_CODED_VALUE', oils_i18n_gettext(494,
'Create/Update/Delete SVF Record Attribute Coded Value Map', 'ppl',
'description'))
                ,(495, 'ADMIN_SERIAL_ITEM', oils_i18n_gettext(495,
'Create/Retrieve/Update/Delete Serial Item', 'ppl', 'description'))
                ,(496, 'ADMIN_SVF', oils_i18n_gettext(496,
'Create/Update/Delete SVF Record Attribute Defintion', 'ppl',
'description'))
                ,(497, 'CREATE_BIB_PTYPE', oils_i18n_gettext(497,
'Create Bibliographic Record Peer Type', 'ppl', 'description'))
                ,(498, 'CREATE_PURCHASE_REQUEST',
oils_i18n_gettext(498, 'Create User Purchase Request', 'ppl',
'description'))
                ,(499, 'DELETE_BIB_PTYPE', oils_i18n_gettext(499,
'Delete Bibliographic Record Peer Type', 'ppl', 'description'))
                ,(500, 'MAP_MONOGRAPH_PART', oils_i18n_gettext(500,
'Create/Update/Delete Copy Monograph Part Map', 'ppl', 'description'))
                ,(501, 'MARK_ITEM_MISSING_PIECES',
oils_i18n_gettext(501, 'Allows the Mark Item Missing Pieces action.',
'ppl', 'description'))
                ,(502, 'UPDATE_BIB_PTYPE', oils_i18n_gettext(502,
'Update Bibliographic Record Peer Type', 'ppl', 'description'))
                ,(503, 'UPDATE_HOLD_REQUEST_TIME',
oils_i18n_gettext(503, 'Allows editing of a hold''s request time,
and/or its Cut-in-line/Top-of-queue flag.', 'ppl', 'description'))
                ,(504, 'UPDATE_PICKLIST', oils_i18n_gettext(504,
'Allows update/re-use of an acquisitions pick/selection list.', 'ppl',
'description'))
                ,(505, 'UPDATE_WORKSTATION', oils_i18n_gettext(505,
'Allows update of a workstation during workstation registration
override.', 'ppl', 'description'))
                ,(506, 'VIEW_USER_SETTING_TYPE',
oils_i18n_gettext(506, 'Allows viewing of configurable user setting
types.', 'ppl', 'description'))
            ) np(id,code,description)
            LEFT JOIN permission.perm_list pl USING (id)
      WHERE pl.id IS NULL;

> 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!
>

Thank you!

-- 
Mike Rylander
 | Director of Research and Development
 | Equinox Software, Inc. / Your Library's Guide to Open Source
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com