Bug 35543 - Error message when upgrading from 23.05 to 23.11
Summary: Error message when upgrading from 23.05 to 23.11
Status: RESOLVED DUPLICATE of bug 34516
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low critical (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-12-12 05:58 UTC by Victor Zuniga
Modified: 2023-12-12 18:55 UTC (History)
2 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Victor Zuniga 2023-12-12 05:58:33 UTC
Hello,

I was finishing the upgrade on our catalog from 23.05 to 23.11 via the web interface this evening when I got the following error message:

=====

Updating database structure
Update errors :

Upgrade to 23.06.00.007 [22:25:29]: Bug 34029 - Extend datatypes in biblioitems and deletedbiblioitems tables to avoid import errors
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
Updated biblioitems.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
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 checked both tables and the column is there:



=========


mysql> show columns from biblioitems;
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field                 | Type         | Null | Key | Default           | Extra                                         |
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| biblioitemnumber      | int          | NO   | PRI | NULL              | auto_increment                                |
| biblionumber          | int          | NO   | MUL | 0                 |                                               |
| volume                | longtext     | YES  |     | NULL              |                                               |
| number                | longtext     | YES  |     | NULL              |                                               |
| itemtype              | varchar(10)  | YES  | MUL | NULL              |                                               |
| isbn                  | longtext     | YES  | MUL | NULL              |                                               |
| issn                  | longtext     | YES  | MUL | NULL              |                                               |
| ean                   | longtext     | YES  | MUL | NULL              |                                               |
| publicationyear       | mediumtext   | YES  |     | NULL              |                                               |
| publishercode         | varchar(255) | YES  | MUL | NULL              |                                               |
| volumedate            | date         | YES  |     | NULL              |                                               |
| volumedesc            | mediumtext   | YES  |     | NULL              |                                               |
| collectiontitle       | longtext     | YES  |     | NULL              |                                               |
| collectionissn        | mediumtext   | YES  |     | NULL              |                                               |
| collectionvolume      | longtext     | YES  |     | NULL              |                                               |
| editionstatement      | mediumtext   | YES  |     | NULL              |                                               |
| editionresponsibility | mediumtext   | YES  |     | NULL              |                                               |
| timestamp             | timestamp    | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| illus                 | varchar(255) | YES  |     | NULL              |                                               |
| pages                 | varchar(255) | YES  |     | NULL              |                                               |
| notes                 | longtext     | YES  |     | NULL              |                                               |
| size                  | varchar(255) | YES  |     | NULL              |                                               |
| place                 | text         | YES  |     | NULL              |                                               |
| lccn                  | longtext     | YES  |     | NULL              |                                               |
| url                   | mediumtext   | YES  |     | NULL              |                                               |
| cn_source             | varchar(10)  | YES  |     | NULL              |                                               |
| cn_class              | varchar(30)  | YES  |     | NULL              |                                               |
| cn_item               | varchar(10)  | YES  |     | NULL              |                                               |
| cn_suffix             | varchar(10)  | YES  |     | NULL              |                                               |
| cn_sort               | varchar(255) | YES  |     | NULL              |                                               |
| agerestriction        | varchar(255) | YES  |     | NULL              |                                               |
| totalissues           | int          | YES  |     | NULL              |                                               |
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
32 rows in set (0.00 sec)

mysql>



mysql> show columns from deletedbiblioitems;
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field                 | Type         | Null | Key | Default           | Extra                                         |
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| biblioitemnumber      | int          | NO   | PRI | 0                 |                                               |
| biblionumber          | int          | NO   | MUL | 0                 |                                               |
| volume                | longtext     | YES  |     | NULL              |                                               |
| number                | longtext     | YES  |     | NULL              |                                               |
| itemtype              | varchar(10)  | YES  | MUL | NULL              |                                               |
| isbn                  | longtext     | YES  | MUL | NULL              |                                               |
| issn                  | longtext     | YES  |     | NULL              |                                               |
| ean                   | longtext     | YES  | MUL | NULL              |                                               |
| publicationyear       | mediumtext   | YES  |     | NULL              |                                               |
| publishercode         | varchar(255) | YES  | MUL | NULL              |                                               |
| volumedate            | date         | YES  |     | NULL              |                                               |
| volumedesc            | mediumtext   | YES  |     | NULL              |                                               |
| collectiontitle       | longtext     | YES  |     | NULL              |                                               |
| collectionissn        | mediumtext   | YES  |     | NULL              |                                               |
| collectionvolume      | longtext     | YES  |     | NULL              |                                               |
| editionstatement      | mediumtext   | YES  |     | NULL              |                                               |
| editionresponsibility | mediumtext   | YES  |     | NULL              |                                               |
| timestamp             | timestamp    | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| illus                 | varchar(255) | YES  |     | NULL              |                                               |
| pages                 | varchar(255) | YES  |     | NULL              |                                               |
| notes                 | longtext     | YES  |     | NULL              |                                               |
| size                  | varchar(255) | YES  |     | NULL              |                                               |
| place                 | text         | YES  |     | NULL              |                                               |
| lccn                  | longtext     | YES  |     | NULL              |                                               |
| url                   | mediumtext   | YES  |     | NULL              |                                               |
| cn_source             | varchar(10)  | YES  |     | NULL              |                                               |
| cn_class              | varchar(30)  | YES  |     | NULL              |                                               |
| cn_item               | varchar(10)  | YES  |     | NULL              |                                               |
| cn_suffix             | varchar(10)  | YES  |     | NULL              |                                               |
| cn_sort               | varchar(255) | YES  |     | NULL              |                                               |
| agerestriction        | varchar(255) | YES  |     | NULL              |                                               |
| totalissues           | int          | YES  |     | NULL              |                                               |
+-----------------------+--------------+------+-----+-------------------+-----------------------------------------------+
32 rows in set (0.01 sec)

mysql>



=========



I also looked at the code from file '/usr/share/koha/lib/C4/Installer.pm' on line 741 (last line) which is displayed below:


======

 try {
 736         my $schema = Koha::Database->new->schema;
 737         $schema->txn_do(
 738             sub {
 739                 $db_rev->{up}->( { dbh => $schema->storage->dbh, out => $outfh } );
 740             }
 741         );


======


A quick Google search took me to this page:

https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

Based on the response, it has something to do with converting (or attempting) the data type for this particular column. The response from the link above says the varchar data type on the column should have an explicit character limit which it seems to have it set, from the show columns statement above.

Any help would be greatly appreciated.

Thanks,

Victor
Comment 1 Victor Zuniga 2023-12-12 06:13:10 UTC
Quick clarification: We were trying to upgrade from Koha 23.06.00.006 to 23.11.00.000.
Comment 2 Victor Zuniga 2023-12-12 06:15:11 UTC
This is what I get when I try to modify the datatype for the publishercode column, per Bugzilla ticket #34029:

mysql> ALTER TABLE biblioitems MODIFY publishercode text;
ERROR 1170 (42000): BLOB/TEXT column 'publishercode' used in key specification without a key length
Comment 3 Emmi Takkinen 2023-12-12 06:52:07 UTC
Have you checked indexes for these tables? You can see them by typing "show create table biblioitems;". My guess is that there is a line 
>"KEY `publishercode` (`publishercode`)" 
there which should be something like 
>"KEY `publishercode` (`publishercode`(191))"
Comment 4 Victor Zuniga 2023-12-12 07:43:12 UTC
Hi Emmi,

Thanks for the help!

Yes, the line was showing as you mentioned.

I solved the issue by running the following commands:

ALTER TABLE biblioitems drop key publishercode, add key publishercode (publishercode (191));


 ALTER TABLE deletedbiblioitems drop key publishercode, add key publishercode (publishercode (191));


However, now I am getting a new error message:

Upgrade to 23.06.00.013 [00:36:57]: Bug 28966 - Holds queue view too slow to load for large numbers of holds
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741

When I run the "show create table tmp_holdsqueue", this is what I get:


============



mysql> show create table tmp_holdsqueue;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp_holdsqueue | CREATE TABLE `tmp_holdsqueue` (
  `biblionumber` int DEFAULT NULL,
  `itemnumber` int DEFAULT NULL,
  `barcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `surname` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `firstname` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `phone` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `borrowernumber` int NOT NULL,
  `cardnumber` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reservedate` date DEFAULT NULL,
  `title` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `itemcallnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `holdingbranch` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pickbranch` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `item_level_request` tinyint NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`),
  KEY `tmp_holdsqueue_ibfk_2` (`biblionumber`),
  KEY `tmp_holdsqueue_ibfk_3` (`borrowernumber`),
  CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tmp_holdsqueue_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tmp_holdsqueue_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>



=============
Comment 5 Katrin Fischer 2023-12-12 09:36:15 UTC
Hi, this problem should be fixed in 23.11.01, I pushed a fix to master yesterday, see bug 34516.

*** This bug has been marked as a duplicate of bug 34516 ***
Comment 6 Victor Zuniga 2023-12-12 17:19:53 UTC
Hi Katrin,

I ran the update and upgrade commands again this morning. Here is the output I am getting:


======


Setting up koha-common (23.11.00-1) ...
Installing new version of config file /etc/koha/apache-shared-intranet-plack.conf ...
Installing new version of config file /etc/koha/apache-shared-intranet.conf ...
Installing new version of config file /etc/koha/apache-site-https.conf.in ...
Installing new version of config file /etc/koha/koha-conf-site.xml.in ...
Installing new version of config file /etc/koha/zebradb/marc_defs/marc21/biblios/biblio-koha-indexdefs.xml ...
Installing new version of config file /etc/koha/zebradb/marc_defs/marc21/biblios/biblio-zebra-indexdefs.xsl ...
Upgrading database schema for catalog
Upgrade to 23.06.00.013  [10:26:04]: Bug 28966 - Holds queue view too slow to load for large numbers of holds
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741

dpkg: error processing package koha-common (--configure):
 installed koha-common package post-installation script subprocess returned error exit status 1


E: Sub-process /usr/bin/dpkg returned an error code (1)

======
Comment 7 Victor Zuniga 2023-12-12 18:55:21 UTC
(In reply to Katrin Fischer from comment #5)
> Hi, this problem should be fixed in 23.11.01, I pushed a fix to master
> yesterday, see bug 34516.
> 
> *** This bug has been marked as a duplicate of bug 34516 ***

Thanks Katrin!

Since this is a separate issue, I'll start a new ticket for it (Ticket #35552).

Victor