From 7bebeed02d386b3d53fd60b6abeb7b803d0a02a9 Mon Sep 17 00:00:00 2001 From: Pedro Amorim 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 (`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 --- 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 13f4bf332f..058ef04dd9 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