Bug 42068 - Update on bug 26993 breaks items deletions with a corrupted foreign key
Summary: Update on bug 26993 breaks items deletions with a corrupted foreign key
Status: Passed QA
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low blocker
Assignee: Nick Clemens (kidclamp)
QA Contact: Testopia
URL:
Keywords:
: 42067 (view as bug list)
Depends on: 26993
Blocks:
  Show dependency treegraph
 
Reported: 2026-03-11 12:31 UTC by Nick Clemens (kidclamp)
Modified: 2026-03-11 16:42 UTC (History)
3 users (show)

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


Attachments
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS (2.49 KB, patch)
2026-03-11 12:45 UTC, Nick Clemens (kidclamp)
Details | Diff | Splinter Review
Bug 42068: Update on bug 26993 breaks items deletions with a corrupted foreign key (1.46 KB, patch)
2026-03-11 13:08 UTC, Kyle M Hall (khall)
Details | Diff | Splinter Review
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS (2.54 KB, patch)
2026-03-11 15:50 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS (2.60 KB, patch)
2026-03-11 16:42 UTC, Paul Derscheid
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens (kidclamp) 2026-03-11 12:31:19 UTC
On bug 26993 we turned off foreign key cheks to remove a unique constraint, then enabled them

This meant the existing rows got wrong :-)

To recreate:
1 - Checkout 25.11.x - reset_all
2 - Enable StoreLastBorrower 
3 - Issue an item
4 - Ensure item has a row in items_last_borrower
    SELECT * FROM items_last_borrower
5 - Checkout main
6 - updatedatabase
7 - Try to delete any item, doesn't have to be the one you issued
    DELETE FROM items WHERE itemnumber=93;
8 - ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`koha_kohadev`.`items_last_borrower`, CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE)

TO fix you must recreate the constraint:
ALTER TABLE items_last_borrower DROP FOREIGN KEY items_last_borrower_ibfk_1;

ALTER TABLE items_last_borrower ADD INDEX itemnumber (itemnumber);

ALTER TABLE items_last_borrower 
    ADD CONSTRAINT items_last_borrower_ibfk_1 
    FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) 
    ON DELETE CASCADE ON UPDATE CASCADE;
Comment 1 Kyle M Hall (khall) 2026-03-11 12:40:31 UTC
*** Bug 42067 has been marked as a duplicate of this bug. ***
Comment 2 Nick Clemens (kidclamp) 2026-03-11 12:45:34 UTC
Created attachment 195123 [details] [review]
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS

To test:
1 - Checkout 25.11.x - reset_all
2 - Enable StoreLastBorrower
3 - Issue an item
4 - Ensure item has a row in items_last_borrower
    SELECT * FROM items_last_borrower
5 - Checkout main
6 - updatedatabase
7 - Try to delete any item, doesn't have to be the one you issued
    DELETE FROM items WHERE itemnumber=93;
8 - ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`koha_kohadev`.`items_last_borrower`, CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE)

9 - repeat 1-5
10 - APPLY PATCH
11 - updatedatabase
12 - Try to delete any item
13 - Success!
14 - Delete the item you issued previously (in the staff interface on via mysql)
15 - Confirm no rows in items_last_borrower
Comment 3 Kyle M Hall (khall) 2026-03-11 13:08:10 UTC Comment hidden (obsolete)
Comment 4 Lucas Gass (lukeg) 2026-03-11 15:50:15 UTC
Created attachment 195132 [details] [review]
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS

To test:
1 - Checkout 25.11.x - reset_all
2 - Enable StoreLastBorrower
3 - Issue an item
4 - Ensure item has a row in items_last_borrower
    SELECT * FROM items_last_borrower
5 - Checkout main
6 - updatedatabase
7 - Try to delete any item, doesn't have to be the one you issued
    DELETE FROM items WHERE itemnumber=93;
8 - ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`koha_kohadev`.`items_last_borrower`, CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE)

9 - repeat 1-5
10 - APPLY PATCH
11 - updatedatabase
12 - Try to delete any item
13 - Success!
14 - Delete the item you issued previously (in the staff interface on via mysql)
15 - Confirm no rows in items_last_borrower

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 5 Lucas Gass (lukeg) 2026-03-11 16:00:32 UTC
The test plan needs to be:

1 - Checkout main - reset_all

this bug doesn't exist in 25.11.x
Comment 6 Lucas Gass (lukeg) 2026-03-11 16:27:30 UTC
(In reply to Lucas Gass (lukeg) from comment #5)
> The test plan needs to be:
> 
> 1 - Checkout main - reset_all
> 
> this bug doesn't exist in 25.11.x

Ignore this, I am wrong. 

Checkout 25.11.x - reset_all 

you want to do this to go back to before when the bug was present.
Comment 7 Paul Derscheid 2026-03-11 16:39:35 UTC
Not sure whether this is related, it probably comes from another db update via updatedatabase, but still worth logging here:
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'me.processfee' in 'field list' at /kohadevbox/koha/Koha/Objects.pm line 401
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77

The fix for the bug here works exactly as it should.
Comment 8 Paul Derscheid 2026-03-11 16:42:29 UTC
Created attachment 195136 [details] [review]
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS

To test:
1 - Checkout 25.11.x - reset_all
2 - Enable StoreLastBorrower
3 - Issue an item
4 - Ensure item has a row in items_last_borrower
    SELECT * FROM items_last_borrower
5 - Checkout main
6 - updatedatabase
7 - Try to delete any item, doesn't have to be the one you issued
    DELETE FROM items WHERE itemnumber=93;
8 - ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`koha_kohadev`.`items_last_borrower`, CONSTRAINT `items_last_borrower_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE)

9 - repeat 1-5
10 - APPLY PATCH
11 - updatedatabase
12 - Try to delete any item
13 - Success!
14 - Delete the item you issued previously (in the staff interface on via mysql)
15 - Confirm no rows in items_last_borrower

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Paul Derscheid <paul.derscheid@lmscloud.de>