matomo icon indicating copy to clipboard operation
matomo copied to clipboard
verified publisher icon matomo-org

[Bug] AIAgents: ai_agent_name column not created on wide log_visit tables, core:update hides InnoDB row size error (1118)

Open futureweb opened this issue 4 months ago β€’ 9 comments

What happened?

After updating to Matomo 5.6.1 and enabling the AIAgents plugin, the database migration that should add the ai_agent_name column to matomo_log_visit (formerly piwik_log_visit) reports success in the CLI, but the column is not actually created.

When running:

php /var/www/matomo/console core:update

the output shows something like:

Executing ALTER TABLE `matomo_log_visit` ADD COLUMN `ai_agent_name` VARCHAR(40) NULL;... Done. [1 / 1]

Matomo has been successfully updated!

However, when checking the table structure afterwards, the ai_agent_name column is missing.

Running the same statement manually:

ALTER TABLE `matomo_log_visit` ADD COLUMN `ai_agent_name` VARCHAR(40) NULL;

fails with:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126.

So the underlying ALTER TABLE fails due to the InnoDB row size limit, but core:update still reports success and hides the error, leaving the schema in an incomplete state while the AIAgents plugin assumes the column exists.

What should happen?

If the ALTER TABLE fails (e.g. with ERROR 1118: row size too large), core:update should:

  • not print Done and
  • not report Matomo has been successfully updated!.

Instead, the database error should be clearly shown in the CLI output and the update should be marked as failed or at least partially failed. This would avoid having a silently incomplete schema.

Additionally, for migrations on very wide tables like matomo_log_visit, Matomo could:

  • detect or catch InnoDB row size issues, and
  • show a helpful message (for example suggesting ROW_FORMAT=DYNAMIC or restructuring very wide rows) instead of hiding the error.

Right now, the AIAgents plugin ends up in a broken state on large installations: the column is missing, the plugin assumes it is present, and we have to manually debug the problem and perform a heavy operation on a ~30 GB table to fix it.

How can this be reproduced?

This is not reproducible on the public demo because it requires a very large and wide matomo_log_visit table. The issue occurs on a self-hosted installation with:

  • Matomo 5.6.1
  • AIAgents plugin enabled
  • MySQL/MariaDB with InnoDB
  • matomo_log_visit being a very wide table (many columns, including custom dimensions) and ~30 GB in size.

Steps:

  1. Use a Matomo instance with a very wide InnoDB matomo_log_visit table that is close to the InnoDB row size limit (e.g. many VARCHAR columns, large table ~30 GB).

  2. Enable the AIAgents plugin:

    php /var/www/matomo/console core:plugin:activate AIAgents

  3. Run the update:

    php /var/www/matomo/console core:update

  4. Observe that the CLI reports:

    • Executing ALTER TABLE ... ADD COLUMN ai_agent_name ... Done.
    • Matomo has been successfully updated!

  5. Check the schema of matomo_log_visit – the ai_agent_name column is missing.

  6. Execute the migration SQL manually:

    ALTER TABLE `matomo_log_visit` ADD COLUMN `ai_agent_name` VARCHAR(40) NULL;

  7. You will see:

    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126.

This shows that the migration failed at the DB level, but core:update did not surface the error and still reported a successful update.

Matomo version

5.6.1

PHP version

8.3

Server operating system

AlmaLinux

What browsers are you seeing the problem on?

No response

Computer operating system

No response

Relevant log output

Validations

futureweb avatar Dec 03 '25 15:12 futureweb

@futureweb Thanks for creating the report. Might it be possible that you have created a lot of custom visit dimensions? Otherwise the row size of this table shouldn't be that big 🧐

sgiehl avatar Dec 03 '25 16:12 sgiehl

@sgiehl - not that much for tracking 2.301 Websites with this instance! Γ°ΒŸΒ˜Β…

