[Bug 21065] New: Data in accountoffsets and accountlines is deleted with the patron leaving gaps in financial reports
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Bug ID: 21065 Summary: Data in accountoffsets and accountlines is deleted with the patron leaving gaps in financial reports Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Circulation Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com The tables accountoffsets and accountlines are both "cleaned up" when a patron is deleted by using FK constraints. This is a problem because 1) The patrons have a "right to be forgotten" with GDPR and you can't refuse to delete them if their fines are paid etc. 2) Especially for partial payments and in other cases the data in accountlines and accountoffsets is neded to create reliable reports for statistics, but also for financial reports. It's not possible to determine from other tables like statistics and action_logs what fines are linked to which payment which doesn't allow to create reports by fine/fee type and similar. Instead of deleting the info, we should really just anonymize it. -- CREATE TABLE `accountoffsets` ( `borrowernumber` int(11) NOT NULL DEFAULT '0', `accountno` smallint(6) NOT NULL DEFAULT '0', `offsetaccount` smallint(6) NOT NULL DEFAULT '0', `offsetamount` decimal(28,6) DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `accountoffsets_ibfk_1` (`borrowernumber`), CONSTRAINT `accountoffsets_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `accountlines` ( `accountlines_id` int(11) NOT NULL AUTO_INCREMENT, `issue_id` int(11) DEFAULT NULL, `borrowernumber` int(11) NOT NULL DEFAULT '0', `accountno` smallint(6) NOT NULL DEFAULT '0', `itemnumber` int(11) DEFAULT NULL, `date` date DEFAULT NULL, `amount` decimal(28,6) DEFAULT NULL, `description` mediumtext COLLATE utf8_unicode_ci, `dispute` mediumtext COLLATE utf8_unicode_ci, `accounttype` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, `amountoutstanding` decimal(28,6) DEFAULT NULL, `lastincrement` decimal(28,6) DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `notify_id` int(11) NOT NULL DEFAULT '0', `notify_level` int(2) NOT NULL DEFAULT '0', `note` text COLLATE utf8_unicode_ci, `manager_id` int(11) DEFAULT NULL, PRIMARY KEY (`accountlines_id`), KEY `acctsborridx` (`borrowernumber`), KEY `timeidx` (`timestamp`), KEY `itemnumber` (`itemnumber`), CONSTRAINT `accountlines_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `accountlines_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=4666 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- 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=21065 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Data in accountoffsets and |Data in account_offsets and |accountlines is deleted |accountlines is deleted |with the patron leaving |with the patron leaving |gaps in financial reports |gaps in financial reports -- 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=21065 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> --- on delete set null? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com --- Comment #2 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- or set to anon patron? -- 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=21065 --- Comment #3 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I think this is a real evil bug - can we please prioritize it a bit with the accounts rework? It means you can't rely on reports that libraries need - like statistics on what was paid for certain fee types during a certain time period. There is no way to get this information from Koha in a reliable way, which is a real issue. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|major |blocker --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- This is a data loss bug... I am upping severity to get more attention. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 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. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 83220 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83220&action=edit Bug 21065: Add tests -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #6 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 83221 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83221&action=edit Bug 21065: Add koha_object(s)_class methods to accountline -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #7 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 83222 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83222&action=edit Bug 21065: Set ON DELETE SET NULL on accountlines.borrowernumber Note: Why do we have ON UPDATE SET NULL on accountlines.itemnumber? -- 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=21065 --- Comment #8 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 83223 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83223&action=edit Bug 21065: Update DBIC Schema change -- 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=21065 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |jonathan.druart@bugs.koha-c |ity.org |ommunity.org --- Comment #9 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- No test plan provided, on purpose. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #83223|0 |1 is obsolete| | --- Comment #10 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 83224 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83224&action=edit Bug 21065: Update DBIC Schema change -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |martin.renvoize@ptfs-europe |y.org |.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I find the ON UPDATE SET NULL odd too.. think it's a mistake and should also be CASCADE for itemnumber.. would be happy to sneek that in here. I'm currently running the entire test suit against this as if memory serves there's something odd going on with koha_object_class stuff in this area which fell out once pushed to Jenkins last time I pushed a similar change. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #83220|0 |1 is obsolete| | --- Comment #12 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 83235 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83235&action=edit Bug 21065: Add tests 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=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #83221|0 |1 is obsolete| | --- Comment #13 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 83236 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83236&action=edit Bug 21065: Add koha_object(s)_class methods to accountline 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=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #83222|0 |1 is obsolete| | --- Comment #14 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 83237 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83237&action=edit Bug 21065: Set ON DELETE SET NULL on accountlines.borrowernumber Note: Why do we have ON UPDATE SET NULL on accountlines.itemnumber? 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=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #83224|0 |1 is obsolete| | --- Comment #15 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 83238 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83238&action=edit Bug 21065: Update DBIC Schema change 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=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #16 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- It works as described and I've run the entire test suit against it. I believe this is a solid improvement to an important deficit.. Going straight for PQA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> 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=21065 --- Comment #17 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- It should, however, be noted that this action, depending on the size of your accountlines table, may take some time to run and may get executed multiple times if it is backported... RMaints, RM.. perhaps we could come up with a way of preventing this re-execution as part of backporting? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |14825 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14825 [Bug 14825] Accounts Rewrite Omnibus -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |22008 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=22008 [Bug 22008] Missing constraints on accountlines -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks|14825 | Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14825 [Bug 14825] Accounts Rewrite Omnibus -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #18 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- I think we should start using a more descriptive naming scheme for foreign keys.. instead of table_ibfk_int (where int is a running number) we should make them more descriptive and have table1_fk_table2.. or {tablename}_{columnname(s)}_{suffix} (as per postgres defaults perhaps).. that would mean in this case we could check for the old _1 key before deleting it and check for the new _table2 constraint before creating it. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #19 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Martin Renvoize from comment #18)
I think we should start using a more descriptive naming scheme for foreign keys.. instead of table_ibfk_int (where int is a running number) we should make them more descriptive and have table1_fk_table2.. or {tablename}_{columnname(s)}_{suffix} (as per postgres defaults perhaps).. that would mean in this case we could check for the old _1 key before deleting it and check for the new _table2 constraint before creating it.
Makes sense for me. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Target Milestone|--- |19.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #20 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 83366 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=83366&action=edit Bug 19850: (QA follow-up) Make the update idempotent 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=21065 --- Comment #21 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- (In reply to Martin Renvoize from comment #20)
Created attachment 83366 [details] [review] Bug 19850: (QA follow-up) Make the update idempotent
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Not much important, but the existing occurrences are either the automatic one ${table}_ibfk_$i or ${table}_${column}, not ${table}_${related_table} So I would have named it accountlines_borrowernumber. Not considering this as blocker, just noting ;) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master CC| |nick@bywatersolutions.com --- Comment #22 from Nick Clemens <nick@bywatersolutions.com> --- Awesome work all! Pushed ot master for 19.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #23 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Pushed to 18.11.x for 18.11.01 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com --- Comment #24 from Lucas Gass <lucas@bywatersolutions.com> --- backported to 18.05 for 18.05.08 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=22052 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |cmurdock@ccfls.org --- Comment #25 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- *** Bug 18323 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=21065 Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |caroline.cyr-la-rose@inlibr | |o.com --- Comment #26 from Caroline Cyr La Rose <caroline.cyr-la-rose@inlibro.com> --- Hello everyone! I noticed this bug was pushed but the wrong one is mentioned in the release notes... https://gitlab.com/koha-community/koha-release-notes/blob/master/release_not... Under Enhancements > Acquisitions it says that bug [19850] Enhance invoicing functionality for each line item is pushed, but there is nothing so far in this bug. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #27 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Caroline Cyr La Rose from comment #26)
Hello everyone! I noticed this bug was pushed but the wrong one is mentioned in the release notes...
https://gitlab.com/koha-community/koha-release-notes/blob/master/ release_notes_18_12_00.md
Under Enhancements > Acquisitions it says that bug [19850] Enhance invoicing functionality for each line item is pushed, but there is nothing so far in this bug.
I think it might have happened here: http://git.koha-community.org/gitweb/?p=koha.git&a=search&h=6d44f9dac8235dfe0259ea5b935c5a8908d2e441&st=commit&s=21065 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Resolution|--- |FIXED Status|Pushed to Stable |RESOLVED --- Comment #28 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- Sorry I missed this one for 17.11.x I prefer not push now at branch end life. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #29 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Did I mess up here.. should we be replacing the query to INFORMATION SCHEMA with foreign_key_exists? Jonathan Druart, I'd appreciate your thoughts here... I'm happy to code it up as a followup or a new bug. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21065 --- Comment #30 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- (In reply to Martin Renvoize from comment #29)
Did I mess up here.. should we be replacing the query to INFORMATION SCHEMA with foreign_key_exists?
Jonathan Druart, I'd appreciate your thoughts here... I'm happy to code it up as a followup or a new bug.
I do not think so. We are dropping the FK to recreate it with a different ON DELETE clause value. So we could have used foreign_key_exists but rename the FK. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org