Missing db functions in 3.0.1-3.0.2 upgrade script

Bug #1772028 reported by Bill Ott on 2018-05-18
16
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Evergreen
High
Unassigned
3.0
High
Unassigned
3.1
Undecided
Unassigned
3.2
Undecided
Unassigned

Bug Description

While working through upgrade scripts, I found several functions missing from my database. I don't see them added in another script, so it should be benign to just do a CREATE OR REPLACE before adding triggers for them.

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=94d2924f38330f66ff6ad292c6198e79ac620f39

Changed in evergreen:
status: New → Confirmed
importance: Undecided → High
milestone: none → 3.2-beta
Jason Stephenson (jstephenson) wrote :

I set this to confirmed and high because I see the functions are missing from any upgrade scripts. That said, I seem to have all but the last one, evergreen.asset_copy_alert_copy_inh_fkey, in a database that was upgraded from 2.12.8 to 3.0.7 using the upgrade scripts. I don't know where the functions came from unless they were always there or were added to our production database at some point prior to the dump and test upgrade. I don't remember adding them, myself.

Also I am not sure that editing a published upgrade script is the best way to go. We may want to do that and create a new upgrade script for those who won't re-run the 3.0.1-3.0.2 upgrade.

John Merriam (jmerriam) wrote :

Hello. We also seem to be missing a couple functions in our database. What is interesting is that for the 3.0 upgrade we only seem to be missing evergreen.container_copy_bucket_item_target_copy_inh_fkey() but we were NOT missing the evergreen.asset_* functions that Bill has in his patch.

For the 3.1 upgrade we were also missing oils_json_to_text()

I assumed this was some sort of brain damage that our database had accumulated over the years, added the functions back in (picking them out of the database creation scripts) and moved on.

Could something else strange be going on here?

Bill Ott (bott) wrote :

A note on oils_json_to_text(). I had that in the public schema, which seems to date back to v2.0. I did copy it into the evergreen schema which is where it's expected in 3.1

Jason Stephenson (jstephenson) wrote :

I recall there being some discussion of these functions missing before. It turns out they are supposed to be created by the 1063 upgrade script in 2.12.6 to 3.0.0. Looking at the code in 1063, it seems to me that the new functions will not be created if your database lacks the prerequisite constraints on asset.copy_pkey.

So, I'm less certain this is a confirmed bug or a data issue, but I'll not change it for now.

John Merriam (jmerriam) wrote :

Hmmm, our database has this:

CONSTRAINT copy_pkey PRIMARY KEY (id),

in asset.copy.

In our 2.12 production database, the information_schema.referential_constraints has two rows with unique_constraint_name = copy_pkey. One with constraint_name = asset_copy_note_copy_fkey The other has constraint_name = import_item_imported_as_fkey

Those unique_constraint_name = copy_pkey rows are gone in our test databases that have been upgraded to 3.0 and 3.1.

Changed in evergreen:
milestone: 3.2-beta → 3.1.3
no longer affects: evergreen/3.1
Changed in evergreen:
milestone: 3.1.3 → 3.1.4
Martha Driscoll (mjdriscoll) wrote :

I am running into this problem as well. Upgrade script 1063 does not outright create the functions. It runs a query that should return data that is then used as variables to create the functions. My 2.12.4 system built in 2012 and upgraded ever since returns no rows. A 2.12.0 system built recently returns 3 rows and creates the needed functions.

So I think the success of 3.0.1-3.0.2 depends on how old your system is. The missing functions were added to 070.schema.container.sql and 800.fkeys.sql since April 2017.

I like Bill's update to script 1081 that explicitly creates or updates the functions.

Changed in evergreen:
milestone: 3.1.4 → 3.1.5
Changed in evergreen:
milestone: 3.1.5 → 3.1.6
Changed in evergreen:
milestone: 3.1.6 → 3.2.1
Changed in evergreen:
milestone: 3.2.1 → 3.2.2
Changed in evergreen:
milestone: 3.2.2 → 3.2.3
Changed in evergreen:
milestone: 3.2.3 → 3.3-beta1

It looks like the vandelay.import_item imported_as_fkey constraint was added to the 2.2 release, but wasn't included in the upgrade scripts. So possibly those that have a pre 2.2 database, may run into at least that one?

Josh

Changed in evergreen:
milestone: 3.3-beta1 → 3.3-rc
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers