Following an upgrade to 22.11.07.003 on Ubuntu 22.04, the upgrade fails and the staff interface goes to the web installer. Following advice on the community list, I ran the following commands to try to resolve the issue, with the output following: $ sudo service memcached restart $ sudo koha-upgrade-schema [instance name] Upgrading database schema for [instance name] Upgrade to 22.11.07.003 [14:01:47]: 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: BLOB/TEXT column 'publishercode' used in key specification without a key length at /usr/share/koha/lib/C4/Installer.pm line 741
I have the same issue and I am upgrading from 23.05.01.002 to 23.05.02.000 with Ubuntu 22.04.3 LTS
(In reply to estclair from comment #1) > I have the same issue and I am upgrading from 23.05.01.002 to 23.05.02.000 > with Ubuntu 22.04.3 LTS I was able to fix it by running: DROP INDEX publishercode on deletedbiblioitems; and DROP INDEX publishercode on biblioitems; Then rerunning the upgrade. eric
Interesting, thanks for sharing what worked for you. I tried the same process, I think, with the following result: $ mysql -uroot -p mysql> USE koha_[instance]; mysql> DROP INDEX publishercode on deletedbiblioitems; mysql> DROP INDEX publishercode on biblioitems; mysql> quit; $ sudo apt upgrade [to upgrade the koha-common package] $ sudo service memcached restart $ sudo koha-upgrade-schema [instance] Upgrading database schema for [instance] Upgrade to 22.12.00.026 [20:28:04]: Bug 22440 - Add new /ill_requests endopoint ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate key name 'illrequests_bibfk' at /usr/share/koha/lib/C4/Installer.pm line 741 For me, the "Welcome to the Koha 23.05 web installer" then appears rather than the staff interface, and the OPAC shows it is in maintenance mode.
*** Bug 34548 has been marked as a duplicate of this bug. ***
(In reply to dcowens76 from comment #3) > Interesting, thanks for sharing what worked for you. I tried the same > process, I think, with the following result: > > $ mysql -uroot -p > > mysql> USE koha_[instance]; > mysql> DROP INDEX publishercode on deletedbiblioitems; > mysql> DROP INDEX publishercode on biblioitems; > mysql> quit; > > $ sudo apt upgrade [to upgrade the koha-common package] > > $ sudo service memcached restart > $ sudo koha-upgrade-schema [instance] > Upgrading database schema for [instance] > Upgrade to 22.12.00.026 [20:28:04]: Bug 22440 - Add new /ill_requests > endopoint > ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate key > name 'illrequests_bibfk' at /usr/share/koha/lib/C4/Installer.pm line 741 > > For me, the "Welcome to the Koha 23.05 web installer" then appears rather > than the staff interface, and the OPAC shows it is in maintenance mode. try this thread: there's a fix in it. https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276
Which version of the DBMS? Could you run `SHOW CREATE TABLE biblioitems` when it's failing (I know it's certainly too late...)?
It might indeed too late but here is the info you requested: mysql Ver 8.0.34-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu)) biblioitems | CREATE TABLE `biblioitems` ( `biblioitemnumber` int NOT NULL AUTO_INCREMENT, `biblionumber` int NOT NULL DEFAULT '0', `volume` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `number` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `itemtype` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `isbn` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `issn` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `ean` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `publicationyear` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `publishercode` text COLLATE utf8mb4_unicode_ci COMMENT 'publisher (MARC21 260$b and 264$b)', `volumedate` date DEFAULT NULL, `volumedesc` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `collectiontitle` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `collectionissn` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `collectionvolume` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `editionstatement` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `editionresponsibility` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `illus` text COLLATE utf8mb4_unicode_ci COMMENT 'illustrations (MARC21 300$b)', `pages` text COLLATE utf8mb4_unicode_ci COMMENT 'number of pages (MARC21 300$a)', `notes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `size` text COLLATE utf8mb4_unicode_ci COMMENT 'material size (MARC21 300$c)', `place` text COLLATE utf8mb4_unicode_ci COMMENT 'publication place (MARC21 260$a and 264$a)', `lccn` longtext COLLATE utf8mb4_unicode_ci COMMENT 'library of congress control number (MARC21 010$a)', `url` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, `cn_source` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cn_class` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cn_item` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cn_suffix` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `cn_sort` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `agerestriction` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `totalissues` int DEFAULT NULL, PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`(255)), KEY `issn` (`issn`(255)), KEY `timestamp` (`timestamp`), KEY `ean` (`ean`(255)), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=20499 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
It would be interesting to have the output before the index has been dropped.
*** Bug 34712 has been marked as a duplicate of this bug. ***
It looks like we are stuck here on why this is failing :( Could people add more information please? MariaDB/MySQL, which version? Which version did you update from?
I am using mysql - 8.032, I just follow estclair to drop those 2 table field's index publishercode and the upgrade is done.
(In reply to estclair from comment #7) > PRIMARY KEY (`biblioitemnumber`), > KEY `bibinoidx` (`biblioitemnumber`), > KEY `bibnoidx` (`biblionumber`), > KEY `itemtype_idx` (`itemtype`), > KEY `isbn` (`isbn`(255)), > KEY `issn` (`issn`(255)), > KEY `timestamp` (`timestamp`), > KEY `ean` (`ean`(255)), I do have: PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), KEY `isbn` (`isbn`(191)), KEY `issn` (`issn`(191)), KEY `ean` (`ean`(191)), KEY `publishercode` (`publishercode`(191)), KEY `timestamp` (`timestamp`),
(In reply to Katrin Fischer from comment #10) > It looks like we are stuck here on why this is failing :( > > Could people add more information please? MariaDB/MySQL, which version? > Which version did you update from? We were upgrading from Koha 23.05.01.002 to 23.05.02.000 mysql Ver 8.0.34-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
I was upgrading from Koha 22.11.07.003. mysql version 8.0.34-0ubuntu0.22.04.1.
Anyone with a backup who could run 'SHOW CREATE TABLE biblioitems' *before* the upgrade? Are you using an old installation? Could you remember which version you installed initially?
| biblioitems | CREATE TABLE `biblioitems` ( `biblioitemnumber` int(11) NOT NULL AUTO_INCREMENT, `biblionumber` int(11) NOT NULL DEFAULT 0, `volume` longtext DEFAULT NULL, `number` longtext DEFAULT NULL, `itemtype` varchar(10) DEFAULT NULL, `isbn` longtext DEFAULT NULL, `issn` longtext DEFAULT NULL, `ean` longtext DEFAULT NULL, `publicationyear` mediumtext DEFAULT NULL, `publishercode` varchar(255) DEFAULT NULL, `volumedate` date DEFAULT NULL, `volumedesc` mediumtext DEFAULT NULL, `collectiontitle` longtext DEFAULT NULL, `collectionissn` mediumtext DEFAULT NULL, `collectionvolume` longtext DEFAULT NULL, `editionstatement` mediumtext DEFAULT NULL, `editionresponsibility` mediumtext DEFAULT NULL, `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `illus` varchar(255) DEFAULT NULL, `pages` varchar(255) DEFAULT NULL, `notes` longtext DEFAULT NULL, `size` varchar(255) DEFAULT NULL, `place` text DEFAULT NULL COMMENT 'publication place (MARC21 260$a and 264$a)', `lccn` longtext DEFAULT NULL COMMENT 'library of congress control number (MARC21 010$a)', `url` mediumtext DEFAULT NULL, `cn_source` varchar(10) DEFAULT NULL, `cn_class` varchar(30) DEFAULT NULL, `cn_item` varchar(10) DEFAULT NULL, `cn_suffix` varchar(10) DEFAULT NULL, `cn_sort` varchar(255) DEFAULT NULL, `agerestriction` varchar(255) DEFAULT NULL, `totalissues` int(10) DEFAULT NULL, PRIMARY KEY (`biblioitemnumber`), KEY `bibinoidx` (`biblioitemnumber`), KEY `bibnoidx` (`biblionumber`), KEY `itemtype_idx` (`itemtype`), KEY `publishercode` (`publishercode`), KEY `isbn` (`isbn`(255)), KEY `issn` (`issn`(255)), KEY `timestamp` (`timestamp`), KEY `ean` (`ean`(255)), CONSTRAINT `biblioitems_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=9794 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | From trying to upgrade from 22.11.07 to 22.11.10-2
Created attachment 159259 [details] [review] Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer MySQL requires key length for indexes of text columns, while MariaDB sets it automatically for utf8mb4. Test plan: 1. Setup Koha to use MySQL (e.g. ku-my8) 2. Downgrade database with the following commands (e.g. koha-mysql kohadev): ALTER TABLE `biblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `biblioitems` DROP INDEX `publishercode`; ALTER TABLE `biblioitems` ADD INDEX `publishercode` (`publishercode`); ALTER TABLE `deletedbiblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `deletedbiblioitems` DROP INDEX `publishercode`; ALTER TABLE `deletedbiblioitems` ADD INDEX `publishercode` (`publishercode`); UPDATE systempreferences SET value="23.0600006" WHERE variable = "Version"; 3. Attempt to update the database (e.g. koha-upgrade-schema kohadev) 4. Notice the error about "key specification without a key length" 5. Apply patch 6. Attempt to update the database (e.g. koha-upgrade-schema kohadev) 7. Notice that the database updates 8. Sign off
Created attachment 159260 [details] [review] Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer MySQL requires key length for indexes of text columns, while MariaDB sets it automatically for utf8mb4. Test plan: 1. Setup Koha to use MySQL (e.g. ku-my8) 2. Downgrade database with the following commands (e.g. koha-mysql kohadev): ALTER TABLE `biblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `biblioitems` DROP INDEX `publishercode`; ALTER TABLE `biblioitems` ADD INDEX `publishercode` (`publishercode`); ALTER TABLE `deletedbiblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `deletedbiblioitems` DROP INDEX `publishercode`; ALTER TABLE `deletedbiblioitems` ADD INDEX `publishercode` (`publishercode`); UPDATE systempreferences SET value="23.0600006" WHERE variable = "Version"; 3. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 4. Notice the error about "key specification without a key length" 5. Apply patch 6. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 7. Notice that the database is upgraded 8. Sign off
Created attachment 159339 [details] [review] Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer MySQL requires key length for indexes of text columns, while MariaDB sets it automatically for utf8mb4. Test plan: 1. Setup Koha to use MySQL (e.g. ku-my8) 2. Downgrade database with the following commands (e.g. koha-mysql kohadev): ALTER TABLE `biblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `biblioitems` DROP INDEX `publishercode`; ALTER TABLE `biblioitems` ADD INDEX `publishercode` (`publishercode`); ALTER TABLE `deletedbiblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `deletedbiblioitems` DROP INDEX `publishercode`; ALTER TABLE `deletedbiblioitems` ADD INDEX `publishercode` (`publishercode`); UPDATE systempreferences SET value="23.0600006" WHERE variable = "Version"; 3. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 4. Notice the error about "key specification without a key length" 5. Apply patch 6. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 7. Notice that the database is upgraded 8. Sign off Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Created attachment 159375 [details] [review] Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer MySQL requires key length for indexes of text columns, while MariaDB sets it automatically for utf8mb4. Test plan: 1. Setup Koha to use MySQL (e.g. ku-my8) 2. Downgrade database with the following commands (e.g. koha-mysql kohadev): ALTER TABLE `biblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `biblioitems` DROP INDEX `publishercode`; ALTER TABLE `biblioitems` ADD INDEX `publishercode` (`publishercode`); ALTER TABLE `deletedbiblioitems` MODIFY COLUMN `publishercode` varchar(255); ALTER TABLE `deletedbiblioitems` DROP INDEX `publishercode`; ALTER TABLE `deletedbiblioitems` ADD INDEX `publishercode` (`publishercode`); UPDATE systempreferences SET value="23.0600006" WHERE variable = "Version"; 3. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 4. Notice the error about "key specification without a key length" 5. Apply patch 6. Attempt to upgrade the database (e.g. koha-upgrade-schema kohadev) 7. Notice that the database is upgraded 8. Sign off Signed-off-by: Owen Leonard <oleonard@myacpl.org> Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Thank you for solving the mystery, Kevin!! Straightforward fix, passing QA.
Pushed for 24.05! Well done everyone, thank you!
Pushed to 23.11.x for 23.11.01
*** Bug 35543 has been marked as a duplicate of this bug. ***
Backported to 23.05.x for upcoming 23.05.07
Thank you to those who worked on this. I waited awhile to try the upgrade, making sure it was in the Ubuntu package repository. This is what I get when the database schema fails: Upgrading database schema for [instance name] Upgrade to 22.12.00.026 [02:21:44]: Bug 22440 - Add new /ill_requests endopoint ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate key name 'illrequests_bibfk' at /usr/share/koha/lib/C4/Installer.pm line 741 This looks very familiar. The package version is 23.11.03-1. Is there an extra step I need to take to get past this bug? Or do I need to go back to oldoldstable?
(In reply to dcowens76 from comment #26) > Thank you to those who worked on this. I waited awhile to try the upgrade, > making sure it was in the Ubuntu package repository. This is what I get when > the database schema fails: > > Upgrading database schema for [instance name] > Upgrade to 22.12.00.026 [02:21:44]: Bug 22440 - Add new /ill_requests > endopoint > ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate key > name 'illrequests_bibfk' at /usr/share/koha/lib/C4/Installer.pm line 741 > > This looks very familiar. The package version is 23.11.03-1. Is there an > extra step I need to take to get past this bug? Or do I need to go back to > oldoldstable? I think this might be Bug 34276. Dropping these two keys should help https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34276#c21.
Yeah, that was it. Database upgraded now. Thank you. Should have read my old post!