[Bug 40057] New: Database update 24.12.00.017 fails if old ILL data points to non-existent borrowernumber
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Bug ID: 40057 Summary: Database update 24.12.00.017 fails if old ILL data points to non-existent borrowernumber Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: ILL Assignee: koha-bugs@lists.koha-community.org Reporter: pedro.amorim@openfifth.co.uk QA Contact: testopia@bugs.koha-community.org CC: pedro.amorim@openfifth.co.uk, tomascohen@gmail.com -- 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=40057 Pedro Amorim <pedro.amorim@openfifth.co.uk> 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=40057 --- Comment #1 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 182919 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182919&action=edit Bug 40057: Fix db revision Test plan (ktd): 1) reset_all 2) Run first part of the 241200017.pl atomicupdate manually koha-mysql kohadev ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk; 3) Create an ILL request linked to a borrower that doesn't exist (simulate old data): INSERT INTO illrequests (, , ) VALUES ('Standard', '99999', 'CPL'); 4) Run the 2nd part of the atomicupdate manually: ALTER TABLE illrequests ADD CONSTRAINT illrequests_bnfk FOREIGN KEY() REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE; 5) Notice you get the error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (., CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE) 6) Run the added query in the patch: UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers); 7) Repeat step 4) 8) Notice there's no errors -- 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=40057 --- Comment #2 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- We've had this come up twice now on some of our upgrades. Some databases have very old ILL requests where 'borrowernumber' has a value of a borrowernumber that doesn't exist. I don't know exactly how the data ended up this way, but it's happened twice now so this db revision fix is needed. -- 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=40057 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_05_candidate -- 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=40057 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |pedro.amorim@openfifth.co.u |ity.org |k CC| |martin.renvoize@openfifth.c | |o.uk -- 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=40057 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |major -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |32630 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32630 [Bug 32630] Don't delete ILL requests when patron is deleted -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #3 from David Nind <david@davidnind.com> --- I get this error with step 2: INSERT INTO illrequests (, , ) VALUES ('Standard', '99999', 'CPL'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' , ) VALUES ('Standard', '99999', 'CPL')' at line 1 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #182919|0 |1 is obsolete| | --- Comment #4 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- Created attachment 182988 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=182988&action=edit Bug 40057: Fix db revision Test plan (ktd): 1) reset_all 2) Run first part of the 241200017.pl atomicupdate manually koha-mysql kohadev ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk; 3) Create an ILL request linked to a borrower that doesn't exist (simulate old data): INSERT INTO illrequests (`backend`, `borrowernumber`,`branchcode` ) VALUES ('Standard', '99999', 'CPL'); 4) Run the 2nd part of the atomicupdate manually: ALTER TABLE illrequests ADD CONSTRAINT illrequests_bnfk FOREIGN KEY() REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE; 5) Notice you get the error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (., CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE) 6) Run the added query in the patch: UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers); 7) Repeat step 4) 8) Notice there's no errors -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 --- Comment #5 from Pedro Amorim <pedro.amorim@openfifth.co.uk> --- (In reply to David Nind from comment #3)
I get this error with step 2:
INSERT INTO illrequests (, , ) VALUES ('Standard', '99999', 'CPL'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' , ) VALUES ('Standard', '99999', 'CPL')' at line 1
Thank you David. It appears the column names got filtered out of the commit message! I've corrected and resubmitted. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 David Nind <david@davidnind.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=40057 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #182988|0 |1 is obsolete| | --- Comment #6 from David Nind <david@davidnind.com> --- Created attachment 183013 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=183013&action=edit Bug 40057: Fix db revision Test plan (ktd): 1) reset_all 2) Run first part of the 241200017.pl atomicupdate manually koha-mysql kohadev ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk; 3) Create an ILL request linked to a borrower that doesn't exist (simulate old data): INSERT INTO illrequests (`backend`, `borrowernumber`,`branchcode` ) VALUES ('Standard', '99999', 'CPL'); 4) Run the 2nd part of the atomicupdate manually: ALTER TABLE illrequests ADD CONSTRAINT illrequests_bnfk FOREIGN KEY(`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE; 5) Notice you get the error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (., CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE) 6) Run the added query in the patch: UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers); 7) Repeat step 4) 8) Notice there's no errors Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 --- Comment #7 from David Nind <david@davidnind.com> --- (In reply to Pedro Amorim from comment #5)
(In reply to David Nind from comment #3)
I get this error with step 2:
INSERT INTO illrequests (, , ) VALUES ('Standard', '99999', 'CPL'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' , ) VALUES ('Standard', '99999', 'CPL')' at line 1
Thank you David. It appears the column names got filtered out of the commit message! I've corrected and resubmitted.
Thanks Pedro! I amended step 4 of the patch commit message, as the columns were missing as well. Testing notes (using KTD): 1. After running through the test plan, I applied the patch and added my sign-off. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This fixes a database release notes| |update related to ILL | |requests, for bug 32630 - | |Don't delete ILL requests | |when patron is deleted, | |added in Koha 25.05. | | | |Background: Some databases | |have very old ILL requests | |where 'borrowernumber' has | |a value of a borrowernumber | |that doesn't exist. We're | |not exactly how the data | |ended up this way, but it's | |happened at least twice now | |for one provider. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA 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=40057 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #183013|0 |1 is obsolete| | --- Comment #8 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 183038 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=183038&action=edit Bug 40057: Fix db revision Test plan (ktd): 1) reset_all 2) Run first part of the 241200017.pl atomicupdate manually koha-mysql kohadev ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk; 3) Create an ILL request linked to a borrower that doesn't exist (simulate old data): INSERT INTO illrequests (`backend`, `borrowernumber`,`branchcode` ) VALUES ('Standard', '99999', 'CPL'); 4) Run the 2nd part of the atomicupdate manually: ALTER TABLE illrequests ADD CONSTRAINT illrequests_bnfk FOREIGN KEY(`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE; 5) Notice you get the error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (., CONSTRAINT FOREIGN KEY () REFERENCES () ON DELETE SET NULL ON UPDATE CASCADE) 6) Run the added query in the patch: UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers); 7) Repeat step 4) 8) Notice there's no errors Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_25_05_candidate | CC| |m.de.rooy@rijksmuseum.nl --- Comment #9 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Just curious SELECT COUNT(*) FROM illrequests WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) Wouldnt a join on borrowers with a NULL test be better? No big deal. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |25.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 --- Comment #10 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 25.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Pedro Amorim <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_05_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com --- Comment #11 from Jonathan Druart <jonathan.druart@gmail.com> --- (In reply to Marcel de Rooy from comment #9)
Just curious SELECT COUNT(*) FROM illrequests WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) Wouldnt a join on borrowers with a NULL test be better? No big deal.
I came here to say that. This is really not the best we can do. The following query using LEFT JOIN should work: UPDATE illrequests AS ill LEFT JOIN borrowers AS b ON ill.borrowernumber = b.borrowernumber SET ill.borrowernumber = NULL WHERE b.borrowernumber IS NULL; -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00 |25.11.00,25.05.01 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 --- Comment #12 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Nice work everyone! Pushed to 25.05.x for 25.05.03 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Baptiste Wojtkowski (bwoj) <baptiste.wojtkowski@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|25.11.00,25.05.01 |25.11.00,25.05.01,24.11.07 released in| | Status|Pushed to stable |Pushed to oldstable CC| |baptiste.wojtkowski@biblibr | |e.com --- Comment #13 from Baptiste Wojtkowski (bwoj) <baptiste.wojtkowski@biblibre.com> --- Pushed to 24.11.x for 24.11.07 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_25_05_candidate | CC| |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=40057 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldstable |Needs documenting CC| |wainuiwitikapark@catalyst.n | |et.nz --- Comment #14 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Not backporting to 22.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |FIXED Status|Needs documenting |RESOLVED --- Comment #15 from David Nind <david@davidnind.com> --- No changes required to the manual for this change. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40057 Bug 40057 depends on bug 32630, which changed state. Bug 32630 Summary: Don't delete ILL requests when patron is deleted https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32630 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org