CREATE TABLE `piwik_log_visit` (
  `idvisit` bigint(10) UNSIGNED NOT NULL,
  `idsite` int(10) UNSIGNED NOT NULL,
  `idvisitor` binary(8) NOT NULL,
  `visitor_localtime` time DEFAULT NULL,
  `visitor_returning` tinyint(1) DEFAULT NULL,
  `visitor_count_visits` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `visit_first_action_time` datetime NOT NULL,
  `visit_last_action_time` datetime NOT NULL,
  `visit_exit_idaction_url` int(10) UNSIGNED DEFAULT 0,
  `visit_exit_idaction_name` int(10) UNSIGNED DEFAULT NULL,
  `visit_entry_idaction_url` int(11) UNSIGNED DEFAULT NULL,
  `visit_entry_idaction_name` int(10) UNSIGNED DEFAULT NULL,
  `visit_total_actions` int(11) UNSIGNED DEFAULT NULL,
  `visit_total_searches` smallint(5) UNSIGNED DEFAULT NULL,
  `visit_total_events` int(11) UNSIGNED DEFAULT NULL,
  `visit_total_time` int(11) UNSIGNED NOT NULL,
  `visit_goal_converted` tinyint(1) DEFAULT NULL,
  `visit_goal_buyer` tinyint(1) DEFAULT NULL,
  `referer_type` tinyint(1) UNSIGNED DEFAULT NULL,
  `referer_name` varchar(255) DEFAULT NULL,
  `referer_url` varchar(1500) DEFAULT NULL,
  `referer_keyword` varchar(255) DEFAULT NULL,
  `config_id` binary(8) NOT NULL,
  `config_os` char(3) DEFAULT NULL,
  `config_os_version` varchar(100) DEFAULT NULL,
  `config_browser_name` varchar(40) DEFAULT NULL,
  `config_browser_version` varchar(20) DEFAULT NULL,
  `config_device_type` tinyint(100) DEFAULT NULL,
  `config_device_brand` varchar(100) DEFAULT NULL,
  `config_device_model` varchar(100) DEFAULT NULL,
  `config_resolution` varchar(18) DEFAULT NULL,
  `config_pdf` tinyint(1) DEFAULT NULL,
  `config_flash` tinyint(1) DEFAULT NULL,
  `config_java` tinyint(1) DEFAULT NULL,
  `config_quicktime` tinyint(1) DEFAULT NULL,
  `config_realplayer` tinyint(1) DEFAULT NULL,
  `config_windowsmedia` tinyint(1) DEFAULT NULL,
  `config_silverlight` tinyint(1) DEFAULT NULL,
  `config_cookie` tinyint(1) DEFAULT NULL,
  `location_ip` varbinary(16) NOT NULL,
  `location_browser_lang` varchar(20) DEFAULT NULL,
  `location_country` char(3) DEFAULT NULL,
  `location_region` char(3) DEFAULT NULL,
  `location_city` varchar(255) DEFAULT NULL,
  `location_latitude` decimal(9,6) DEFAULT NULL,
  `location_longitude` decimal(9,6) DEFAULT NULL,
  `custom_var_k1` varchar(200) DEFAULT NULL,
  `custom_var_v1` varchar(200) DEFAULT NULL,
  `custom_var_k2` varchar(200) DEFAULT NULL,
  `custom_var_v2` varchar(200) DEFAULT NULL,
  `custom_var_k3` varchar(200) DEFAULT NULL,
  `custom_var_v3` varchar(200) DEFAULT NULL,
  `custom_var_k4` varchar(200) DEFAULT NULL,
  `custom_var_v4` varchar(200) DEFAULT NULL,
  `custom_var_k5` varchar(200) DEFAULT NULL,
  `custom_var_v5` varchar(200) DEFAULT NULL,
  `location_provider` varchar(200) DEFAULT NULL,
  `user_id` varchar(200) DEFAULT NULL,
  `config_browser_engine` varchar(10) DEFAULT NULL,
  `visit_total_interactions` mediumint(8) UNSIGNED DEFAULT 0,
  `visitor_seconds_since_first` int(11) UNSIGNED DEFAULT NULL,
  `visitor_seconds_since_order` int(11) UNSIGNED DEFAULT NULL,
  `visitor_seconds_since_last` int(11) UNSIGNED DEFAULT NULL,
  `profilable` tinyint(1) DEFAULT NULL,
  `last_idlink_va` bigint(20) UNSIGNED DEFAULT NULL,
  `custom_dimension_1` varchar(255) DEFAULT NULL,
  `custom_dimension_2` varchar(255) DEFAULT NULL,
  `custom_dimension_3` varchar(255) DEFAULT NULL,
  `custom_dimension_4` varchar(255) DEFAULT NULL,
  `custom_dimension_5` varchar(255) DEFAULT NULL,
  `config_client_type` tinyint(1) DEFAULT NULL,
  `ai_agent_name` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

futureweb avatar Dec 03 '25 17:12 futureweb

@futureweb are you actively using custom variables and custom dimensions? custom variables is also adding a lot to the total row size, so if you don't use it, it might be beneficial to remove the plugin (and the database columns).

sgiehl avatar Dec 05 '25 09:12 sgiehl

I would think this is related to the same problem, when i try to update to Matomo 5.6.1 i get this error from the web update.

/home/user/www/matomo/core/Columns/Updater.php: Error trying to execute the migration 'ALTER TABLEpiwik_log_visitADD COLUMNai_agent_nameVARCHAR(40) 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

TimerWolf avatar Dec 05 '25 10:12 TimerWolf

@sgiehl – since we manage 2,301 Websites/Users through this setup, I unfortunately can’t say whether those features are actively used by any of our customers.

It’s not a big issue on our side though – since I was able to resolve the problem by switching the table to ROW_FORMAT=DYNAMIC.

I mainly opened this issue so that similar cases can be avoided for other users in the future. πŸ™‚

futureweb avatar Dec 09 '25 14:12 futureweb

@futureweb I guess that instance is already a bit older, right? New tables should meanwhile actually already be created with ROW_FORMAT=DYNAMIC πŸ€”

sgiehl avatar Dec 09 '25 14:12 sgiehl

@sgiehl - so old the Tables still named piwik_* ... πŸ˜…

futureweb avatar Dec 09 '25 15:12 futureweb

@futureweb I guess that instance is already a bit older, right? New tables should meanwhile actually already be created with ROW_FORMAT=DYNAMIC πŸ€”

Maybe that should be a check somewhere, when the database is that old and make this change automatic as i am is the same position and also changed my table to "dynamic" yesterday and then it just worked fine but this should already been done and check by the "auto update" at some point is my opinion.

And my tables also still named "piwik_*"

Maybe its been missed in the check somewhere ?

TimerWolf avatar Dec 09 '25 15:12 TimerWolf

Thanks for the feedback @futureweb and @TimerWolf. We will look into finding an appropriate solution for this.

sgiehl avatar Dec 09 '25 15:12 sgiehl