From 1bea089d94204eebb4b873472a441001fe73a7a1 Mon Sep 17 00:00:00 2001 From: Lucas Gass Date: Thu, 28 Aug 2025 19:05:15 +0000 Subject: [PATCH] Bug 40725: Add TableExists check to db_revs/231200053.pl This patch makes the DB revision idempotent. To test: 1. Fix the Koha version right before the broken upgrade: $ ktd --shell k$ koha-mysql kohadev > UPDATE systempreferences SET value="23.1200052" WHERE variable="version"; \q 2. Restart everything (memcached should be enough) k$ restart_all 3. Run: k$ updatedatabase => FAIL: You get: ```shell Upgrade to 23.12.00.053 [19:34:35]: Bug 36755 - Increase length of 'code' column in borrower_attribute_types ERROR: C4::Installer::foreign_key_exists(): DBI Exception: DBD::mysql::db selectrow_array failed: Table 'koha_kohadev.pseudonymized_borrower_attributes' doesn't exist at /kohadevbox/koha/installer/data/mysql/db_revs/231200053.pl line 11 ``` 4. Apply this patch 5. Repeat 3 => SUCCESS: Update runs smoothly! 6. Sign off :-D Signed-off-by: Tomas Cohen Arazi --- installer/data/mysql/db_revs/231200053.pl | 121 +++++++++++++++------- 1 file changed, 83 insertions(+), 38 deletions(-) diff --git a/installer/data/mysql/db_revs/231200053.pl b/installer/data/mysql/db_revs/231200053.pl index 79116c24a6b..1eb8289e16d 100755 --- a/installer/data/mysql/db_revs/231200053.pl +++ b/installer/data/mysql/db_revs/231200053.pl @@ -8,62 +8,107 @@ return { my ( $dbh, $out ) = @$args{qw(dbh out)}; # Drop related tables constraints - if ( foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) { - $dbh->do( - q{ALTER TABLE pseudonymized_borrower_attributes DROP FOREIGN KEY anonymized_borrower_attributes_ibfk_2} - ); + if ( TableExists('pseudonymized_borrower_attributes') ) { + if ( foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) { + $dbh->do( + q{ALTER TABLE pseudonymized_borrower_attributes DROP FOREIGN KEY anonymized_borrower_attributes_ibfk_2} + ); + } } - if ( foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) { - $dbh->do( - q{ALTER TABLE borrower_attribute_types_branches DROP FOREIGN KEY borrower_attribute_types_branches_ibfk_1} - ); + + if ( TableExists('borrower_attribute_types_branches') ) { + if ( foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) + { + $dbh->do( + q{ALTER TABLE borrower_attribute_types_branches DROP FOREIGN KEY borrower_attribute_types_branches_ibfk_1} + ); + } } - if ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { - $dbh->do(q{ALTER TABLE borrower_attributes DROP FOREIGN KEY borrower_attributes_ibfk_2}); + + if ( TableExists('borrower_attributes') ) { + if ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { + $dbh->do(q{ALTER TABLE borrower_attributes DROP FOREIGN KEY borrower_attributes_ibfk_2}); + } } # Update the column we want - unless ( foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) - || foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) - || foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) - { + my $fkcheck1 = 0; + if ( TableExists('pseudonymized_borrower_attributes') ) { + $fkcheck1 = + foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ); + } + + my $fkcheck2 = 0; + if ( TableExists('borrower_attribute_types_branches') ) { + $fkcheck2 = + foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ); + } + + my $fkcheck3 = 0; + if ( TableExists('borrower_attributes') ) { + $fkcheck3 = foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ); + } + + unless ( $fkcheck1 || $fkcheck2 || $fkcheck3 ) { $dbh->do( q{ALTER TABLE borrower_attribute_types MODIFY COLUMN code VARCHAR(64) NOT NULL COMMENT 'unique key used to identify each custom field'} ); } # Update the related tables - unless ( foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) { - $dbh->do( - q{ALTER TABLE pseudonymized_borrower_attributes MODIFY COLUMN code VARCHAR(64) NOT NULL COMMENT 'foreign key from the borrower_attribute_types table, defines which custom field this value was entered for'} - ); + if ( TableExists('pseudonymized_borrower_attributes') ) { + unless ( + foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) + { + $dbh->do( + q{ALTER TABLE pseudonymized_borrower_attributes MODIFY COLUMN code VARCHAR(64) NOT NULL COMMENT 'foreign key from the borrower_attribute_types table, defines which custom field this value was entered for'} + ); + } } - unless ( foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) - { - $dbh->do(q{ALTER TABLE borrower_attribute_types_branches MODIFY COLUMN bat_code VARCHAR(64) DEFAULT NULL}); + + if ( TableExists('borrower_attribute_types_branches') ) { + unless ( + foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) + { + $dbh->do( + q{ALTER TABLE borrower_attribute_types_branches MODIFY COLUMN bat_code VARCHAR(64) DEFAULT NULL}); + } } - unless ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { - $dbh->do( - q{ALTER TABLE borrower_attributes MODIFY COLUMN code VARCHAR(64) NOT NULL COMMENT 'foreign key from the borrower_attribute_types table, defines which custom field this value was entered for'} - ); + + if ( TableExists('borrower_attributes') ) { + unless ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { + $dbh->do( + q{ALTER TABLE borrower_attributes MODIFY COLUMN code VARCHAR(64) NOT NULL COMMENT 'foreign key from the borrower_attribute_types table, defines which custom field this value was entered for'} + ); + } } # Restore related tables constraints - unless ( foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) { - $dbh->do( - q{ALTER TABLE pseudonymized_borrower_attributes ADD CONSTRAINT anonymized_borrower_attributes_ibfk_2 FOREIGN KEY (code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE ON UPDATE CASCADE} - ); + if ( TableExists('pseudonymized_borrower_attributes') ) { + unless ( + foreign_key_exists( 'pseudonymized_borrower_attributes', 'anonymized_borrower_attributes_ibfk_2' ) ) + { + $dbh->do( + q{ALTER TABLE pseudonymized_borrower_attributes ADD CONSTRAINT anonymized_borrower_attributes_ibfk_2 FOREIGN KEY (code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE ON UPDATE CASCADE} + ); + } } - unless ( foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) - { - $dbh->do( - q{ALTER TABLE borrower_attribute_types_branches ADD CONSTRAINT borrower_attribute_types_branches_ibfk_1 FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE} - ); + if ( TableExists('borrower_attribute_types_branches') ) { + unless ( + foreign_key_exists( 'borrower_attribute_types_branches', 'borrower_attribute_types_branches_ibfk_1' ) ) + { + $dbh->do( + q{ALTER TABLE borrower_attribute_types_branches ADD CONSTRAINT borrower_attribute_types_branches_ibfk_1 FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE} + ); + } } - unless ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { - $dbh->do( - q{ALTER TABLE borrower_attributes ADD CONSTRAINT borrower_attributes_ibfk_2 FOREIGN KEY (code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE ON UPDATE CASCADE} - ); + + if ( TableExists('borrower_attributes') ) { + unless ( foreign_key_exists( 'borrower_attributes', 'borrower_attributes_ibfk_2' ) ) { + $dbh->do( + q{ALTER TABLE borrower_attributes ADD CONSTRAINT borrower_attributes_ibfk_2 FOREIGN KEY (code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE ON UPDATE CASCADE} + ); + } } # HTML customizations -- 2.51.0