From d41a9d2bb2da29bdc8414cfa426772e8a308ffb5 Mon Sep 17 00:00:00 2001 From: Tomas Cohen Arazi Date: Mon, 25 Apr 2022 10:09:41 -0300 Subject: [PATCH] Bug 30449: (QA follow-up) Report back on problematic situations Signed-off-by: Tomas Cohen Arazi Signed-off-by: Martin Renvoize Signed-off-by: Fridolin Somers Signed-off-by: Kyle M Hall --- .../data/mysql/atomicupdate/bug_30449.pl | 42 +++++++++++++++++-- 1 file changed, 38 insertions(+), 4 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_30449.pl b/installer/data/mysql/atomicupdate/bug_30449.pl index 316f4016fa..b9c2f73eb5 100755 --- a/installer/data/mysql/atomicupdate/bug_30449.pl +++ b/installer/data/mysql/atomicupdate/bug_30449.pl @@ -13,11 +13,45 @@ return { $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| ); } } - if( !foreign_key_exists('borrower_attribute_types', 'borrower_attribute_types_ibfk_1') ) { - if( !index_exists('borrower_attribute_types', 'category_code') ) { - $dbh->do( q|ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code)| ); + + if ( + !foreign_key_exists( + 'borrower_attribute_types', 'borrower_attribute_types_ibfk_1' + ) + ) + { + + my $sth = $dbh->prepare( + q{ + SELECT category_code + FROM borrower_attribute_types + WHERE category_code NOT IN (SELECT categorycode FROM categories); + } + ); + + $sth->execute; + + my @invalid_categories; + while ( my $row = $sth->fetchrow_arrayref() ) { + push( @invalid_categories, $row->[0] ); + } + + if (@invalid_categories) { + die "The 'borrower_attribute_types' table contains " + . "references to invalid category codes: " + . join( ', ', @invalid_categories ); + } + + if ( !index_exists( 'borrower_attribute_types', 'category_code' ) ) + { + $dbh->do(q| + ALTER TABLE borrower_attribute_types ADD INDEX category_code (category_code) + |); } - $dbh->do( q|ALTER TABLE borrower_attribute_types ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`)| ); + $dbh->do(q| + ALTER TABLE borrower_attribute_types + ADD CONSTRAINT borrower_attribute_types_ibfk_1 FOREIGN KEY (`category_code`) REFERENCES `categories` (`categorycode`) + |); } }, }; -- 2.39.5