Bugzilla – Attachment 182919 Details for
Bug 40057
Database update 24.12.00.017 fails if old ILL data points to non-existent borrowernumber
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 40057: Fix db revision
Bug-40057-Fix-db-revision.patch (text/plain), 2.26 KB, created by
Pedro Amorim
on 2025-06-03 11:20:53 UTC
(
hide
)
Description:
Bug 40057: Fix db revision
Filename:
MIME Type:
Creator:
Pedro Amorim
Created:
2025-06-03 11:20:53 UTC
Size:
2.26 KB
patch
obsolete
>From 16963aebcc57b6ca32c82b65433c9204933aa9c6 Mon Sep 17 00:00:00 2001 >From: Pedro Amorim <pedro.amorim@openfifth.co.uk> >Date: Tue, 3 Jun 2025 11:17:15 +0000 >Subject: [PATCH] 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 >--- > installer/data/mysql/db_revs/241200017.pl | 16 ++++++++++++++++ > 1 file changed, 16 insertions(+) > >diff --git a/installer/data/mysql/db_revs/241200017.pl b/installer/data/mysql/db_revs/241200017.pl >index 13f4bf332fc..058ef04dd90 100755 >--- a/installer/data/mysql/db_revs/241200017.pl >+++ b/installer/data/mysql/db_revs/241200017.pl >@@ -8,6 +8,22 @@ return { > my ($args) = @_; > my ( $dbh, $out ) = @$args{qw(dbh out)}; > >+ my $sth = $dbh->prepare( >+ q{ >+ SELECT COUNT(*) FROM illrequests WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) >+ } >+ ); >+ $sth->execute; >+ my $count = $sth->fetchrow_array; >+ >+ if ($count) { >+ say_warning( $out, "Found $count illrequests with a borrowernumber that doesn't exist in borrowers" ); >+ $dbh->do( >+ q{ UPDATE illrequests SET borrowernumber = NULL WHERE borrowernumber NOT IN (SELECT borrowernumber FROM borrowers) } >+ ); >+ say_info( $out, "Set those borrowernumbers to NULL" ); >+ } >+ > $dbh->do(q{ ALTER TABLE illrequests DROP FOREIGN KEY illrequests_bnfk }); > $dbh->do( > q{ >-- >2.39.5
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 40057
:
182919
|
182988
|
183013
|
183038