From fb5ed738dbe767caf353b3d44bdaca63153c795a Mon Sep 17 00:00:00 2001
From: Kevin Carnes <kevin.carnes@ub.lu.se>
Date: Mon, 27 Nov 2023 09:40:55 +0100
Subject: [PATCH] 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>
---
 installer/data/mysql/db_revs/230600007.pl | 20 ++++++++++++++++++++
 1 file changed, 20 insertions(+)

diff --git a/installer/data/mysql/db_revs/230600007.pl b/installer/data/mysql/db_revs/230600007.pl
index 92b0736cbb..fb4b1f43dd 100755
--- a/installer/data/mysql/db_revs/230600007.pl
+++ b/installer/data/mysql/db_revs/230600007.pl
@@ -17,16 +17,36 @@ return {
         });
         say $out "Updated deletedbiblioitems.place to text";
 
+        $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)'
         });
         say $out "Updated biblioitems.publishercode to text";
+        $dbh->do(q{
+            ALTER TABLE `biblioitems`
+            ADD INDEX `publishercode` (`publishercode`(191))
+        });
+        say $out "Create index on biblioitems.publishercode";
+        $dbh->do(q{
+            ALTER TABLE `deletedbiblioitems`
+            DROP INDEX `publishercode`
+        });
+        say $out "Remove index on deletedbiblioitems.publishercode";
         $dbh->do(q{
             ALTER TABLE `deletedbiblioitems`
             MODIFY COLUMN `publishercode` text DEFAULT NULL COMMENT 'publisher (MARC21 260$b and 264$b)'
         });
         say $out "Updated deletedbiblioitems.publishercode to text";
+        $dbh->do(q{
+            ALTER TABLE `deletedbiblioitems`
+            ADD INDEX `publishercode` (`publishercode`(191))
+        });
+        say $out "Create index on deletedbiblioitems.publishercode";
 
         $dbh->do(q{
             ALTER TABLE `biblioitems`
-- 
2.30.2