Bug 36978 - Upgrade fails at 23.06.00.007 [Bug 34029]
Summary: Upgrade fails at 23.06.00.007 [Bug 34029]
Status: Pushed to main
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: 24.05
Hardware: All All
: P5 - low blocker (vote)
Assignee: Martin Renvoize
QA Contact: Julian Maurice
URL:
Keywords:
Depends on: 34029
Blocks:
  Show dependency treegraph
 
Reported: 2024-05-28 21:17 UTC by Rex Barkdoll
Modified: 2024-06-10 11:04 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
24.11.00


Attachments
Bug 36978: [Bug 34029 follow-up] Make update idempotent (2.21 KB, patch)
2024-06-04 11:52 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 36978: [Bug 34029 follow-up] Make update idempotent (2.26 KB, patch)
2024-06-07 08:26 UTC, Chris Cormack
Details | Diff | Splinter Review
Bug 36978: (Bug 34029 follow-up) Make update idempotent (2.32 KB, patch)
2024-06-07 12:14 UTC, Julian Maurice
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Rex Barkdoll 2024-05-28 21:17:09 UTC
Hi, I'm trying to upgrade from 22.05 to 24 (current).

I'm running into this error and I'm not sure how to fix it. I've tried removing the publishercode column from biblioitems and deletedbiblioitems, but it still returns this error. I've also looked at the Installer.pm file, but the error is returning in the middle of a function and I have no idea what to do with that.

Any suggestions for how to help get this working again?

Upgrading database schema for INSTANCE
Upgrade to 23.06.00.007  [21:04:39]: Bug 34029 - Extend datatypes in biblioitems and deletedbiblioitems tables to avoid import errors
        Updated biblioitems.place to text
        Updated deletedbiblioitems.place to text
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP 'publishercode'; check that column/key exists at /usr/share/koha/lib/C4/Installer.pm line 741
Comment 1 Katrin Fischer 2024-05-29 12:24:30 UTC
Hi Rex,

removing is not the correct fix, the column needs to be there. When you look at the update script used, the drop is not for the column, but for the index:

https://git.koha-community.org/Koha-community/Koha/src/branch/main/installer/data/mysql/db_revs/230600007.pl


        $dbh->do(
            q{
            ALTER TABLE `biblioitems`
            DROP INDEX `publishercode`
        }
        );
        say $out "Remove index on biblioitems.publishercode";
        $dbh->do(
            q{
            ALTER TABLE `biblioitems`
            MODIFY COLUMN `publishercode` text DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)'
        }
        );

Can you verify there is no index publishercode in your installation? Adding it might make that one pass... but we need to get some more information.

24.05 (we do 2 releases a year) is as old as yesterday, so it's very very new.
Comment 2 Rex Barkdoll 2024-05-29 15:58:49 UTC
Ok, I've restored my database to a previous version.

As far as I can tell, there's no index. 

How would I go about adding one correctly?
Comment 3 Rex Barkdoll 2024-05-29 16:15:15 UTC
Ok, after a little research into the Koha codebase, i saw how it was done originally, adding the index to the biblioitems and deletedbiblioitems tables allowed the upgrade to progress and then i hit a new wall.

"cannot change column 'budget_period_id': used in a foreign key constraint 'aqbudgetperiods_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741
Comment 4 Rex Barkdoll 2024-05-29 17:59:10 UTC
Ok, I've finally upgraded to 24.05.00.000

I took a screenshot of the Foreign Key constraint in PHPmyadmin's relation view and figured out how to remove it so that i could run the upgrade again. The upgrade continued until got stopped here:

Upgrade to 23.12.00.044  [17:47:36]: Bug 36120 - Add pickup location to bookings
        Added column 'bookings.pickup_library_id'
        Set existing bookings pickup location to item homebranch
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'pickup_library_id': used in a foreign key constraint 'bookings_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 741

I added back the first constraint to the aqbudgets.budget_period_id

then repeated the process of screenshotting the bookings.pickup_library_id foreign key, removing it, running the update (which then got me all the way to version 24.05) and then finally re-added the constraint for bookings.pickup_library_id. 

Koha is back up and running now, so I should be all good.

I would appreciate it if you could let me know whether adding back those foreign key constraints was the right thing to do or if I should remove them? 

Thank you for your assistance on this Katrin :)
Comment 5 Katrin Fischer 2024-05-31 08:09:53 UTC
We'll split this into different bugs for the issues found. This way it's easier as we can link it to the bug that caused the issue in the first place:

This bug
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP 'publishercode'; check that column/key exists at

Bug 36993:
"cannot change column 'budget_period_id': used in a foreign key constraint 'aqbudgetperiods_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741

Bug 37000
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'pickup_library_id': used in a foreign key constraint 'bookings_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 741
Comment 6 Martin Renvoize 2024-06-04 11:52:55 UTC
Created attachment 167379 [details] [review]
Bug 36978: [Bug 34029 follow-up] Make update idempotent

The update wasn't checking for index pre-existance and as such could
fail in certain cases.
Comment 7 Chris Cormack 2024-06-07 08:26:42 UTC
Created attachment 167562 [details] [review]
Bug 36978: [Bug 34029 follow-up] Make update idempotent

The update wasn't checking for index pre-existance and as such could
fail in certain cases.

Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>
Comment 8 Julian Maurice 2024-06-07 12:14:42 UTC
Created attachment 167570 [details] [review]
Bug 36978: (Bug 34029 follow-up) Make update idempotent

The update wasn't checking for index pre-existance and as such could
fail in certain cases.

Signed-off-by: Chris Cormack <chris@bigballofwax.co.nz>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>
Comment 9 Martin Renvoize 2024-06-10 11:04:06 UTC
Thanks for all the hard work!

Pushed to main for the next 24.11.00 release as RM Assistant