From 4cdb3a57db739c8b5ad672444b02b333ef914762 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 12 Jul 2023 14:09:35 +0000 Subject: [PATCH] Bug 33671: (follow-up) Disable FK check for column change See also BZ report. As suggested by Tomas. NOTE: The dbrev is extended too for removing records with a failing guarantor_id foreign key (theoretically not present obviously). Test plan: 1) cp installer/data/mysql/db_revs/220600048.pl installer/data/mysql/atomicupdate/ 2) Run updatedatabase.pl 3) Remove copied file Bonus: Manually remove FK constraint before running updatedatabase. Insert a record with bad guarantor_id into relationships manually. Run the update again. You should not see the message that the constraint is removed. Is the bad record count reported and record removed? Signed-off-by: Marcel de Rooy [EDIT] Simplified. No longer removes the FK. Just disables during ALTER. Signed-off-by: Martin Renvoize Signed-off-by: Tomas Cohen Arazi (cherry picked from commit 0397251ece455801d5b3083feb84cf959cfcea83) Signed-off-by: Martin Renvoize --- installer/data/mysql/db_revs/220600048.pl | 13 +++++++++---- 1 file changed, 9 insertions(+), 4 deletions(-) diff --git a/installer/data/mysql/db_revs/220600048.pl b/installer/data/mysql/db_revs/220600048.pl index 8fb5e9c322..7f59d65996 100755 --- a/installer/data/mysql/db_revs/220600048.pl +++ b/installer/data/mysql/db_revs/220600048.pl @@ -1,18 +1,23 @@ use Modern::Perl; return { - bug_number => 30472, + bug_number => 30472, # adjusted on bug 33671 description => "borrower_relationships.guarantor_id NOT NULL", up => sub { my ($args) = @_; my ($dbh, $out) = @$args{qw(dbh out)}; - # Delete 'empty' guarantors. No longer possible to add them via interface. Have no use. - $dbh->do(q{ -DELETE FROM borrower_relationships WHERE guarantor_id IS NULL + # Delete bad/empty guarantors. No longer possible to add them via interface. Have no use. + my $cnt = $dbh->do(q{ +DELETE borrower_relationships FROM borrower_relationships LEFT JOIN borrowers bo ON bo.borrowernumber=guarantor_id WHERE guarantor_id IS NULL OR bo.borrowernumber IS NULL; }); + say $out "Removed $cnt bad guarantor relationship records" if $cnt && $cnt =~ /^\d+$/; + + # Make column NOT NULL, disable FK checks while doing so + $dbh->do('SET FOREIGN_KEY_CHECKS=0'); $dbh->do(q{ ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL }); + $dbh->do('SET FOREIGN_KEY_CHECKS=1'); }, }; -- 2.39.5