Attachment 167306 Details for Bug 36993 – Bug 36993: Upgrade fails at 23.12.00.023 [Bug 32132]

[patch] Bug 36993: Upgrade fails at 23.12.00.023 [Bug 32132]

0001-Bug-36993-Upgrade-fails-at-23.12.00.023-Bug-32132.patch (text/plain), 3.55 KB, created by Emmi Takkinen on 2024-05-31 10:18:35 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Emmi Takkinen

Created: 2024-05-31 10:18:35 UTC

Size: 3.55 KB

patch

obsolete

>From 051de5327966dab7f444b542b415cb4ed7dd352a Mon Sep 17 00:00:00 2001
>From: Emmi Takkinen <emmi.takkinen@koha-suomi.fi>
>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
>

Actions: View | Diff | Splinter Review

Attachments on bug 36993: 167306 | 167315 | 167377