Bug 28267 - Koha-common package upgrade problem from 20.05.xx to 20.11.04-1
Summary: Koha-common package upgrade problem from 20.05.xx to 20.11.04-1
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: unspecified
Hardware: All All
: P1 - high critical with 10 votes (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
: 28635 29728 (view as bug list)
Depends on:
Blocks:
 
Reported: 2021-04-30 01:28 UTC by skaman.tx
Modified: 2022-07-25 00:03 UTC (History)
11 users (show)

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


Attachments
Bug 28267: Modify varchar from items to (TINY)TEXT (15.11 KB, patch)
2021-05-18 09:54 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description skaman.tx 2021-04-30 01:28:05 UTC
Our koha installation is on Debian 10.  Upgrade challenges str specifically related to the outcome of the Debian package koha-common upgrade scripts. We are stuck at koha-common version 20.05.xx at this point.  My latest attempt to upgrade to 20.11.04-1 resulted in the following three error messages.  Is there any way to manually alter the correct table(s) to fix the database issues? I'm not sure what the "Failed to enable unit" message means.

----------------------------
Setting up koha-common (20.11.04-1) ...
Installing new version of config file /etc/cron.daily/koha-common ...
Installing new version of config file /etc/init.d/koha-common ...
Installing new version of config file /etc/koha/SIPconfig.xml ...
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-shared-opac.conf ...
Installing new version of config file /etc/koha/koha-conf-site.xml.in ...
Installing new version of config file /etc/koha/log4perl-site.conf.in ...
Installing new version of config file /etc/koha/plack.psgi ...
Installing new version of config file /etc/koha/zebradb/etc/phrases-icu.xml ...
Installing new version of config file /etc/koha/zebradb/etc/word-phrase-utf.chr ...
Upgrading database schema for [database] .......
Upgrade to 20.06.00.031 done [10:19:38]: Bug 22789 - Add non_priority column on reserves and old_reserves tables 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 [for Statement "ALTER TABLE `items` ADD COLUMN `exclude_from_local_holds_priority` tinyint(1) default NULL AFTER `new_status`"] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 22709.

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 [for Statement "ALTER TABLE `deleteditems` ADD COLUMN `exclude_from_local_holds_priority` tinyint(1) default NULL AFTER new_status`"] at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 22715.
......
Failed to enable unit: Unit /run/systemd/generator.late/koha-common.service is transient or generated.

----------------------------

NOTE: When I then run "/usr/share/koha/bin/migration_tools/rebuild_zebra.pl -b -a -z" in this post-upgrade state I get the following error for each biblio record:


DBD::mysql::st execute failed: Unknown column 'me.exclude_from_local_holds_priority' in 'field list' [for Statement "SELECT `me`.`itemnumber`, `me`.`biblionumber`, `me`.`biblioitemnumber`, `me`.`barcode`, `me`.`dateaccessioned`, `me`.`booksellerid`, `me`.`homebranch`, `me`.`price`, `me`.`replacementprice`, `me`.`replacementpricedate`, `me`.`datelastborrowed`, `me`.`datelastseen`, `me`.`stack`, `me`.`notforloan`, `me`.`damaged`, `me`.`damaged_on`, `me`.`itemlost`, `me`.`itemlost_on`, `me`.`withdrawn`, `me`.`withdrawn_on`, `me`.`itemcallnumber`, `me`.`coded_location_qualifier`, `me`.`issues`, `me`.`renewals`, `me`.`reserves`, `me`.`restricted`, `me`.`itemnotes`, `me`.`itemnotes_nonpublic`, `me`.`holdingbranch`, `me`.`timestamp`, `me`.`location`, `me`.`permanent_location`, `me`.`onloan`, `me`.`cn_source`, `me`.`cn_sort`, `me`.`ccode`, `me`.`materials`, `me`.`uri`, `me`.`itype`, `me`.`more_subfields_xml`, `me`.`enumchron`, `me`.`copynumber`, `me`.`stocknumber`, `me`.`new_status`, `me`.`exclude_from_local_holds_priority` FROM `items` `me` WHERE ( `me`.`itemnumber` = ? )" with ParamValues: 0=1] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1836.
error retrieving biblio 1 at /usr/share/koha/bin/migration_tools/rebuild_zebra.pl line 686.

I understand this database error was previously affecting a different table and was addressed in bug 24986.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24986

Looks like a patch is needed to cure this one as well.  I am not a programmer so turning to the bug list for help.
Comment 1 skaman.tx 2021-05-04 14:52:39 UTC
Since I am new to Bugzilla postings, I may have not expressed the "Importance" of this bug correctly.  I have updated the importance to critical since the koha-common upgrade package script breaks koha, at least for the upgrade path described in the original bug report.
Comment 2 Jonathan Druart 2021-05-18 09:54:53 UTC
Created attachment 121103 [details] [review]
Bug 28267: Modify varchar from items to (TINY)TEXT

Note that barcode, branchcode and holdingbranch have not been modified.
Comment 3 Jonathan Druart 2021-05-18 09:56:36 UTC
Anyone saw that already on 20.11? This patch should fix the reported issue.

Skaman, can you tell us if you are using a modified Koha (like your items table has custom DB column)?
Comment 4 skaman.tx 2021-05-18 11:42:36 UTC
I am unaware of any specific customizations to any table columns on our end. However, this koha instance has been upgraded from prior versions for years using the Debian apt process so it is possible a prior upgrade that modified a table was missed somehow.  I am merely speculating here and have no direct information that occurred.
Comment 5 Martin Renvoize 2021-05-26 13:28:54 UTC
(In reply to Jonathan Druart from comment #3)
> Anyone saw that already on 20.11? This patch should fix the reported issue.
> 
> Skaman, can you tell us if you are using a modified Koha (like your items
> table has custom DB column)?

We've not seen this issue here yet and have a few customers running 20.11 now.

The patch looks reasonable.
Comment 6 Jonathan Druart 2021-05-26 13:42:02 UTC
Lowering severity as it's not confirmed on master, but marking as a candidate for early push during the next cycle.
Comment 7 skaman.tx 2021-05-26 14:49:25 UTC
Thank you for the attention to this.  I there a way to run the patch as an individual perl script now versus waiting for the next release cycle?  I'd really like to get our installation caught up now versus falling farther behind.
Comment 8 Jonathan Druart 2021-05-26 15:02:28 UTC
(In reply to skaman.tx from comment #7)
> Thank you for the attention to this.  I there a way to run the patch as an
> individual perl script now versus waiting for the next release cycle?  I'd
> really like to get our installation caught up now versus falling farther
> behind.

Apply the patch and execute the updatedatabase.pl script (in a test environment first please!)
Comment 9 skaman.tx 2021-05-26 15:21:09 UTC
Understood. Please forgive my ignorance here as I am not a KOHA or perl programmer. Where/how do I apply the patch? The rest I understand.
Comment 10 Jonathan Druart 2021-05-26 15:43:58 UTC
(In reply to skaman.tx from comment #9)
> Understood. Please forgive my ignorance here as I am not a KOHA or perl
> programmer. Where/how do I apply the patch? The rest I understand.

The patch is adding a new file "installer/data/mysql/atomicupdate/bug_28267.perl"
Create it with its content at this location.
Comment 11 skaman.tx 2021-05-26 16:05:59 UTC
Thank you.
Comment 12 skaman.tx 2021-06-14 17:54:12 UTC
(In reply to Jonathan Druart from comment #10)
> (In reply to skaman.tx from comment #9)
> > Understood. Please forgive my ignorance here as I am not a KOHA or perl
> > programmer. Where/how do I apply the patch? The rest I understand.
> 
> The patch is adding a new file
> "installer/data/mysql/atomicupdate/bug_28267.perl"
> Create it with its content at this location.

Jonathan,

I fear I just am not getting this right.  The only "content" to put into the "bug_28267.perl" file is the attachment you provided which looks like a diff file.  Thus, I'm not sure what to copy from the patch you wrote into the "bug_28267.per" file.  Can you guide me here?  I know where the patch file goes from your response above....just not what precise information goes into the file.

Sorry for my ignorance here.

Scott
Comment 13 Katrin Fischer 2021-06-14 19:04:36 UTC
You are right that the attachment is a diff file. So you can't use the file as is, but have to recreate the changes listed in it locally. It requires some manual work if you are not using git, like copying the correct lines, creating the file with the name, removing the + at the beginning of the lines...
Comment 14 skaman.tx 2021-06-28 03:45:35 UTC
So I am not a developer and I do not know how to patch the koha files using git.  Thus, as Kartin suggested I would have to do, I manually edited the diff file Jonathan posted and I created the installer/data/mysql/atomicupdate/bug_28267.perl file with the following contents:

$DBversion = 'XXX'; # will be replaced by the RM
if( CheckVersion( $DBversion ) ) {
    my $fields = {
        itemcallnumber           => 255,
        coded_location_qualifier => 10,
        location                 => 80,
        permanent_location       => 80,
        cn_source                => 10,
        cn_sort                  => 255,
        ccode                    => 80,
        itype                    => 10,
        copynumber               => 32,
        stocknumber              => 32,
        new_status               => 32,
    };
    # Index on items
    for my $index ( qw( itemstocknumberidx itemcallnumber items_location items_ccode itype_idx ) ) {
        next unless index_exists('items', $index);
        $dbh->do(qq|
            ALTER TABLE items DROP INDEX $index
        |);
    }

    # Index on deleteditems
    for my $index ( qw( delitemstocknumberidx itype_idx ) ) {
        next unless index_exists('deleteditems', $index);
        $dbh->do(qq|
            ALTER TABLE deleteditems DROP INDEX $index
        |);
    }

    while ( my ( $f, $size ) = each %$fields ) {
        my $datatype = ( $size >= 24 ) ? 'TEXT' : 'TINYTEXT';
        my $size_index = ( $size == 255 ) ? 191 : $size;

        $dbh->do(qq|
            ALTER TABLE items MODIFY $f $datatype DEFAULT NULL
        |);

        # Re-add index on items
        unless ( index_exists( 'items', sprintf("%s_idx", $f) ) ) {
            $dbh->do(sprintf q|
                ALTER TABLE items ADD INDEX %s_idx( %s(%s) )
            |, $f, $f, $size_index);
            }

        $dbh->do(qq|
            ALTER TABLE deleteditems MODIFY $f $datatype DEFAULT NULL
        |);
    }

    # Re-add index on deleteditems
    unless ( index_exists( 'deleteditems', 'itype_idx' ) ) {
        $dbh->do(q|
            ALTER TABLE deleteditems ADD INDEX itype_idx( itype(10))
        |);
    }
    unless ( index_exists( 'deleteditems', 'stocknumber_idx' ) ) {
        $dbh->do(q|
            ALTER TABLE deleteditems ADD INDEX stocknumber_idx( itype(32))
        |);
    }

    NewVersion( $DBversion, 28267, "Switch items VARCHAR fields to TINYTEXT or MEDIUMTEXT");
}


I then manually edited the kohastructure.sql file adding the "+" lines from the diff file and removing the "-" lines.  I made sure all the added lines instances of "default NULL" were "default NULL," as well.

I set the following variables via the debian command line:

  export PERL5LIB=/usr/share/koha/lib
  export KOHA_CONF=/etc/koha/sites/library/koha-conf.xml

And then I ran:

  perl /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl

Alas, I get the following 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 [for Statement "
            ALTER TABLE `items` ADD COLUMN `exclude_from_local_holds_priority` tinyint(1) default NULL AFTER `new_status`
        "]  at /usr/share/perl5/DBIx/Class/Schema.pm line 1118.
        DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5616a4e90248), "DBI Exception: DBD::mysql::db do failed: Row size too large. "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113
        DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5616a3f14ff8), "DBI Exception: DBD::mysql::db do failed: Row size too large. "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1501
        DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Row size too large. The maximum row"..., DBI::db=HASH(0x5616a5993ba8), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 22720


Any further thoughts?
Comment 15 Mason James 2021-06-28 12:09:55 UTC
(In reply to skaman.tx from comment #14)
> 
> Any further thoughts?

hi skaman

running this SQL before the upgrade might fix the error...
 ALTER TABLE `items` ROW_FORMAT=DYNAMIC;

more info... 
 https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/
Comment 16 skaman.tx 2021-09-06 15:39:43 UTC
Mason's reply was the answer.  Instead of going through all the patching above, merely running the following two sql queries on the library database allowed the koha-common upgrade to complete without error.

ALTER TABLE `deleteditems` ROW_FORMAT=DYNAMIC;
ALTER TABLE `items` ROW_FORMAT=DYNAMIC;

Thank you, Mason!
Comment 17 David Cook 2021-09-27 04:23:57 UTC
Having a similar problem on 50% of 21.05 instances I've upgraded, but different table in question:

{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 TXT or BLOBs [for Statement "ALTER TABLE borrowers ADD COLUMN autorenew_checkouts TINYINT(1) NOT NULL DEFAULT 1 AFTER anonymized"]  at /usr/share/perl5/DBIx/Class/Schema.pm line 1118.
        DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x562bc062d928), "DBI Exception: DBD::mysql::db do failed: Row size too large. "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113
        DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x562bc077f160), "DBI Exception: DBD::mysql::db do failed: Row size too large. "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1501
        DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Row size too large. The maximum row"..., DBI::db=HASH(0x562bc20628b8), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl lie 21313
Comment 18 David Cook 2021-09-27 04:37:54 UTC
(In reply to Mason James from comment #15)
> (In reply to skaman.tx from comment #14)
> > 
> > Any further thoughts?
> 
> hi skaman
> 
> running this SQL before the upgrade might fix the error...
>  ALTER TABLE `items` ROW_FORMAT=DYNAMIC;
> 
> more info... 
>  https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-
> innodb/

Cheers, Mason!

When I ran some SQL from that troubleshooting page, I noticed that all the Koha tables were using "Compact" row format. 

The database I was using was set up a long time ago, and it did in-place upgrades that retained the legacy settings it seems.

From 10.2.x, the default has been Dynamic. 

I think this bug report could probably be closed, although perhaps we could have an audit script for double-checking some MySQL settings before upgrading I guess... that's a bit fraught though.
Comment 19 Mason James 2021-11-06 01:37:29 UTC
> From 10.2.x, the default has been Dynamic. 
> 
> I think this bug report could probably be closed, although perhaps we could
> have an audit script for double-checking some MySQL settings before
> upgrading I guess... that's a bit fraught though.

this seems like a great idea, perhaps a warning on the about.pl pages too?
Comment 20 Liz Rea 2021-11-29 15:26:32 UTC
I've seen this as well, very often in Koha where the database server started out in 2012-era ubuntu, and the database has never been dropped and recreated/reimported since.

Updating the rows to dynamic solved it. We went whole hog and did all the tables since Koha was the only thing in the affected database server.

This will spit out the commands necessary to alter all of the tables, you can pick and choose which ones you want, or do them all at once: 

for table in $(mysql --batch --skip-column-names --defaults-extra-file=/etc/mysql/debian.cnf --execute="SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT IN('Redundant', 'Compact') AND TABLE_NAME NOT IN('SYS_DATAFILES', 'SYS_FOREIGN', 'SYS_FOREIGN_COLS', 'SYS_TABLESPACES', 'SYS_VIRTUAL', 'SYS_ZIP_DICT', 'SYS_ZIP_DICT_COLS');"); do echo "ALTER TABLE ${table} ROW_FORMAT=DYNAMIC;"; done;

Be a little careful, this will spit out all tables in all databases. Be sure that's what you want to do before going and pasting that into a mysql cli.
Comment 21 Liz Rea 2021-11-29 15:28:27 UTC
* be careful and don't just copy/paste willy nilly, be sure you're only getting the Koha database tables. They'll be easy to spot. You run the command given in the shell, then that command's output can be copied/pasted to the MariaDB/MySQL cli. Sorry I wasn't clear the first time.
Comment 22 Fridolin Somers 2021-12-18 07:15:59 UTC
*** Bug 28635 has been marked as a duplicate of this bug. ***
Comment 23 Hayley Pelham 2021-12-20 20:33:42 UTC
*** Bug 29728 has been marked as a duplicate of this bug. ***
Comment 24 Fridolin Somers 2021-12-30 08:04:45 UTC
We also need to impact borrowers table right ?
Comment 25 Katrin Fischer 2022-01-02 15:13:45 UTC
It looks like the borrower_modifications table can also be affected as we had this one failing with the known "Row size too large." error:

$DBversion = "16.12.00.001";
if ( CheckVersion($DBversion) ) {
    $dbh->do(q{
        ALTER TABLE borrower_modifications
        ADD COLUMN extended_attributes text DEFAULT NULL
        AFTER privacy
    });

    print "Upgrade to $DBversion done (Bug 17767 - Let Koha::Patron::Modification handle extended attributes)\n";
    SetVersion($DBversion);
}

Is there a way we could fix this once and for all? Or at least provide information/warning to users?
Comment 26 Jonathan Druart 2022-01-04 07:43:40 UTC
(In reply to Katrin Fischer from comment #25)
> 
> Is there a way we could fix this once and for all? Or at least provide
> information/warning to users?

We can eventually run this query and display a warning on the about page if some tables are not using "Dynamic".

SELECT table_name,row_format FROM information_schema.tables WHERE table_schema='koha_kohadev' AND row_format != "Dynamic";

A link to a wiki page explaining the problem and providing a solution would be great as well.
Comment 27 Katrin Fischer 2022-01-04 08:24:06 UTC
Could it make sense to stop the installer until the tables have been fixed? Like we do for missing dependencies?
Comment 28 Martin Renvoize 2022-05-11 16:15:16 UTC
So are we agreed we should stop the installer/upgrade with a sane error message and warn in the about page for non-dynamic installs?
Comment 29 David Cook 2022-07-22 06:15:55 UTC
This is arguably a database management problem rather than a Koha problem per se, no?
Comment 30 Martin Renvoize 2022-07-22 08:00:25 UTC
Yes and no.. the packages are generally expected to just "do the right thing" so if there is an appraisal we can take to "fix" peoples databases, we probably should.
Comment 31 David Cook 2022-07-25 00:03:20 UTC
(In reply to Martin Renvoize from comment #30)
> Yes and no.. the packages are generally expected to just "do the right
> thing" so if there is an appraisal we can take to "fix" peoples databases,
> we probably should.

I suppose it's a fine line. Maybe it does make sense to change all Koha tables over to DYNAMIC if they're not already. As I noted above, it looks like if you in-place upgrade your database, it'll retain COMPACT and not automatically update, so I suppose that does leave the responsibility to Koha...

(As a side note, I looked at https://wiki.koha-community.org/wiki/System_requirements_and_recommendations and https://wiki.debian.org/LTS, and it looks like Debian 9 reached it's EOL LTS a month ago. We might want to stop supporting it too. I'll send out an email about that. Debian 10 will be regular EOL in the next month or two it seems.)