[Koha] Solved! Error upgrading to 22.05

[King, Fred]

Esteemed Colleagues,

This may be something everybody else knows already, or may apply only in unusual circumstances, or may be valid only for my Koha instance. I don't know. I am only an egg. But just in case I'm not the only person having this problem, here's how I solved it. The solution is online if you look for it, but I'm putting it all together in one post, which I haven't found elsewhere. If you get bored, feel free to hit the delete key.

When I tried to upgrade my Koha instance to 22.05, I got the following message:
   ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs at /usr/share/koha/lib/C4/Installer.pm line 739

When I tried to go to the admin page, I got the setup screen, which gave me a similar message. Nick Clemens (thanks Nick!) pointed me to this bug, and this specific message:
   https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28267#c16

The problem lay not in one row format, but in all of them. I set up my instance with 3.12, way back when the row format was Compact. Yes, maybe I should refresh entirely, but not today. I upgraded a scratch monkey instance and it went without a hitch. Then I upgraded the production instance. That worked, too. I also upgraded the scratch monkey to Ubuntu 20.22LTS without a problem, but I'm saving the production instance for another day. I also found out the difference between a backtick and a single quote. (` vs ' in Outlook)

Here's what I did:


Update and upgrade until nothing is left to upgrade.
In this instance, fwupd and koha-common were hald back

   apt-get upgrade fwupd
   apt-get upgrade koha-common (21.05.19-1)

   apt autoremove
After this, nothing is held back.


Edit
   /etc/apt/sources.list.d/koha.list
to say
   deb http://debian.liha-community.org/koha stable main

Update/upgrade

Upgrade packages held back EXCEPT koha-common:
   apt-get upgrade libmojolicious-perl

Having Koha tables in Compact row format broke the upgrade process. Check the table status for current Koha instance.

Start MariaDB
   mariadb
   use koha_library
   show table status
Every table status was Compact, so I needed to change all of them to dynamic. Run this to find list the tables and the command to change them:

SELECT CONCAT(
  'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
  'ROW_FORMAT=DYNAMIC;'
) AS _alter
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='InnoDB' AND ROW_FORMAT <> 'DYNAMIC';


The output should look like several hundred rows of this after you remove all the "|" around it:
   ALTER TABLE `koha_library`.`borrower_attributes` ROW_FORMAT=DYNAMIC;
   ALTER TABLE `koha_library`.`borrower_message_preferences` ROW_FORMAT=DYNAMIC;
   ALTER TABLE `koha_library`.`oauth_access_tokens` ROW_FORMAT=DYNAMIC;

Copy the output and past it into the MariaDB prompt. (I copied, pasted it into Notebook, then pasted back into MariaDB. Copying and pasting will depend on what interface you're using. Using Ctrl C to copy in MariaDB doesn't work.)
   MariaDB [koha_library]> (paste here)

You probably don't ahve to do this, but it doesn't hurt anything:
   apt-get update
   apt-get upgrade
But this might remove soemthing unnecessary:
   apt autoremove
Then update to the version in /etc/apt/sources.list.d/koha.list
   apt-get upgrade koha-common
This time, everything went smoothly.

Fred King, MSLS, AHIP
Medical Librarian, MedStar Washington Hospital Center
fred.king at medstar.net
202-877-6670
ORCID 0000-0001-5266-0279
MedStar Authors Catalog: http://medstarauthors.org

My parents sent me to mime camp when I was nine. I still can't talk about it.
--Anon

----------------------------------------------------------------------
MedStar Health is a not-for-profit, integrated healthcare delivery system, the largest in Maryland and the Washington, D.C., region. Nationally recognized for clinical quality in heart, orthopedics, cancer and GI.

IMPORTANT: This e-mail (including any attachments) may contain information that is private, confidential, or protected by attorney-client or other privilege. If you received this e-mail in error, please delete it from your system without copying it and notify sender by reply e-mail, so that our records can be corrected... Thank you.

Help conserve valuable resources - only print this email if necessary.