From ff02d16b57af8459c01d9bc8944b54b44c34458c Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Tue, 7 Jun 2022 14:31:39 +0000 Subject: [PATCH] Bug 30899: [21.11.x] Follow-up for dbrev 211200041 from bug 30449 Content-Type: text/plain; charset=utf-8 Replace DROP CONSTRAINT by DROP FOREIGN KEY in old dbrev, and submit a new dbrev to fix cases where dbrev went wrong. Test plan: NOTE: When you check show create table, verify that you see an index for category_code and a foreign key called borrower_attribute_types_ibfk_1. [1] run updatedatabase; check show create table [2] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; alter table borrower_attribute_types drop index category_code; RUN updatedatabase again; check show create table [3] alter table borrower_attribute_types drop foreign key borrower_attribute_types_ibfk_1; ALTER TABLE borrower_attribute_types ADD CONSTRAINT category_code_fk FOREIGN KEY (category_code) REFERENCES categories(categorycode); RUN updatedatabase again; check show create table --- .../data/mysql/atomicupdate/bug_30899.pl | 57 +++++++++++++++++++ installer/data/mysql/db_revs/211105004.pl | 2 +- 2 files changed, 58 insertions(+), 1 deletion(-) create mode 100755 installer/data/mysql/atomicupdate/bug_30899.pl diff --git a/installer/data/mysql/atomicupdate/bug_30899.pl b/installer/data/mysql/atomicupdate/bug_30899.pl new file mode 100755 index 0000000000..4701bc4690 --- /dev/null +++ b/installer/data/mysql/atomicupdate/bug_30899.pl @@ -0,0 +1,57 @@ +use Modern::Perl; + +return { + bug_number => 30899, + description => "Check borrower_attribute_types FK constraint (30449 follow-up)", + up => sub { + my ($args) = @_; + my ($dbh, $out) = @$args{qw(dbh out)}; + + if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) { + $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| ); + if( index_exists('borrower_attribute_types', 'category_code_fk') ) { + $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' + ) + ) + { + + 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`) + |); + } + }, +}; diff --git a/installer/data/mysql/db_revs/211105004.pl b/installer/data/mysql/db_revs/211105004.pl index b9c2f73eb5..eb0431b180 100755 --- a/installer/data/mysql/db_revs/211105004.pl +++ b/installer/data/mysql/db_revs/211105004.pl @@ -8,7 +8,7 @@ return { my ($dbh, $out) = @$args{qw(dbh out)}; if( foreign_key_exists('borrower_attribute_types', 'category_code_fk') ) { - $dbh->do( q|ALTER TABLE borrower_attribute_types DROP CONSTRAINT category_code_fk| ); + $dbh->do( q|ALTER TABLE borrower_attribute_types DROP FOREIGN KEY category_code_fk| ); if( index_exists('borrower_attribute_types', 'category_code_fk') ) { $dbh->do( q|ALTER TABLE borrower_attribute_types DROP INDEX category_code_fk| ); } -- 2.20.1