Bug 42068

Summary: Update on bug 26993 breaks items deletions with a corrupted foreign key
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Architecture, internals, and plumbingAssignee: Nick Clemens (kidclamp) <nick>
Status: Passed QA --- QA Contact: Testopia <testopia>
Severity: blocker    
Priority: P5 - low CC: kyle, lucas, paul.derscheid
Version: Main   
Hardware: All   
OS: All   
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:
Bug Depends on: 26993    
Bug Blocks:    
Attachments: Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS
Bug 42068: Update on bug 26993 breaks items deletions with a corrupted foreign key
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS
Bug 42068: Bug 26993 (follow-up) Don't use FOREIGN_KEY_CHECKS

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>