From 7c8e7bd18004e17a854433a0a1fcacabaf18d248 Mon Sep 17 00:00:00 2001 From: Agustin Moyano Date: Fri, 9 Oct 2020 15:14:26 -0300 Subject: [PATCH] Bug 18050: (follow-up) Force adding FK constraint When there are inconsistent budget_period_id in aqbudgets this patch creates the table _bug_18050_aqbudgets with the original data, then sets the column to null and warns the user that there is inconsistent data. Signed-off-by: Katrin Fischer Signed-off-by: Jonathan Druart (cherry picked from commit 1193dddd8beb4a10a78e385b221c0f908d92dcf6) Signed-off-by: Lucas Gass (cherry picked from commit 5fc0d24ca8c58407e4461142054cb67a4aec5a00) Signed-off-by: Aleisha Amohia --- .../bug_18050_aqbudget_constraint.perl | 21 ++++++++++++++++--- 1 file changed, 18 insertions(+), 3 deletions(-) diff --git a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl b/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl index c2f9af4464..948a72c3e6 100644 --- a/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl +++ b/installer/data/mysql/atomicupdate/bug_18050_aqbudget_constraint.perl @@ -6,15 +6,30 @@ if( CheckVersion( $DBversion ) ) { |); + my $message = ""; + + if($count_missing_budget->[0] > 0) { + $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{ + 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." + } + if ( !foreign_key_exists( 'aqbudgets', 'aqbudgetperiods_ibfk_1' ) && $count_missing_budget->[0] == 0 ) { $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 |); print "Upgrade to $DBversion done (Bug 18050 - Add FK constraint on aqbudgets.budget_period_id)\n"; SetVersion( $DBversion ); - } elsif ( $count_missing_budget->[0] > 0 ) { - print "Upgrade to $DBversion done (Bug 18050 - FK constraint on aqbudgets.budget_period_id couldn't be added. There are $count_missing_budget->[0] funds in your database that are not linked to a valid budget.)\n"; - SetVersion( $DBversion ); } else { print "Upgrade to $DBversion done (Bug 18050 - FK constraint on aqbudgets.budget already exists)\n"; SetVersion( $DBversion ); -- 2.20.1