Bug 28267 - Older databases fail to upgrade due to having a row format other than "DYNAMIC"
Summary: Older databases fail to upgrade due to having a row format other than "DYNAMIC"
Status: RESOLVED FIXED
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 20 votes (vote)
Assignee: Jonathan Druart
QA Contact: Marcel de Rooy
URL:
Keywords:
: 28635 29728 (view as bug list)
Depends on:
Blocks:
 
Reported: 2021-04-30 01:28 UTC by skaman.tx
Modified: 2024-01-04 11:16 UTC (History)
16 users (show)

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


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
Bug 28267: Add warning on about.pl for database row formats (4.71 KB, patch)
2023-01-18 03:25 UTC, David Cook
Details | Diff | Splinter Review
Bug 28267: Add warnings for database row formats (11.48 KB, patch)
2023-01-18 05:50 UTC, David Cook
Details | Diff | Splinter Review
Bug 28267: Add warnings for database row formats (11.53 KB, patch)
2023-01-18 12:07 UTC, Owen Leonard
Details | Diff | Splinter Review
Bug 28267: Add warnings for database row formats (11.57 KB, patch)
2023-01-20 10:56 UTC, Marcel de Rooy
Details | Diff | Splinter Review
Bug 28267: Add warnings for database row formats (12.05 KB, patch)
2023-05-04 07:08 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28267: Simplify things (8.11 KB, patch)
2023-05-04 07:08 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 28267: Add warnings for database row formats (12.09 KB, patch)
2023-05-04 10:56 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28267: Simplify things (7.70 KB, patch)
2023-05-04 10:56 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 28267: [22.05] Add warnings for database row formats (11.88 KB, patch)
2023-08-17 04:53 UTC, Victor Grousset/tuxayo
Details | Diff | Splinter Review
Bug 28267: [22.05] Simplify things (7.88 KB, patch)
2023-08-17 04:54 UTC, Victor Grousset/tuxayo
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.)
Comment 32 Jonathan Druart 2022-11-11 14:36:50 UTC
Shouldn't we lower the severity of this bug?
Comment 33 Matthew Ross 2022-12-28 18:05:06 UTC
Greetings.

I'm having this issue. I decided to finially update my Koha server, since I had left it alone for some time now. I was running Debian 9, but I had successfully updated to Debian 11. Previous version of Koha was 22.05, and now I'm trying to upgrade to Koha Stable (22.11).

I'm receiving the followin error, even after manually changing the "borrowers" table to dynamic:

---
Upgrade to 22.06.00.006 [09:37:59]: Bug 21978 - Add middle_name to borrowers table
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
---

My limited understanding is that the newer versions of MariaDB (and defaults) to the DYNAMIC table/row type, which is much more flexible. But my older install must be using the older type(s).

