Bugzilla – Attachment 144516 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: Remove FK constraints while changing columns
Bug-32399-Remove-FK-constraints-while-changing-col.patch (text/plain), 3.38 KB, created by
Martin Renvoize (ashimema)
on 2022-12-09 07:35:40 UTC
(
hide
)
Description:
Bug 32399: Remove FK constraints while changing columns
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2022-12-09 07:35:40 UTC
Size:
3.38 KB
patch
obsolete
>From 9dabe1c0524cc16287c6253f92c4a64ead2119c3 Mon Sep 17 00:00:00 2001 >From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Date: Mon, 5 Dec 2022 09:18:04 +0000 >Subject: [PATCH] Bug 32399: Remove FK constraints while changing columns > >Some users have reported that the dbrev of bug 30483 fails. This might >depend on bad data, SQL server version, etc. >We now remove the constraints temporarily. > >Test plan: >Reset your version in order to pass this dbrev again. So below >22.06.00.041. >* update systempreferences set value='22.0600040' where variable = 'Version'; >Run updatedatabase. > >Bonus: Create an issues record with invalid itemnumber (set FK check >off when doing so). Verify that dbrev fails and installer stops. > >Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> >Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> >--- > installer/data/mysql/db_revs/220600041.pl | 26 +++++++++++++++++++++++ > 1 file changed, 26 insertions(+) > >diff --git a/installer/data/mysql/db_revs/220600041.pl b/installer/data/mysql/db_revs/220600041.pl >index 1d3f498f4e8..21731f3f922 100755 >--- a/installer/data/mysql/db_revs/220600041.pl >+++ b/installer/data/mysql/db_revs/220600041.pl >@@ -6,10 +6,36 @@ 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 > 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( 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| ); > }, > }; >-- >2.38.1
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