Checkout history fails on serials checkouts / deleted copies

Bug #1564079 reported by Bill Erickson
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Fix Released

Bug Description

Evergreen 2.10.0

Two part bug. Both parts related.

1. If an action.circulation row exists with a target_copy value referencing a copy which has been deleted (removed from the database), the 2.10.0 DB upgrade script will fail with a foreign key constraint violation. This scenario is rare, but possible, since there is no foreign key constraint on action.circulation.target_copy. We have evidence of at least one EG system in the wild where such data exits.

As discussed in IRC, the plan here is simply to avoid creating circ history rows when a circulation refers to a nonexistent copy.

2. Checkouts of serial.unit's do not translate successfully into action.usr_circ_history rows since usr_circ_history has a foreign-key constraint on asset.copy. Foreign key constraints do not work seamlessly with table inheritance to serial.unit.

The solution here is to remove the foreign key constraint, consistent with action.circulation.

These changes will require new upgrade scripts and a modification to the 2.10.0 SQL upgrade script, since the upgrade script will fail if either of these conditions occurs.

Tags: pullrequest
Revision history for this message
Bill Erickson (berick) wrote :

One point of clarification, action.circulation does enforce constraints on asset.copy / serial.unit via evergreen.fake_fkey_tgr. (This is similar, but not as restrictive as a native constraint). We should add the same to action.usr_circ_history.

Revision history for this message
Bill Erickson (berick) wrote :

Fixes pushed:;a=shortlog;h=refs/heads/user/berick/lp1564079-circ-history-serials-deleted

Contains 2 commits, one to support serials circ history, one to skip circs whose copies don't exist.

Notable section of the serials commit:

    This modifies the 2.9.3-2.10.0-upgrade-db.sql and 0960 upgrade scripts,
    since without this change, the scripts may fail.

    A separate upgrade script is added to drop the constraint for those who
    have already successfully run the the 2.9.3-2.10.0-upgrade-db.sql script
    (i.e. those who don't use serials). For simplicity, the
    evergreen.fake_fkey_tgr trigger is only applied in this separate upgrade
    script (in addition to the base schema, of course).

Adding pullrequest.

Changed in evergreen:
milestone: none → 2.10.2
tags: added: pullrequest
Changed in evergreen:
assignee: Bill Erickson (berick) → nobody
Revision history for this message
Bill Erickson (berick) wrote :

To test the serials change:

1. Enable circ history retention for a user.
2. Check out a serial copy (serial.unit) to the user.
3. The checkout should not explode.

Kathy Lussier (klussier)
Changed in evergreen:
status: New → Confirmed
importance: Undecided → High
assignee: nobody → Kathy Lussier (klussier)
Revision history for this message
Kathy Lussier (klussier) wrote :

Looks good to me. I merged the branch to master and backported it to 2.10.

Thanks Bill!

Changed in evergreen:
status: Confirmed → Fix Committed
assignee: Kathy Lussier (klussier) → nobody
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.