From 668311e7fcaf412c1c503e006c6be65005fe2ce1 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 (`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
---
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