Bugzilla – Attachment 149235 Details for
Bug 32399
Database update for bug 30483 is failing
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 32399: [22.05.x] Remove FK constraints while changing columns
Bug-32399-2205x-Remove-FK-constraints-while-changi.patch (text/plain), 2.76 KB, created by
Lucas Gass (lukeg)
on 2023-04-06 14:35:09 UTC
(
hide
)
Description:
Bug 32399: [22.05.x] Remove FK constraints while changing columns
Filename:
MIME Type:
Creator:
Lucas Gass (lukeg)
Created:
2023-04-06 14:35:09 UTC
Size:
2.76 KB
patch
obsolete
>From 68a59ec1b11f024fa58f77678c779bc947fd4d9c Mon Sep 17 00:00:00 2001 >From: Lucas Gass <lucas@bywatersolutions.com> >Date: Thu, 6 Apr 2023 14:34:26 +0000 >Subject: [PATCH] Bug 32399: [22.05.x] Remove FK constraints while changing > columns > >--- > installer/data/mysql/db_revs/220506001.pl | 26 +++++++++++++++++++++++ > 1 file changed, 26 insertions(+) > >diff --git a/installer/data/mysql/db_revs/220506001.pl b/installer/data/mysql/db_revs/220506001.pl >index 53eeae9962..626c5b7e25 100755 >--- a/installer/data/mysql/db_revs/220506001.pl >+++ b/installer/data/mysql/db_revs/220506001.pl >@@ -6,6 +6,32 @@ return { > up => sub { > my ($args) = @_; > my ($dbh, $out) = @$args{qw(dbh out)}; >+ >+ # Count bad records (expecting zero..) >+ my ( $cnt ) = $dbh->selectrow_array(q{ >+ SELECT COUNT(issue_id) FROM issues >+ LEFT JOIN borrowers USING (borrowernumber) >+ LEFT JOIN items USING (itemnumber) >+ WHERE items.itemnumber IS NULL OR borrowers.borrowernumber IS NULL }); >+ >+ # If we found bad records, we will not continue >+ if( $cnt ) { >+ say $out "ERROR: Your issues table contains $cnt records violating foreign key constraints to the borrowers or items table."; >+ say $out "We recommend to remove them with a statement like:"; >+ say $out " DELETE issues FROM issues LEFT JOIN borrowers bo USING (borrowernumber) WHERE bo.borrowernumber IS NULL;"; >+ say $out " DELETE issues FROM issues LEFT JOIN items it USING (itemnumber) WHERE it.itemnumber IS NULL;"; >+ die "Interrupting installer process: database revision for bug 30483 fails!"; >+ } >+ >+ # Green zone: remove FK constraints while changing columns (needed for some SQL server versions) >+ if( foreign_key_exists('issues', 'issues_ibfk_1') ) { >+ $dbh->do( q|ALTER TABLE issues DROP FOREIGN KEY issues_ibfk_1| ); >+ } >+ if( foreign_key_exists('issues', 'issues_ibfk_2') ) { >+ $dbh->do( q|ALTER TABLE issues DROP FOREIGN KEY issues_ibfk_2| ); >+ } >+ $dbh->do( q|ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE RESTRICT ON UPDATE CASCADE| ); >+ $dbh->do( q|ALTER TABLE issues ADD CONSTRAINT `issues_ibfk_2` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE RESTRICT ON UPDATE CASCADE| ); > my $sql = "ALTER TABLE issues MODIFY COLUMN borrowernumber int(11) NOT NULL COMMENT 'foreign key, linking this to the borrowers table for the patron this item was checked out to', MODIFY COLUMN itemnumber int(11) NOT NULL COMMENT 'foreign key, linking this to the items table for the item that was checked out'"; > $dbh->do($sql); > }, >-- >2.30.2
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 32399
:
144428
|
144448
|
144516
|
144616
| 149235