From 6567f6e19219e5613e70dd62b5ee9243ad2a076a Mon Sep 17 00:00:00 2001 From: Emmi Takkinen Date: Wed, 4 Sep 2024 08:47:20 +0300 Subject: [PATCH] Bug 37820: Upgrade fails at 23.12.00.023 [Bug 36993] If table aqbudgets is miissing foreign key 'aqbudgetperiods_ibfk_1' database update fails on "Can't DROP FOREIGN KEY" error. To test: 1. Remove changes made in bug 32132, drop foreign key aqbudgetperiods_ibfk_1 and downgrade your database: - ALTER TABLE aqbudgets MODIFY COLUMN `budget_period_id` INT(11) NULL; - UPDATE aqbudgets SET budget_period_id = NULL WHERE budget_period_id IN(SELECT budget_period_id FROM aqbudgetperiods WHERE budget_period_description = "Budget for funds without budget"); - DELETE FROM aqbudgetperiods WHERE budget_period_description = "Budget for funds without budget"; - ALTER TABLE aqbudgets DROP FOREIGN KEY aqbudgetperiods_ibfk_1; - UPDATE systempreferences SET value="23.1200022" WHERE variable = "Version; 2. Upgrade your database (e.g. running installer/data/mysql/updatedatabase.pl) => Update fails on error "Can't DROP FOREIGN KEY `aqbudgetperiods_ibfk_1`;...". 4. Apply this patch. 5. Try to update your database again. => Database should now be upgraded succesfully. => Confirm table aqbudgets now contains 'aqbudgetperiods_ibfk_1'. Signed-off-by: Jonathan Druart --- installer/data/mysql/db_revs/231200023.pl | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/db_revs/231200023.pl b/installer/data/mysql/db_revs/231200023.pl index cf413d31cbc..2d25669e70a 100755 --- a/installer/data/mysql/db_revs/231200023.pl +++ b/installer/data/mysql/db_revs/231200023.pl @@ -30,8 +30,10 @@ return { "There were $count budget(s) without budget_period_id. They all have been updated to be under budget called 'Budget for funds without budget' (id $aqbp_id)."; } - $dbh->do(q{ALTER TABLE aqbudgets DROP FOREIGN KEY aqbudgetperiods_ibfk_1}); - say $out "Dropped foreign key aqbudgetperiods_ibfk_1"; + if ( foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) ) { + $dbh->do(q{ALTER TABLE aqbudgets DROP FOREIGN KEY aqbudgetperiods_ibfk_1}); + say $out "Dropped foreign key aqbudgetperiods_ibfk_1"; + } $dbh->do(q{ALTER TABLE aqbudgets MODIFY COLUMN `budget_period_id` INT(11) NOT NULL}); say $out "Updated aqbudgets.budget_period_id to NOT accept NULL values"; -- 2.34.1