My SQL knowledge is limited, so I may have to revert to a backup of this server to get it back and running. Any help would be appreciated.
Comment 34 David Cook 2023-01-02 22:18:49 UTC
(In reply to Matthew Ross from comment #33)
> I'm receiving the followin error, even after manually changing the
> "borrowers" table to dynamic:
> 
> ---
> Upgrade to 22.06.00.006 [09:37:59]: Bug 21978 - Add middle_name to borrowers
> table
> 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
> ---

Looking at bug 21978, it looks like you'll need to change the borrower_modifications and deletedborrowers tables too.

Alternatively, you could follow the steps that Liz Rea outlines at https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=28267#c20 to update all tables. That would be a more future-proofed solution.
Comment 35 David Cook 2023-01-18 03:20:54 UTC
Changing title from "Koha-common package upgrade problem from 20.05.xx to 20.11.04-1" to something more descriptive...
Comment 36 David Cook 2023-01-18 03:25:57 UTC
Created attachment 145380 [details] [review]
Bug 28267: Add warning on about.pl for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

Test plan:
0. Apply the patch
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

Note: Due to bug 32665 you'll see an unrelated warning on
step 3
Comment 37 David Cook 2023-01-18 03:27:23 UTC
OK I've added the about.pl warning, but that's only going to help people that see the warning *before* they try to upgrade their database...
Comment 38 David Cook 2023-01-18 03:28:19 UTC
(In reply to Martin Renvoize from comment #28)
> 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?

Yeah, in retrospect, I think that probably makes sense. 

Apparently it's 2:30pm and I haven't had lunch yet, so I think I'll leave the "stop the installer" for someone else, or for me for another day...
Comment 39 David Cook 2023-01-18 05:50:26 UTC
Created attachment 145386 [details] [review]
Bug 28267: Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3
Comment 40 David Cook 2023-01-18 05:54:54 UTC
Ok, I've done all the things.

The warning message could be re-worded by myself or QA. Please provide feedback on that.

The wiki page "Database_row_format" also doesn't share exact details about how to detail with the problem. Rather, it refers back to this bug report and the MariaDb troubleshooting page.

--

Hopefully now the majority of the hard work is done, so we can get this one resolved soon.
Comment 41 Owen Leonard 2023-01-18 12:07:35 UTC
Created attachment 145390 [details] [review]
Bug 28267: Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3

Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Comment 42 Owen Leonard 2023-01-18 12:08:11 UTC
QA tool has this complaint but I assume it's trivial to fix:

 FAIL	t/db_dependent/Koha/Installer.t
   FAIL	  file permissions
		File must have the exec flag
Comment 43 Marcel de Rooy 2023-01-20 10:55:37 UTC
+        my $sth = $dbh->prepare($sql);
+        $sth->execute($database);
+        my $row = $sth->fetchrow_arrayref;
+        my $count = $row->[0];
+        if ($count){
+            $result{count} = $count;
+        }
Why do you test $count here? And why not shorter like : $result{count} = $dbh->selectrow_arrayref->[0] ?

+    $schema->storage->txn_begin;
+    my $dbh = C4::Context->dbh;
+    $dbh->do("ALTER TABLE tags row_format=COMPACT;");

False idea of security? The txn cant have an ALTER TABLE, right?
Comment 44 Marcel de Rooy 2023-01-20 10:56:20 UTC
Created attachment 145507 [details] [review]
Bug 28267: Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3

Signed-off-by: Owen Leonard <oleonard@myacpl.org>
Comment 45 Marcel de Rooy 2023-01-20 10:56:49 UTC
(In reply to Owen Leonard from comment #42)
> QA tool has this complaint but I assume it's trivial to fix:
> 
>  FAIL	t/db_dependent/Koha/Installer.t
>    FAIL	  file permissions
> 		File must have the exec flag

chmod 755  t/db_dependent/Koha/Installer.t
Fixed
Comment 46 David Cook 2023-01-23 05:51:13 UTC
(In reply to Marcel de Rooy from comment #45)
> (In reply to Owen Leonard from comment #42)
> > QA tool has this complaint but I assume it's trivial to fix:
> > 
> >  FAIL	t/db_dependent/Koha/Installer.t
> >    FAIL	  file permissions
> > 		File must have the exec flag
> 
> chmod 755  t/db_dependent/Koha/Installer.t
> Fixed

Thanks for that. The test was a last minute addition.
Comment 47 David Cook 2023-01-23 06:01:40 UTC
(In reply to Marcel de Rooy from comment #43)
> +        my $sth = $dbh->prepare($sql);
> +        $sth->execute($database);
> +        my $row = $sth->fetchrow_arrayref;
> +        my $count = $row->[0];
> +        if ($count){
> +            $result{count} = $count;
> +        }
> Why do you test $count here? And why not shorter like : $result{count} =
> $dbh->selectrow_arrayref->[0] ?

I think there's a mistake in that selectrow_arrayref syntax, but overall yeah that could be used instead.

The trio of statements is habit/the usual practice in Koha I think.

As for testing $count... that's also habit. I usually test return values, although I think you could argue in this case that it would be better not to test $count.

It's a simple change, so I just "did the usual". 

> +    $schema->storage->txn_begin;
> +    my $dbh = C4::Context->dbh;
> +    $dbh->do("ALTER TABLE tags row_format=COMPACT;");
> 
> False idea of security? The txn cant have an ALTER TABLE, right?

The txn can contain the ALTER TABLE, but I don't think rolling back does anything. I think the commit happens implicitly behind the scenes.

The txn_begin bit is copy/pasted from other tests.
Comment 48 David Cook 2023-01-23 06:03:24 UTC
I'm happy for this to be marked "Failed QA" and for someone else to take it over.
Comment 49 Jonathan Druart 2023-05-04 07:07:21 UTC
critical with simple rebase that is abandoned? why?...
Comment 50 Jonathan Druart 2023-05-04 07:08:10 UTC
Created attachment 150627 [details] [review]
Bug 28267: Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3

Signed-off-by: Owen Leonard <oleonard@myacpl.org>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 51 Jonathan Druart 2023-05-04 07:08:14 UTC
Created attachment 150628 [details] [review]
Bug 28267: Simplify things

We don't need:
* a separate Koha::Installer module when we already have
C4::Installer
* the tests as they are actually modifying the DB structure
without rolling back
* An unecessary complicated subroutine, just make is simple

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 52 Nick Clemens 2023-05-04 10:56:26 UTC
Created attachment 150645 [details] [review]
Bug 28267: Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3

Signed-off-by: Owen Leonard <oleonard@myacpl.org>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 53 Nick Clemens 2023-05-04 10:56:29 UTC
Created attachment 150646 [details] [review]
Bug 28267: Simplify things

We don't need:
* a separate Koha::Installer module when we already have
C4::Installer
* the tests as they are actually modifying the DB structure
without rolling back
* An unecessary complicated subroutine, just make is simple

WNC amended patch: Remove change to Koha.pm

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 54 Tomás Cohen Arazi 2023-05-05 13:14:49 UTC
Pushed to master for 23.05.

Nice work everyone, thanks!
Comment 55 Mengu Yazicioglu 2023-05-10 08:42:56 UTC
Hi all,

Does this patch only change row format of table "tags" ?

I've manually changed that table but didn't pass the installation phase.
I've found some other tables like biblioitems,branches,borrowers,etc.
Comment 56 Jonathan Druart 2023-05-10 08:59:25 UTC
(In reply to Mengu Yazicioglu from comment #55)
> Hi all,
> 
> Does this patch only change row format of table "tags" ?
> 
> I've manually changed that table but didn't pass the installation phase.
> I've found some other tables like biblioitems,branches,borrowers,etc.

This patch does not modify the DB, it only displays a warning on the about page.
Comment 57 Mengu Yazicioglu 2023-05-10 10:45:03 UTC
When we try to upgrade to 22.11.05, post-installation database update part gives error about ERM tables related with this bug.

In order to bypass the post-installation steps, we need manually change some tables' row format status, otherwise we can't go forward.
Comment 58 David Cook 2023-05-10 23:44:50 UTC
(In reply to Mengu Yazicioglu from comment #57)
> When we try to upgrade to 22.11.05, post-installation database update part
> gives error about ERM tables related with this bug.
> 
> In order to bypass the post-installation steps, we need manually change some
> tables' row format status, otherwise we can't go forward.

What version of MySQL are you using and on what OS?
Comment 59 Mengu Yazicioglu 2023-05-11 12:19:28 UTC
(In reply to David Cook from comment #58)
> (In reply to Mengu Yazicioglu from comment #57)
> > When we try to upgrade to 22.11.05, post-installation database update part
> > gives error about ERM tables related with this bug.
> > 
> > In order to bypass the post-installation steps, we need manually change some
> > tables' row format status, otherwise we can't go forward.
> 
> What version of MySQL are you using and on what OS?

We upgraded from Debian 9 to Debian 11 and Mariadb 10.1 to 10.5 and it is not possible to bypass to change related tables row_format to DYNAMIC.

I've updated wiki page for solution.

https://wiki.koha-community.org/wiki/Database_row_format
Comment 60 David Cook 2023-05-11 23:32:25 UTC
(In reply to Mengu Yazicioglu from comment #59)
> We upgraded from Debian 9 to Debian 11 and Mariadb 10.1 to 10.5 and it is
> not possible to bypass to change related tables row_format to DYNAMIC.

I don't understand what you're saying here.
Comment 61 Mengu Yazicioglu 2023-05-12 13:02:29 UTC
(In reply to David Cook from comment #60)
> (In reply to Mengu Yazicioglu from comment #59)
> > We upgraded from Debian 9 to Debian 11 and Mariadb 10.1 to 10.5 and it is
> > not possible to bypass to change related tables row_format to DYNAMIC.
> 
> I don't understand what you're saying here.

As you know, if there is a change in database structure, we have to run post installation steps from intranet part. Due to row format issues of some tables, newly added ERM tables in the post installation steps gave many errors saying "Row size too large..."

In order to finish upgrade we have to click "Try again" button but first solve the row format issue. In a new terminal, we have to change row_format feature of related tables to DYNAMIC.

After solving this issue, we may go through Try again and post database installation steps will pass

I hope I am explained well now.
Comment 62 Martin Renvoize 2023-05-12 14:05:36 UTC
Many hands makes light work, thankyou everyone!

Pushed to 22.11.x for the next release
Comment 63 David Cook 2023-05-14 23:16:46 UTC
(In reply to Mengu Yazicioglu from comment #61)
> As you know, if there is a change in database structure, we have to run post
> installation steps from intranet part. Due to row format issues of some
> tables, newly added ERM tables in the post installation steps gave many
> errors saying "Row size too large..."
> 
> In order to finish upgrade we have to click "Try again" button but first
> solve the row format issue. In a new terminal, we have to change row_format
> feature of related tables to DYNAMIC.

Thanks, Mengu. 

I'm a bit surprised that the error would appear when adding new tables, since they should default to the DYNAMIC format when you're on a newer MySQL/MariaDB on a newer OS. Maybe something more to look at with your installation...
Comment 64 Lucas Gass 2023-05-15 22:50:59 UTC
Merge conflicts with 22.05.x, please rebase.
Comment 65 Victor Grousset/tuxayo 2023-08-16 09:10:32 UTC
Will that warning ever be visible in a real installation impacted by this?
Since to see the warning, one must have been able to do many DB upgrades to reach 22.11.x and will certainly (IIUC) hit the issue on a few of the tables that had alterations in the last few years.
I guess if they only fix the blocking tables one at a time, they will still have most the of the table with the row format. And hence the warning will be useful to hopefully get them all fixed and not hit this again in the future.
Is that the main uses that? (admins that didn't fix all the tables)
Comment 66 Victor Grousset/tuxayo 2023-08-17 04:53:55 UTC
Created attachment 154481 [details] [review]
Bug 28267: [22.05] Add warnings for database row formats

This patch adds a warning on about.pl regarding database row formats
other than "DYNAMIC". It points to the Koha community wiki for more
information on how to resolve the problem.

This patch also stops the installer if there are any database tables
with a row format other than "DYNAMIC". It points to the Koha community
wiki for more information on how to resolve the problem.

Test plan:
0a. Apply the patch
0b. koha-plack --restart kohadev
1a. koha-mysql kohadev
1b. ALTER TABLE tags ROW_FORMAT=COMPACT;
2. Go to http://localhost:8081/cgi-bin/koha/about.pl
3. Note that there is a warning about database row formats
4a. koha-mysql kohadev
4b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
5. Go to http://localhost:8081/cgi-bin/koha/about.pl
6. Note that there is no warning about database row formats

7a. Manually change the version in Koha.pm to a higher version
7b. koha-plack --restart kohadev
8a. koha-mysql kohadev
8b. ALTER TABLE tags ROW_FORMAT=COMPACT;
9. Go to http://localhost:8081/
10. Log into the web installer and click through until you reach
a warning telling you that you have database row formats other
than 'DYNAMIC'
11. Note that you can't progress with the installer until
this problem is resolved
12a. koha-mysql kohadev
12b. ALTER TABLE tags ROW_FORMAT=DYNAMIC;
13. Refresh the page or redo the web installer process
14. Note that you're no longer blocked from running the installer
due to database row format
15. Undo your change to Koha.pm
16. Profit

Note: Due to bug 32665 you'll see an unrelated warning on
step 3

Signed-off-by: Owen Leonard <oleonard@myacpl.org>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(cherry picked from commit b76fb9cf466dd4cc4eb4d382c4bfb9fa78a1fce6)
Rebased-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Comment 67 Victor Grousset/tuxayo 2023-08-17 04:54:01 UTC
Created attachment 154482 [details] [review]
Bug 28267: [22.05] Simplify things

We don't need:
* a separate Koha::Installer module when we already have
C4::Installer
* the tests as they are actually modifying the DB structure
without rolling back
* An unecessary complicated subroutine, just make is simple

WNC amended patch: Remove change to Koha.pm

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
(cherry picked from commit f394bf61cf7e60058afd9856f7ae8dda8afb385b)
Signed-off-by: Victor Grousset/tuxayo <victor@tuxayo.net>
Comment 68 Victor Grousset/tuxayo 2023-08-17 04:54:54 UTC
(In reply to Lucas Gass from comment #64)
> Merge conflicts with 22.05.x, please rebase.

Here is a rebase :)
Test plan done to check that it works.
Comment 69 Rudolf Byker 2024-01-04 11:16:49 UTC
Here is my script to fix old installations before upgrading: https://pastebin.com/5ujqW3H0

I'll add it to https://wiki.koha-community.org/wiki/Database_row_format once I can get into the wiki (see https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34637#c10 ).