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
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.
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?
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
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 :)
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
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.
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>
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>
Thanks for all the hard work! Pushed to main for the next 24.11.00 release as RM Assistant
Just ran into this on an upgrade to 23.11.10-1. Can this be backported, please?