Database error when saving CiviCRM event: “Row size too large”

Question

I'm using CiviCRM in multilingual mode. I can generally create events (and other objects) fine. However, I often run into update problems at some point. The interface will "spin" and never save.

Looking at the CiviCRM error log reveals a database error:

nativecode=1118 ** Row size too large (> 8126)

I have looked at this clearly related case: Database error when using CiviCRM admin UI: "Row size too large"

I have tried (with some trepidation because I haven't worked on the Civi backend yet and don't know where the pitfalls are) to change the ROW_FORMAT of Civicrm_event_en_CA to Dynamic, but am told that it is not a base table.

I'm not really clear as I am more of a db user than a programmer, but assume that this table is somehow subsidiary to civicrm_event.

That table is set to ROW_TYPE=COMPACT. I'm game to change it, but it seems drastic enough that I am wondering:

1. Is it safe to do so WRT to CiviCRM operation?
2. Will it persist through a CiviCRM upgrade or will it revert (and potentially throw away data)?

Answer 1

this might be a mariadb config issue.

# enable large indexes and Barracuda
# for utf8mb4
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_large_prefix=1

might also change the tables to ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED see this post for much more info on this front https://stackoverflow.com/questions/15585602/change-limit-for-mysql-row-size-too-large

but do not follow the suggestion there to alter the tables to use MyISAM instead of INNODB, that's a really bad idea.

Answer 2

Adding this for ease of reference... In addition to the above innodb config, you will need to check the tables as @Keith Nunn alluded to. There are two tables that may need tweaking, SQL code below (row format is the key, but no harm to make sure block size and engine are set):-

ALTER TABLE civicrm_contribution_page
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;

ALTER TABLE civicrm_event
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;