Error while updating to 4.0.0-b1 - "have to change some columns to TEXT or BLOBs"

Hi,

I did the automatic update right now and encountered this error. We have a relatively small installation.

Critical Error during the update process:
/home/members/artisticactivism/sites/c4aa.org/web/matomo_stats/core/Updates/4.0.0-b1.php: Error trying to execute the migration 'ALTER TABLE `matomo_log_visit` ADD COLUMN `visitor_seconds_since_first` INT(11) UNSIGNED NULL, ADD COLUMN `visitor_seconds_since_order` INT(11) UNSIGNED NULL, ADD COLUMN `visitor_seconds_since_last` INT(11) UNSIGNED NULL, ADD COLUMN `profilable` TINYINT(1) NULL;'. The error was: SQLSTATE[42000]: Syntax error or access violation: 1118 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

Wrapped but formatted version for easier reading

Critical Error during the update process:
/home/members/artisticactivism/sites/c4aa.org/web/matomo_stats/core/Updates/4.0.0-b1.php: Error trying to execute the migration ‘ALTER TABLE matomo_log_visit ADD COLUMN visitor_seconds_since_first INT(11) UNSIGNED NULL, ADD COLUMN visitor_seconds_since_order INT(11) UNSIGNED NULL, ADD COLUMN visitor_seconds_since_last INT(11) UNSIGNED NULL, ADD COLUMN profilable TINYINT(1) NULL;’. The error was: SQLSTATE[42000]: Syntax error or access violation: 1118 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

I’m familiar enough with mySQL to create databases, install, backup, and make a few changes but it’s not something I work with often. If you have suggestions, please speak clearly. Thanks! :slightly_smiling_face:

1 Like

I have the exact same problem (except table name is piwik_log_visit instead of matomo_log_visit, but I suspect my original installation is older than Steve).

Any help to fix this, would be appreciated.
Cheers.

1 Like

Hi,

Thanks for reporting, I created this issue:

A few questions to clarify:

  • WHat is your Version of MySQL,
  • which ROW_FORMAT is used in the log_visit table
  • do you use innodb_strict_mode?

Hello,
I am running MariaDB 10.3.27
Row_format : Compact
I could not find a way to know whether I am using innodb_strict_mode.

However, as recommended in MariaDB doc, I’ve converted the log_visit table to DYNAMIC and the update then worked.
ALTER TABLE log_visit ROW_FORMAT=DYNAMIC;

May be the ALTER TABLE should be added to the update process.
Thanks for pointing to the correct direction.

,

I suppose this problem comes from either having many custom variables or custom dimensions. Could someone maybe confirm this?

In case of many custom dimensions converting some to TEXT might help (you can convert as many as needed):

ALTER TABLE matomo_log_visit MODIFY COLUMN custom_dimension_1 TEXT, MODIFY COLUMN custom_dimension_2 TEXT,MODIFY COLUMN custom_dimension_3 TEXT;

or custom variables (same here can convert as many as needed):

ALTER TABLE matomo_log_visit MODIFY COLUMN custom_var_k1 TEXT, MODIFY COLUMN custom_var_v1 TEXT,MODIFY COLUMN custom_var_v2 TEXT;

Otherwise https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ link will help like changing the row format to Dynamic. We can’t do this automatically unfortunately because for some users this could cause issues.

Thanks for the response. Here’s what I found digging around with phpMyAdmin:

  • Server type: MariaDB
  • Server version: 10.3.25-MariaDB-0+deb10u1 - Debian 10

COMPACT

Looks like it’s on.

Thanks again and looking forward.

Would you be able to set (backup the table first) the ROW_FORMAT to DYNAMIC? It offers basically the same features as COMPACT but is a bit more flexible.

ALTER TABLE `matomo_log_visit`  ROW_FORMAT=DYNAMIC;

Are you using a lot of custom dimensions / variables in Matomo?

1 Like

That worked - thanks!

No, it’s pretty close to defaults IIRC.

1 Like