Bug 34516 - Upgrade database fails for 22.11.07.003, points to web installer
Summary: Upgrade database fails for 22.11.07.003, points to web installer
Status: Pushed to oldstable
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: Main
Hardware: PC Linux
: P5 - low blocker with 20 votes (vote)
Assignee: Kevin Carnes
QA Contact: Emily Lamancusa
URL:
Keywords:
: 34548 34712 35543 (view as bug list)
Depends on: 34029
Blocks:
  Show dependency treegraph
 
Reported: 2023-08-10 21:25 UTC by dcowens76
Modified: 2024-04-05 15:09 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
24.05.00,23.11.01,23.05.07


Attachments
Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer (2.96 KB, patch)
2023-11-27 08:55 UTC, Kevin Carnes
Details | Diff | Splinter Review
Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer (2.96 KB, patch)
2023-11-27 08:57 UTC, Kevin Carnes
Details | Diff | Splinter Review
Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer (3.04 KB, patch)
2023-11-29 14:29 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 34516: Upgrade database fails for 22.11.07.003, points to web installer (3.11 KB, patch)
2023-11-29 21:12 UTC, Emily Lamancusa
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description dcowens76 2023-08-10 21:25:01 UTC
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
Comment 1 estclair 2023-08-23 14:19:36 UTC
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
Comment 2 estclair 2023-08-23 17:51:20 UTC
(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
Comment 3 dcowens76 2023-08-23 21:37:20 UTC Comment hidden (obsolete)
Comment 4 Katrin Fischer 2023-08-24 09:06:22 UTC
*** Bug 34548 has been marked as a duplicate of this bug. ***
Comment 5 estclair 2023-08-24 12:38:31 UTC Comment hidden (obsolete)
Comment 6 Jonathan Druart 2023-09-01 08:51:40 UTC
Which version of the DBMS?

Could you run `SHOW CREATE TABLE biblioitems` when it's failing (I know it's certainly too late...)?
Comment 7 estclair 2023-09-01 13:12:53 UTC
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
Comment 8 Jonathan Druart 2023-09-01 13:28:34 UTC
It would be interesting to have the output before the index has been dropped.
Comment 9 Katrin Fischer 2023-09-05 08:45:09 UTC
*** Bug 34712 has been marked as a duplicate of this bug. ***
Comment 10 Katrin Fischer 2023-09-05 08:45:58 UTC
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?
Comment 11 teddy 2023-09-05 08:57:17 UTC
I am using mysql - 8.032, I just follow estclair to drop those 2 table field's index publishercode and the upgrade is done.
Comment 12 Jonathan Druart 2023-09-05 09:05:25 UTC
(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`),
Comment 13 estclair 2023-09-05 12:24:59 UTC
(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))
Comment 14 dcowens76 2023-09-05 19:47:24 UTC
I was upgrading from Koha 22.11.07.003.
mysql version 8.0.34-0ubuntu0.22.04.1.
Comment 15 Jonathan Druart 2023-09-06 07:13:29 UTC
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?
Comment 16 Indranil Das Gupta 2023-10-13 10:40:42 UTC
| 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
Comment 17 Kevin Carnes 2023-11-27 08:55:57 UTC Comment hidden (obsolete)
Comment 18 Kevin Carnes 2023-11-27 08:57:49 UTC
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
Comment 19 Owen Leonard 2023-11-29 14:29:51 UTC
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>
Comment 20 Emily Lamancusa 2023-11-29 21:12:38 UTC
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>
Comment 21 Emily Lamancusa 2023-11-29 21:14:36 UTC
Thank you for solving the mystery, Kevin!! Straightforward fix, passing QA.
Comment 22 Katrin Fischer 2023-12-11 18:46:49 UTC
Pushed for 24.05!

Well done everyone, thank you!
Comment 23 Fridolin Somers 2023-12-12 07:33:38 UTC
Pushed to 23.11.x for 23.11.01
Comment 24 Katrin Fischer 2023-12-12 09:36:15 UTC
*** Bug 35543 has been marked as a duplicate of this bug. ***
Comment 25 Lucas Gass 2023-12-14 21:15:27 UTC
Backported to 23.05.x for upcoming 23.05.07
Comment 26 dcowens76 2024-03-26 02:26:41 UTC
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?
Comment 27 Kevin Carnes 2024-03-27 09:16:30 UTC
(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.
Comment 28 dcowens76 2024-04-05 15:09:45 UTC
Yeah, that was it. Database upgraded now. Thank you. Should have read my old post!