Fix problem with too long browser version

[dr0i]

This seems related to e.g. #15715.

[Edit]: using Matomo 4.8.0.

My workaround: modify the default restrictions of VARCHAR(20) like this:

mysql> ALTER TABLE matomo_log_visit MODIFY COLUMN config_browser_version VARCHAR(100);

Would be cool if such errors would not result in canceling the loading of the log files when doing ./console core:archive.
I wished that these these malformed entries would just be skipped and thus the indexing would go on. I really don't care of those bad log entries anyway.

@sgiehl wrote:

To avoid that error you can change the sql mode on your server and remove STRICT_TRANS_TABLES

Haven't tried that yet. If that's a solution for all those Mysqli statement execution errors, fine. Should be the default IMO.

Here is the web server's error:

Error in Matomo (tracker): Error query: Mysqli statement execute error : Data too long for column 'config_browser_version' at row 1 In query: INSERT INTO matomo_log_visit (idvisitor, config_id, location_ip, idsite, profilable, visit_first_action_time, visit_goal_buyer, visit_goal_converted, visit_last_action_time, visitor_returning, visitor_seconds_since_first, visitor_seconds_since_order, visitor_count_visits, visit_entry_idaction_name, visit_entry_idaction_url, visit_exit_idaction_name, visit_exit_idaction_url, visit_total_actions, visit_total_interactions, visit_total_searches, referer_name, referer_type, referer_url, location_browser_lang, config_browser_engine, config_browser_name, config_browser_version, config_client_type, config_device_brand, config_device_model, config_device_type, config_os, config_os_version, visit_total_events, visitor_localtime, visitor_seconds_since_last, config_resolution, config_cookie, config_flash, config_java, config_pdf, config_quicktime, config_realplayer, config_silverlight, config_windowsmedia, visit_total_time, location_country, location_latitude, location_longitude, referer_keyword) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '\x18\xca\x93\x15]z\xac\xc7', 1 => '\xc2\x94\xefn\xcb\x02\x94F', 2 => '\xd4f' . "\0" . '' . "\0" . '', 3 => 5, 4 => 0, 5 => '2022-05-04 16:15:35', 6 => 0, 7 => 0, 8 => '2022-05-04 16:15:35', 9 => 0, 10 => 0, 11 => NULL, 12 => 1, 13 => 0, 14 => 36786631, 15 => 0, 16 => 36786631, 17 => 1, 18 => 1, 19 => 0, 20 => 'Google', 21 => 2, 22 => 'https://www.google.com/', 23 => '', 24 => 'Gecko', 25 => 'FF', 26 => '60.989879960022535902747826837404505249', 27 => 1, 28 => '', 29 => 'generic desktop', 30 => 0, 31 => 'WIN', 32 => '10', 33 => 0, 34 => '16:15:35', 35 => 0, 36 => 'unknown', 37 => 0, 38 => 0, 39 => 0, 40 => 0, 41 => 0, 42 => 0, 43 => 0, 44 => 0, 45 => 0, 46 => 'it', 47 => '43.148', 48 => '12.11', 49 => NULL, )

[bx80]

Hi @dr0i, thanks for reporting this.

I'm guessing that 60.989879960022535902747826837404505249 is not an official FireFox version number, but either way a long user input string shouldn't be able to cause a statement execution error.

STRICT_TRANS_TABLES mode would work around the issue but it could potentially cause problems for other applications that use the same database server. We could consider truncating tracker string values in the application code as another long term solution.

[sgiehl]

That should actually be fixed quite easily. The database column currently has a length of 20. So we could simply limit the version to 20 characters and throw away everything afterwards. So a simple substr($x, 0, 20) should fix this here:

matomo/plugins/DevicesDetection/Columns/BrowserVersion.php

Line 40 in 1155273

return $aBrowserInfo['version'];

@justinvelluppillai guess we could do that for 4.12. As it's really low effort.

[justinvelluppillai]

@justinvelluppillai guess we could do that for 4.12. As it's really low effort.

Happy for you to include that if it's a 10 minute fix or so 👍🏽