Bug 32729 - Deleting an item removes the itemnumber from old_issues when it probably should not
Summary: Deleting an item removes the itemnumber from old_issues when it probably shou...
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Lari Strand
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-01-25 18:34 UTC by Lucas Gass
Modified: 2024-05-10 17:54 UTC (History)
10 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 32729: Drop constraint to items.itemnumber from old_issues-table (2.06 KB, patch)
2024-03-11 10:11 UTC, Lari Strand
Details | Diff | Splinter Review
Bug 32729: Drop constraint to items.itemnumber from old_issues-table (2.11 KB, patch)
2024-03-26 14:38 UTC, Brendan Lawlor
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Lucas Gass 2023-01-25 18:34:31 UTC
I ran across this when troubleshooting lifetime savings on ISSUESLIPs.

To recreate:

1. Checkout an item and check it back in.
2. Look at the old_issues tabe ( select itemnumber from old_issues; )
3. See the itemnumber
4. Delete the item.
5. Check old_issues again, the itemnumber is now NULL.
Comment 1 Cab Vinton 2023-01-26 15:31:27 UTC
Unless there's a good reason for this data loss, the itemnumber should be preserved.

If I've understood things correctly, resolving this issue would make it possible to calculate an accurate running total of savings based on the value of a patron's checkouts, i.e., a total that includes values for items that have been deleted.

Cab Vinton/ Plaistow Public Library
Comment 2 Lari Strand 2024-02-14 13:07:46 UTC
(In reply to Cab Vinton from comment #1)
> Unless there's a good reason for this data loss, the itemnumber should be
> preserved.

I agree. We need this information for reports and finding out biblio details based on the itemnumber in the old_issues table etc.

I propose we remove the constraint:

CONSTRAINT `old_issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,

from old_issues-table.

Same goes for old_reserves-table (propably should make a new ticket?):

CONSTRAINT `old_reserves_ibfk_3` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL,

These tables are archive tables and I think this information should be preserved. I can't justify the data loss with any good reason.
Comment 3 Lari Strand 2024-02-15 10:51:30 UTC
Created another ticket for the same issue for old_reserves:

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36101
Comment 4 Lari Strand 2024-03-11 10:11:09 UTC
Created attachment 163019 [details] [review]
Bug 32729: Drop constraint to items.itemnumber from old_issues-table

This patch removes the constraint from old_issues table to the items table so a deleted item's itemnumber will be preserved in the old issues table.

To test:

1. Apply patch
2. run updatedatabase.pl
3. Check constraint old_issues_ibfk_2 has been dropped
Comment 5 Brendan Lawlor 2024-03-26 14:38:39 UTC
Created attachment 163939 [details] [review]
Bug 32729: Drop constraint to items.itemnumber from old_issues-table

Signed-off-by: Brendan Lawlor <blawlor@clamsnet.org>
Comment 6 Brendan Lawlor 2024-03-26 14:44:39 UTC
Test notes:

updatedatabase
DEV atomic update /kohadevbox/koha/installer/data/mysql/atomicupdate/bug_32729.pl  [14:30:50]: Bug 32729 - Drop constraint to items.itemnumber from old_issues-table
        Dropped constraint old_issues_ibfk_2 from old_issues

Checked items out and back in, deleted items and ran report to confirm that the itemnumber is retained.
Comment 7 Kyle M Hall 2024-05-10 17:54:49 UTC
I'm wondering if we should add an deleted_itemnumber column ala Bug 22421 as an alternative.