[Bug 24658] New: Deleting items with fines does not update itemnumber in accountlines to NULL causing ISE
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Bug ID: 24658 Summary: Deleting items with fines does not update itemnumber in accountlines to NULL causing ISE Change sponsored?: --- Product: Koha Version: 19.05 Hardware: All OS: All Status: NEW Severity: minor Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: addie@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org When batch deleting items through the staff client, the itemnumber in the accountlines table remains and does update to NULL. If you then go into the Accountlines tab of a borrower and into the Transactions you will get an internal server error if there is a fine in that patrons accountlines with an itemnumber in the database for the deleted item -- 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=24658 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com Assignee|koha-bugs@lists.koha-commun |addie@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=24658 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org --- Comment #1 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Hi Addie, which error do you get? I do not recreate (19.05.x and master), can you give more detail please? What I did: * Create manual invoice, barcode=39999000018714, amount=42 * Delete the item with barcode=39999000018714 (from cataloguing/additem.pl) * Accounting > Transactions I see: https://snipboard.io/8GNMjB.jpg -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #2 from Addie <addie@bywatersolutions.com> --- The process was: Using the batch item deletion tool, deleted items by barcode numbers. Then if you go to a patron account where a fine was at any point associated with those items, you would get a white page with black "internal server error" in the left corner of the page.
From the database side, the itemnumber of those items remained in the accountlines for associated fines.
-- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #3 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Which exact version of Koha? Do you recreate the problem on the latest 19.05.x? On master? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #4 from Addie <addie@bywatersolutions.com> --- This happened either on 19.05.05 or 19.05.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Tested on 19.05.06: * Create manual invoice, barcode=39999000018714, amount=12 * Create manual invoice, barcode=39999000018653, amount=23. Then pay it * Delete the 2 items * Accounting > Transactions => No crash You need to provide more detail on the error you have in the log, or what kind of transaction was associated to the item. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Julie <julie@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |julie@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Cindy Murdock Ames <cmurdock@ccfls.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |cmurdock@ccfls.org --- Comment #6 from Cindy Murdock Ames <cmurdock@ccfls.org> --- I am also seeing this on 19.11. My plack-error.log shows the error below when it occurs, and if I change the itemnumber to NULL for the deleted item's fine in accountlines the ISE doesn't happen: Template process failed: undef error - DBIC result _type isn't of the _type Biblio at /usr/share/koha/intranet/htdocs/intranet-tmpl/prog/en/modules/members/pay.tt line 96. This is line 96 from pay.tt: [% IF line.itemnumber %] <a href="/cgi-bin/koha/catalogue/moredetail.pl?itemnumber=[% line.itemnumber | uri %]&biblionumber=[% line.item.biblionumber | uri %]#item[% line.itemnumber | uri %]">[% line.item.barcode | html %]</a> I am not sure what method staff used when deleting the item, but since it was a magazine I suspect they used the batch deletion tool. Wouldn't it be better to add some error handling in pay.tt so it doesn't fail if it can't find the record it's trying to link to? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #7 from Cindy Murdock Ames <cmurdock@ccfls.org> --- Note however that I'm not sure when this item was deleted, and we upgraded to 19.11 from 3.20 in April, and the fine was generated in December 2019. So I'm not sure this is still problematic for newly deleted items. If you'd like me to do further testing on 19.11 I'd be happy to. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Hm, we'd need to do some testing here, but I'd argue we should not set it to NULL but instead keep it and make it not explode (the deleteditems information might still be useful) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #9 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- There is a FK on accountlines.itemnumber, and apparently it is there for a long time now. Can you confirm that this constraint does not exist in your database? "show create table accountlines;" will tell you if there is a CONSTRAINT on items.itemnumber -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #10 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #9)
There is a FK on accountlines.itemnumber, and apparently it is there for a long time now.
Can you confirm that this constraint does not exist in your database?
"show create table accountlines;" will tell you if there is a CONSTRAINT on items.itemnumber
The update that added this constraint: if ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_2' ) && !foreign_key_exists( 'accountlines', 'accountlines_ibfk_items' ) ) { $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2"); $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE"); } It looks like if you don't have the original constraint you don't get the new one, perhaps we could do: if ( foreign_key_exists( 'accountlines', 'accountlines_ibfk_2' ) { $dbh->do("ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2"); } unless( foreign_key_exists( 'accountlines', 'accountlines_ibfk_items' ) ){ $dbh->do("ALTER TABLE accountlines ADD CONSTRAINT `accountlines_ibfk_items` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE"); } -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |22008 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22008 [Bug 22008] accountlines.manager_id is missing a foreign key constraint -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #11 from Cindy Murdock Ames <cmurdock@ccfls.org> --- (In reply to Jonathan Druart from comment #9)
There is a FK on accountlines.itemnumber, and apparently it is there for a long time now.
Can you confirm that this constraint does not exist in your database?
"show create table accountlines;" will tell you if there is a CONSTRAINT on items.itemnumber
It looks to me like mine doesn't have that constraint. Here's my output: accountlines | CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) DEFAULT NULL, `borrowernumber` int(11) DEFAULT NULL, `itemnumber` int(11) DEFAULT NULL, `date` date DEFAULT NULL, `amount` decimal(28,6) DEFAULT NULL, `description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `credit_type_code` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `debit_type_code` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `status` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `payment_type` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `amountoutstanding` decimal(28,6) DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `note` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `manager_id` int(11) DEFAULT NULL, `register_id` int(11) DEFAULT NULL, `interface` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, `branchcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), KEY `accountlines_ibfk_branches` (`branchcode`), KEY `accountlines_ibfk_borrowers_2` (`manager_id`), KEY `accountlines_ibfk_registers` (`register_id`), KEY `accountlines_ibfk_debit_type` (`debit_type_code`), KEY `accountlines_ibfk_credit_type` (`credit_type_code`), CONSTRAINT `accountlines_ibfk_borrowers_2` FOREIGN KEY (`manager_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_branches` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_credit_type` FOREIGN KEY (`credit_type_code`) REFERENCES `account_credit_types` (`code`) ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_debit_type` FOREIGN KEY (`debit_type_code`) REFERENCES `account_debit_types` (`code`) ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_registers` FOREIGN KEY (`register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=469046 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=26947 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Jonathan Druart <jonathan.druart@bugs.koha-community.org> 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=24658 --- Comment #12 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 113735 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=113735&action=edit Bug 24658: (bug 22008 follow-up) Correct update DB entry 18.12.00.031 If the original constraint does not exist, the second one is not added. Test plan: 1. checkout dbrev 18.12.00.030 2. Remove the constraint: ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2 3. checkout master 4. % updatedatabase => The constraint accountlines_ibfk_items is not added 5. Repeat 1 to 4 => The constraint is added correctly -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|addie@bywatersolutions.com |jonathan.druart@bugs.koha-c | |ommunity.org Version|19.05 |master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Kyle M Hall <kyle@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=24658 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #113735|0 |1 is obsolete| | --- Comment #13 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 114139 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=114139&action=edit Bug 24658: (bug 22008 follow-up) Correct update DB entry 18.12.00.031 If the original constraint does not exist, the second one is not added. Test plan: 1. checkout dbrev 18.12.00.030 2. Remove the constraint: ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2 3. checkout master 4. % updatedatabase => The constraint accountlines_ibfk_items is not added 5. Repeat 1 to 4 => The constraint is added correctly Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com Severity|minor |critical --- Comment #14 from Kyle M Hall <kyle@bywatersolutions.com> --- Bumping the severity. This bug do to the nature of the bug causing ISEs that make some actions impossible. No data loss so not a blocker. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #114139|0 |1 is obsolete| | --- Comment #15 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 114237 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=114237&action=edit Bug 24658: (bug 22008 follow-up) Correct update DB entry 18.12.00.031 If the original constraint does not exist, the second one is not added. Test plan: 1. checkout dbrev 18.12.00.030 2. Remove the constraint: ALTER TABLE accountlines DROP FOREIGN KEY accountlines_ibfk_2 3. checkout master 4. % updatedatabase => The constraint accountlines_ibfk_items is not added 5. Repeat 1 to 4 => The constraint is added correctly Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com Status|Signed Off |Passed QA --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- This is a good retrospective fix.. I'm wondering if we ought to also add a further DB update to catch cases where it wasn't caught but their already past that original upgrade? Passing QA and leaving the RM to decide whether that followup is needed. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #17 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- (In reply to Martin Renvoize from comment #16)
This is a good retrospective fix.. I'm wondering if we ought to also add a further DB update to catch cases where it wasn't caught but their already past that original upgrade?
Passing QA and leaving the RM to decide whether that followup is needed.
I am not sure we need it. If people get the 500 they will find this bug report with the SQL query that is needed to add the missing constraint. I can still add it later if too many people is facing it. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |21.05.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 --- Comment #18 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Pushed to master for 21.05, thanks to everybody involved! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Version(s)|21.05.00 |21.05.00,20.11.01 released in| | Status|Pushed to master |Pushed to stable --- Comment #19 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to 20.11.x for 20.11.01 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|21.05.00,20.11.01 |21.05.00,20.11.01,20.05.07 released in| | Status|Pushed to stable |Pushed to oldstable CC| |andrew@bywatersolutions.com --- Comment #20 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- Pushed to 20.05.x for 20.05.07 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24658 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org