[Bug 42068] New: Update on bug 26993 breaks items deletions with a corrupted foreign key
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Bug ID: 42068 Summary: Update on bug 26993 breaks items deletions with a corrupted foreign key Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: blocker Priority: P5 - low Component: Architecture, internals, and plumbing Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org 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; -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 --- Comment #1 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- *** Bug 42067 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |26993 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26993 [Bug 26993] Allow StoreLastBorrower to retain a locally-defined number of previous borrowers -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 --- Comment #2 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 195123 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195123&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 --- Comment #3 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195125 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195125&action=edit Bug 42068: Update on bug 26993 breaks items deletions with a corrupted foreign key On bug 26993 we turned off foreign key checks to remove a unique constraint, then enabled them Test plan: 1) Run the db update for bug 42068 2) Attempt to delete an item from Koha or via mysql 3) Note the error 4) Reset your database to pre 42068 5) Apply this patch 6) Run the db update for bug 42068 7) Note you can delete items again! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195125|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195123|0 |1 is obsolete| | --- Comment #4 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 195132 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195132&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #5 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- The test plan needs to be: 1 - Checkout main - reset_all this bug doesn't exist in 25.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 --- Comment #6 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|kyle@bywatersolutions.com |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |paul.derscheid@lmscloud.de --- Comment #7 from Paul Derscheid <paul.derscheid@lmscloud.de> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195132|0 |1 is obsolete| | --- Comment #8 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Created attachment 195136 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195136&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.05.00 released in| | --- Comment #9 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Pushed to main for 26.05, thanks all! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42068 Jacob O'Mara <jacob.omara@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jacob.omara@openfifth.co.uk Status|Pushed to main |Needs documenting --- Comment #10 from Jacob O'Mara <jacob.omara@openfifth.co.uk> --- Dep not pushed, not backporting. Scream at me if you feel strongly it should be backported. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org