Upgrade fails with Row size too large error

Question

Upgrading CiviCRM from 4.6.26 to 4.7.16 is failing at step Upgrade DB to 4.7.beta6: SQL with the following error:

ALTER TABLE civicrm_event ADD COLUMN max_additional_participants int(10) unsigned DEFAULT 0 COMMENT 'Maximum number of additional participants that can be registered on a single booking' AFTER is_multiple_registrations [nativecode=1118 ** Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.]

Everything I can find says that this is a fairly fundamental MySQL restriction but I can't find any much trace of people hitting it with CiviCRM at all.

The MySQL server version is 5.5.54 which should be supported, and I have tried increasing the InnoDB log file size from 5Mb to 48Mb as some things say that will help but it made no difference.

Answer 1

Ok so the problem is Multi Lingual with CiviCRM adds a per language column to the tables where its needed. In the case of the civi_event table this is one of the most troublesome as it has quite a lot of columns that are needed in the different languages.

What I've done to get round this in the past is clear out the relevant data from the fields i.e. create a copy of civi_event as it stands, delete the data in the multilingual description and then try the upgrade again. This was in the earlier versions of MySQL where the rowsize wasn't really controllable.

More recent versions of MySQL have some config options to allow you to setup your MySQL a little differently, I haven't tried the file format options in this thread but we do run table per file regularly.

https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large

Please do let us know how you get on, its a common problem for multi language CiviCRM installs.

Answer 2

I have this issue with an upgrade to 5.35.1 from 5.30.1. In my case, it was due to the fact that the site was originally installed with mariadb 10.1 and later upgraded to 10.2. My solution was to convert the row formats to dynamic as TomH does above.

Here's a little mysql to generate those convert statements:

SELECT GROUP_CONCAT(CONCAT('ALTER TABLE `', table_name, '` ROW_FORMAT=DYNAMIC') SEPARATOR ';') AS aquery FROM information_schema.tables where table_schema = 'yourdbname';

That will spit out a long statement that you can execute from the mysql command line to convert all your tables.