[SOLVED] Converting XWiki table in MariaDB, from latin1 to utf8, got SQL Error [1118] [42000]: Row size too large

Converting XWiki table in MariaDB, from latin1 to utf8, got SQL Error [1118] [42000]: Row size too large

We are trying to upgrade an XWiki instance from v7.0.1 to v13.10.9 and need to convert the legacy production data.

We are testing with MariaDB v10.3 and v10.9. And to change the character set, we used SQL commands like:

alter table `xwiki`.`activitystream_events`
convert to character set utf8mb4 collate 'utf8mb4_unicode_ci';

However, the above command got an error saying:

SQL Error [1118] [42000]: (conn=14) Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Similar errors exist on other tables as well.

My Questions:

How do we resolve the issue? E.g. setting changes, etc.

What is the common practice for this type of database conversion? For example, would the new version of the Java project have an automatic after-upgrade migration to take care of it?

I will highly appreciate any hints and suggestions.

The DDL of the table that the error concerns:

-- xwiki.activitystream_events definition

CREATE TABLE `activitystream_events` (
  `ase_eventid` varchar(48) NOT NULL,
  `ase_requestid` varchar(48) DEFAULT NULL,
  `ase_stream` varchar(255) DEFAULT NULL,
  `ase_date` datetime DEFAULT NULL,
  `ase_priority` int(11) DEFAULT NULL,
  `ase_type` varchar(255) DEFAULT NULL,
  `ase_application` varchar(255) DEFAULT NULL,
  `ase_user` varchar(255) DEFAULT NULL,
  `ase_wiki` varchar(255) DEFAULT NULL,
  `ase_space` varchar(255) DEFAULT NULL,
  `ase_page` varchar(255) DEFAULT NULL,
  `ase_hidden` bit(1) DEFAULT NULL,
  `ase_url` varchar(2000) DEFAULT NULL,
  `ase_title` varchar(2000) DEFAULT NULL,
  `ase_body` varchar(2000) DEFAULT NULL,
  `ase_version` varchar(30) DEFAULT NULL,
  `ase_param1` varchar(2000) DEFAULT NULL,
  `ase_param2` varchar(2000) DEFAULT NULL,
  `ase_param3` varchar(2000) DEFAULT NULL,
  `ase_param4` varchar(2000) DEFAULT NULL,
  `ase_param5` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`ase_eventid`),
  KEY `EVENT_TYPE` (`ase_type`),
  KEY `EVENT_PRIORITY` (`ase_priority`),
  KEY `EVENT_WIKI` (`ase_wiki`),
  KEY `EVENT_DATE` (`ase_date`),
  KEY `EVENT_PAGE` (`ase_page`),
  KEY `EVENT_USER` (`ase_user`),
  KEY `EVENT_SPACE` (`ase_space`),
  KEY `EVENT_STREAM` (`ase_stream`),
  KEY `EVENT_APP` (`ase_application`),
  KEY `EVENT_HIDDEN` (`ase_hidden`),
  KEY `EVENT_REQUESTID` (`ase_requestid`),
  KEY `ase_page_date` (`ase_page`,`ase_date`),
  KEY `ase_param1` (`ase_param1`(200)),
  KEY `ase_param2` (`ase_param2`(200)),
  KEY `ase_param3` (`ase_param3`(200)),
  KEY `ase_param4` (`ase_param4`(200)),
  KEY `ase_param5` (`ase_param5`(200))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Solution

In the past, we have consistently and successfully used this script (executed from an XWiki page saved with programming rights) for converting numerous XWiki instances to real UTF8 (utf8mb4) after an upgrade, as far as I remember on both MySQL and MariaDB variants:

https://snippets.xwiki.org/xwiki/bin/view/Extension/Migrate%20MySQL%20databases%20to%20utf8mb4/

The upgrades were generally a bit more incremental than yours (which jumps from 7 to 13), but you should still give it a go.