Bug 37820

Summary:	Upgrade fails at 23.12.00.023 [Bug 36993]
Product:	Koha	Reporter:	Emmi Takkinen <emmi.takkinen>
Component:	Installation and upgrade (command-line installer)	Assignee:	Emmi Takkinen <emmi.takkinen>
Status:	Pushed to main ---	QA Contact:	Marcel de Rooy <m.de.rooy>
Severity:	normal
Priority:	P5 - low	CC:	emily.lamancusa, jonathan.druart, m.de.rooy, tomascohen
Version:	Main	Keywords:	RM_priority
Hardware:	All
OS:	All
Change sponsored?:	---	Patch complexity:	Trivial patch
Documentation contact:		Documentation submission:
Text to go in the release notes:		Version(s) released in:	24.11.00
Circulation function:
Bug Depends on:	36993
Bug Blocks:
Attachments:	Bug 37820: Upgrade fails at 23.12.00.023 [Bug 36993] Bug 37820: Upgrade fails at 23.12.00.023 [Bug 36993] Bug 37820: Upgrade fails at 23.12.00.023 [Bug 36993]

Description Emmi Takkinen 2024-09-03 11:43:57 UTC

If aqbudgets table doesn't contain foreign key "aqbudgetperiods_ibfk_1" upgrade will fail on following error:

Upgrade to 23.12.00.023  [15:26:18]: Bug 32132 - Set aqbudgets.budget_period_id as NOT NULL
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP FOREIGN KEY `aqbudgetperiods_ibfk_1`; check that it exists at /usr/share/koha/lib/C4/Installer.pm line 741

Comment 1 Emmi Takkinen 2024-09-04 05:48:57 UTC

Created attachment 171010 [details] [review]
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'.

Comment 2 Jonathan Druart 2024-09-05 10:08:58 UTC

Created attachment 171051 [details] [review]
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 <jonathan.druart@bugs.koha-community.org>

Comment 3 Marcel de Rooy 2024-09-06 07:53:21 UTC

Created attachment 171127 [details] [review]
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 <jonathan.druart@bugs.koha-community.org>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>

Comment 4 Katrin Fischer 2024-09-13 09:30:25 UTC

Thanks Emmi for staying on the case here!

Comment 5 Katrin Fischer 2024-09-13 10:08:04 UTC

Pushed for 24.11!

Well done everyone, thank you!