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

[futureweb]

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

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.

[sgiehl]

@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 🧐

[futureweb]

@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;

[sgiehl]

@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).

[TimerWolf]

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