From 051de5327966dab7f444b542b415cb4ed7dd352a Mon Sep 17 00:00:00 2001 From: Emmi Takkinen Date: Fri, 31 May 2024 12:39:57 +0300 Subject: [PATCH] Bug 36993: Upgrade fails at 23.12.00.023 [Bug 32132] Database update fails on some databases because of foreign key constraint error. This comes from attempt to make column aqbudgets.budget_period_id not accept NULL values. Update also fails if there are rows where column aqbudgets.budget_period_id doesn't match any values in aqbudgetperiods.budget_period_id. To test: 1. Remove changes made in bug 32132 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"; - UPDATE systempreferences SET value="23.1200022" WHERE variable = "Version; 2. Upgrade your database (e.g. running installer/data/mysql/updatedatabase.pl) => Update fails on foreign key constraint error. 4. Apply this patch. 5. Try to update your database again. => Database should now be upgraded succesfully. --- installer/data/mysql/db_revs/231200023.pl | 16 ++++++++++++++-- 1 file changed, 14 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/db_revs/231200023.pl b/installer/data/mysql/db_revs/231200023.pl index 7fa58207f3..cf413d31cb 100755 --- a/installer/data/mysql/db_revs/231200023.pl +++ b/installer/data/mysql/db_revs/231200023.pl @@ -8,7 +8,8 @@ return { my ( $dbh, $out ) = @$args{qw(dbh out)}; # Do you stuffs here - my $count_sql = q{SELECT COUNT(*) FROM aqbudgets WHERE budget_period_id IS NULL}; + my $count_sql = + q{SELECT COUNT(*) FROM aqbudgets WHERE budget_period_id IS NULL OR budget_period_id NOT IN(SELECT budget_period_id FROM aqbudgetperiods)}; my ($count) = $dbh->selectrow_array($count_sql); if ($count) { $dbh->do( @@ -20,13 +21,24 @@ return { q{SELECT budget_period_id FROM aqbudgetperiods WHERE budget_period_description = "Budget for funds without budget"}; my ($aqbp_id) = $dbh->selectrow_array($aqbp_sql); - $dbh->do( q{UPDATE aqbudgets SET budget_period_id = ? WHERE budget_period_id IS NULL}, undef, $aqbp_id ); + $dbh->do( + q{UPDATE aqbudgets SET budget_period_id = ? WHERE budget_period_id IS NULL OR budget_period_id NOT IN(SELECT budget_period_id FROM aqbudgetperiods)}, + undef, $aqbp_id + ); say $out "Updated columns aqbudgets.budget_period_id with value NULL"; say $out "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"; + $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"; + + $dbh->do( + q{ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES aqbudgetperiods(`budget_period_id`) ON DELETE CASCADE ON UPDATE CASCADE} + ); + say $out "Readded foreign key aqbudgetperiods_ibfk_1"; }, }; -- 2.34.1