@Findus23 opened this Issue on November 26th 2020 Member

reported by two people in https://forum.matomo.org/t/error-while-updating-to-4-0-0-b1-have-to-change-some-columns-to-text-or-blobs/39482

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

@sgiehl commented on November 26th 2020 Member

Guess that might only happen in innodb strict mode and depends on configured row format and some other settings.
There's a quite good troubleshooting here: https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

@tsteur commented on November 26th 2020 Member

I suppose this problem comes from either having many custom variables or custom dimensions.

In case of many custom dimensions converting some to TEXT might help:

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:

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

Otherwise as @sgiehl mentioned the https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ link will help.

We can't really do anything here otherwise unfortunately. Can we close this issue?

@C0rn3j commented on November 27th 2020
There was a problem installing the plugin CustomDimensions:

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

Curiously, I have had this issue when upgrading 3.14.1 to 4.0.1 BEFORE the utf8mb4 conversion, and it disappeared right after.

Using mariadb 10.5.8.

@Findus23 commented on November 27th 2020 Member
@tsteur commented on November 29th 2020 Member

So far what might help fix this issue is :

Option 1 (confirmed it works)

ALTER TABLE log_visit ROW_FORMAT=DYNAMIC;

Option 2 (when many custom dimensions or custom variables)

In case you have many custom dimensions converting some of them 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 if you have many 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;

Option 3 (Experimental)

Alternatively it might help to disable innodb strict mode like. Not 100% sure this helps though or is a good idea. And likely the setting should be turned on again after the migration.

SET GLOBAL innodb_strict_mode = 0;
@tsteur commented on November 29th 2020 Member

Summing roughly our default columns when marketing campaign reporting is installed I end up roughly 28+8+16+200+3+11+11+30+11+8+5+255+255+1+4000+20+10+40+20+100+100+100+3+11+100+18+10+11+255+3+2000+(255*10)+(255*6)+44= 11K characters so I suppose this roughly translates to 11K row page size when innodb maybe allows slightly less than 8K.

The easiest fix be to lower referer_url to 1000 which should be still plenty of characters for the referrer url

@tsteur commented on November 29th 2020 Member

select sum(character_maximum_length) from COLUMNS where table_name like '%log_visit' and table_schema like '%api4_3%' returns in my case 12969 . This is with 15 custom dimensions though. With only 5 custom dimensions in scope visit it is only 10K.

@tsteur commented on November 29th 2020 Member

To close this issue we need to create an FAQ suggesting the first 2 options as a workaround.

@tsteur commented on November 29th 2020 Member
@S-n-d commented on December 1st 2020

I did all the things stated in the FAQ, so option 1 and option 2. Still get the same error. Any thoughts? I have 0 custom dimensions and my custom_var rows are all empty.

@sgiehl commented on December 1st 2020 Member

@S-n-d would you mind sharing the table information of your log_visit table? (you can simply use a query like DESCRIBE log_visit;)

@S-n-d commented on December 1st 2020

@sgiehl thanks for you reply. I've restored a file and DB backup (from yesterday) because I couldn't get into the dashboard anymore. Seems I've gotten myself in a heap of other issues now. I'll get back to this when I get my backup up and running.

This Issue was closed on November 29th 2020
Powered by GitHub Issue Mirror