From 5f4b24e97494c92b09e35247a31bdf5611c01091 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Mon, 5 Dec 2022 09:18:04 +0000 Subject: [PATCH] Bug 32399: Unset foreign key checks while changing issues constraint Content-Type: text/plain; charset=utf-8 Some users have reported that the dbrev of bug 30483 fails. This might depend on bad data, SQL server version, etc. The easiest workaround is now to unset the check temporarily. Maybe we should also warn for issues without itemnumber or borrowernumber on About? Or remove them? 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 prints warning about it. Signed-off-by: Marcel de Rooy --- installer/data/mysql/db_revs/220600041.pl | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/installer/data/mysql/db_revs/220600041.pl b/installer/data/mysql/db_revs/220600041.pl index 1d3f498f4e..f7a9133885 100755 --- a/installer/data/mysql/db_revs/220600041.pl +++ b/installer/data/mysql/db_revs/220600041.pl @@ -6,10 +6,19 @@ 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 }); + $dbh->do('SET FOREIGN_KEY_CHECKS=0'); $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('SET FOREIGN_KEY_CHECKS=1'); + say $out "WARNING: Your issues table contains $cnt records with invalid borrowernumber or itemnumber. Please correct/remove them." if $cnt; }, }; -- 2.30.2