Attachment 153367 Details for Bug 33671 – Bug 33671: (follow-up) Drop FK constraint during column change

[patch] Bug 33671: (follow-up) Drop FK constraint during column change

Bug-33671-follow-up-Drop-FK-constraint-during-colu.patch (text/plain), 2.57 KB, created by Marcel de Rooy on 2023-07-12 14:36:54 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2023-07-12 14:36:54 UTC

Size: 2.57 KB

patch

obsolete

>From 90f43eaf9932533fa7074d32ffffab6107c5b82a Mon Sep 17 00:00:00 2001
>From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>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 <m.de.rooy@rijksmuseum.nl>
>---
> 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

Actions: View | Diff | Splinter Review

Attachments on bug 33671: 150629 | 150630 | 150824 | 150825 | 153367 | 153533 | 153536