From 90f43eaf9932533fa7074d32ffffab6107c5b82a 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) Drop FK constraint during column change Content-Type: text/plain; charset=utf-8 See also BZ report. 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. nsert 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 --- installer/data/mysql/db_revs/220600048.pl | 21 ++++++++++++++++++--- 1 file changed, 18 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/db_revs/220600048.pl b/installer/data/mysql/db_revs/220600048.pl index 8fb5e9c322..3527c12f44 100755 --- a/installer/data/mysql/db_revs/220600048.pl +++ b/installer/data/mysql/db_revs/220600048.pl @@ -7,12 +7,27 @@ return { 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 + # Remove constraint at start + if ( foreign_key_exists('borrower_relationships', 'r_guarantor') ) { + $dbh->do(q{ +ALTER TABLE borrower_relationships DROP FOREIGN KEY `r_guarantor` + }); + say $out "Temporarily removing FK constraint on guarantor_id"; + } + + # 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 and add constraint back $dbh->do(q{ ALTER TABLE borrower_relationships CHANGE COLUMN guarantor_id guarantor_id int(11) NOT NULL }); + $dbh->do(q{ +ALTER TABLE borrower_relationships ADD FOREIGN KEY `r_guarantor` (guarantor_id) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE + }); + say $out "Changed column guarantor_id and added FK constraint on guarantor_id back"; }, }; -- 2.30.2