Bug 41246 - Deleting patron categories or libraries can leave orphaned rules in the overduerules table
Summary: Deleting patron categories or libraries can leave orphaned rules in the overd...
Status: Failed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Circulation (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Lucas Gass (lukeg)
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-11-13 14:18 UTC by Lucas Gass (lukeg)
Modified: 2025-12-29 17:24 UTC (History)
4 users (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete (4.94 KB, patch)
2025-11-13 14:59 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete (5.16 KB, patch)
2025-11-13 15:05 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review
Bug 41246: DBIC changes [DO NOT PUSH] (4.77 KB, patch)
2025-11-13 15:05 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Lucas Gass (lukeg) 2025-11-13 14:18:09 UTC
To recreate: 

1. Add or have a patron category.
2. Go to Tools >  Overdue notice/status triggers 
3. Set some rules for that category.
4. Delete the patron category 
5. Now do "select distinct categorycode from overduerules;"
6. The rule remains with a non-existent categorycode
Comment 1 Lucas Gass (lukeg) 2025-11-13 14:22:41 UTC
Just notices this is also true of branches. If I make a rule for a specific branch and then deleted the branch, the overduerule remains.
Comment 2 Lucas Gass (lukeg) 2025-11-13 14:59:30 UTC Comment hidden (obsolete)
Comment 3 Lucas Gass (lukeg) 2025-11-13 15:05:55 UTC
Created attachment 189565 [details] [review]
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete

To test:
0. APPLY PATCH, updatedatabase, restart_all
1. Add or have a patron category.
2. Go to Tools >  Overdue notice/status triggers
3. Set some rules for that category.
4. Delete the patron category
5. Now do "select distinct categorycode from overduerules;"
6. All rules should be gone.
Comment 4 Lucas Gass (lukeg) 2025-11-13 15:05:58 UTC
Created attachment 189566 [details] [review]
Bug 41246: DBIC changes [DO NOT PUSH]
Comment 5 Andrew Fuerste-Henry 2025-11-13 15:27:48 UTC
It's worth noting explicitly that these orphaned rules cause the overdues cron to fail and not generate notices for anyone.
Comment 6 Roman Dolny 2025-12-10 19:59:10 UTC
Can not confirm p. 3, error when saving changes:

Error trace
CGI::Compile::ROOT::kohadevbox_koha_tools_overduerules_2epl::overduerules_2epl(): DBI Exception: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`koha_kohadev`.`overduerules`, CONSTRAINT `overduerules_ibfk_branchcode` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE) at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 7 Lucas Gass (lukeg) 2025-12-29 17:24:39 UTC
(In reply to Roman Dolny from comment #6)
> Can not confirm p. 3, error when saving changes:
> 
> Error trace
> CGI::Compile::ROOT::kohadevbox_koha_tools_overduerules_2epl::
> overduerules_2epl(): DBI Exception: DBD::mysql::st execute failed: Cannot
> add or update a child row: a foreign key constraint fails
> (`koha_kohadev`.`overduerules`, CONSTRAINT `overduerules_ibfk_branchcode`
> FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE
> CASCADE ON UPDATE CASCADE) at /usr/share/perl5/CGI/Emulate/PSGI.pm line 30
>  at /usr/share/perl5/DBIx/Class/Exception.pm line 77

Roman, I cannot recreate this. Are you updating the database after applying the patchset? Are you taking the schema patch when you test?