From b696b7eace735f635a4db0fbf6d36f9658284fa2 Mon Sep 17 00:00:00 2001 From: Katrin Fischer Date: Mon, 5 Oct 2020 06:17:34 +0000 Subject: [PATCH] Bug 18050: (QA follow-up) Adjust conditions and make use of message text - When we run into invalid data, we use Augustin's suggested solution and copy the data into a separate table, setting the budget_period_id to NULL. - We amend the output using the new $message. Signed-off-by: Katrin Fischer --- .../atomicupdate/bug_18050_aqbudget_constraint.perl | 18 ++++++++++-------- 1 file changed, 10 insertions(+), 8 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl b/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl index f978bdc6ba..0575d27863 100644 --- a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl +++ b/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl @@ -3,32 +3,34 @@ if( CheckVersion( $DBversion ) ) { my $count_missing_budget = $dbh->selectrow_arrayref(q| SELECT COUNT(*) FROM aqbudgets ab WHERE NOT EXISTS (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id) + AND budget_period_id IS NOT NULL; |); my $message = ""; - if($count_missing_budget->[0] > 0) { - $dbh->do(q{ + $dbh->do(q| CREATE TABLE _bug_18050_aqbudgets AS SELECT * FROM aqbudgets ab WHERE NOT EXISTS (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id) - }); + |); - $dbh->do(q{ + $dbh->do(q| UPDATE aqbudgets ab SET budget_period_id = NULL WHERE NOT EXISTS (SELECT * FROM aqbudgetperiods abp WHERE abp.budget_period_id = ab.budget_period_id) - }); + |); + $message = ". There are $count_missing_budget->[0] funds in your database that are not linked + to a valid budget. Setting invalid budget id (budget_period_id) to null. The table _bug_18050_aqbudgets + was created with original data. Please check that table and place valid ids in aqbudget table as soon as possible." - $message = "There are $count_missing_budget->[0] funds in your database that are not linked to a valid budget. Setting invalid budget id (budget_period_id) to null. The table _bug_18050_aqbudgets was created with original data. Please check that table and place valid ids in aqbudget table as soon as possible." } - if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) && $count_missing_budget->[0] == 0 ) { + if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) ) { $dbh->do(q| ALTER TABLE aqbudgets ADD CONSTRAINT `aqbudgetperiods_ibfk_1` FOREIGN KEY (`budget_period_id`) REFERENCES `aqbudgetperiods` (`budget_period_id`) ON UPDATE CASCADE ON DELETE CASCADE |); - NewVersion( $DBversion, 18050, "Add FK constraint on aqbudgets.budget_period_id"); + NewVersion( $DBversion, 18050, "Add FK constraint on aqbudgets.budget_period_id$message"); } else { NewVersion( $DBversion, 18050, "FK constraint on aqbudgets.budget already exists"); } -- 2.11.0