Tracking Error "Data too long for column 'config_device_model'"

[Thomas-TeS]

Context

I'm automatically and periodically importing logs into Matomo. Since a few days now, a problem occurred during the log-import. The server hosting Matomo always sent a "HTTP Error 500" after a certain number of lines got imported.

I've now tried to import them manually with the same behaviour as above and the following hint:

Max number of attempts reached, server is unreachable!
Fatal error: HTTP Error 500 Internal Server Error, response: {"status":"error","tracked":48,"invalid":0,"invalid_indices":[]}
You can restart the import of "/var/log/mocklog" from the point it failed by specifying --skip=6410 on the command line.

Using the line 6410 and the error-logs from the Matomo server I've narrowed it down to the following error:

Got error 'PHP message: [matomo.mock.de] Error in Matomo (tracker): Error query: Error query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'config_device_model' 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_keyword, 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_coo...'

The log contains the following value for 'config_device_model' which seems to be too long for the import:

Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.5672.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)

Expected Behavior

No error on log import? I don't know what to write here...

Current Behavior

Log-import fails and is completely aborted after failing.

Possible Solution

I think truncating the 'config_device_model' or increasing the size of the accoring column in the db are two feasable solutions.

Steps to Reproduce (for Bugs)

Create a log-file with the 'config_device_model' value from above and try to import it.

Your Environment

• Matomo Version: 4.15.0
• PHP Version: 8.1.14
• Server Operating System: Debian 10.13
• Additionally installed plugins: API, Actions, Annotations, BotTracker 2.08, BulkTracking, Contents, CoreAdminHome, CoreConsole, CoreHome, CorePluginsAdmin, CoreUpdater, CoreVisualizations, CoreVue, CustomDimensions, CustomJsTracker, CustomVariables 4.1.3, Dashboard, DevicePlugins, DevicesDetection, Diagnostics, Ecommerce, Events, Feedback, GeoIp2, Goals, Heartbeat, HeatmapSessionRecording 4.5.10, ImageGraph, Insights, Installation, Intl, IntranetMeasurable, LanguagesManager, Live, LogViewer 4.1.1, Login, MarketingCampaignsReporting 4.1.3, Marketplace, MobileMessaging, Monolog, Morpheus, MultiSites, Overlay, PagePerformance, PrivacyManager, ProfessionalServices, Provider 4.0.5, Proxy, QueuedTracking 4.0.7, Referrers, Resolution, RssWidget, SEO, ScheduledReports, SearchEngineKeywordsPerformance 4.5.4, SecurityInfo 4.0.2, SegmentEditor, SitesManager, TagManager, Tour, Transitions, TwoFactorAuth, UserCountry, UserCountryMap, UserId, UserLanguage, UsersManager, VisitFrequency, VisitTime, VisitorInterest, VisitsSummary, WebsiteMeasurable, Widgetize

[sgiehl]

Hi @Thomas-TeS. It looks something is wrong with the import in your case. The field config_device_model should contain the device models name, like Galaxy 7 or iPhone 5, which is detected by using the useragent with device detector. The useragent isn't stored within Matomo.
The one you posted should actually be detected as GoogleBot and be fully discarded, unless you activated tracking bots on purpose.
Did your log file maybe changed to format, or did you change the way you import stuff?

[Thomas-TeS]

@sgiehl Ah ok, then I've confused the field from your side with the useragent from the log. I took the value

Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.0.5672.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)

which I pasted above directly from the source logs and thought it would just be parsed into the field.

As log-format we're using apache combined. To my knowledge nothing has recently changed in the way we import or format the logs. And the first 6410 lines don't seem to create any problems which puzzles me... If bot-detection is on by default, we also have this on. For testing purposes I created a new website inside Matomo with only the default settings.

One additional thing I've noticed is that the line from the ERROR changes to different values, e.g.:

You can restart the import of "/var/log/mock.log" from the point it failed by specifying --skip=6348 on the command line.

This seems to be connected to the param --skip which I tried with different values (e.g. 1000,2000,6410).

[Thomas-TeS]

Ok, I've found the correct line which fails the import. My assumption that I could just use the skip-param given by the error was incorrect.

The log-line which fails the import contains the following user agent which is likely to contain a device name larger than 100 chars (I don't know how you split the name specificially):

"Mozilla/5.0 (Linux; Android 10; HTC Desire 21 pro 5G Build/QKQ1.200927.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/113.0.5672.132 Mobile Safari/537.36 Instagram 284.0.0.22.85 Android (29/10; 446dpi; 1080x2172; HTC; HTC Desire 21 pro 5G; htc_thudugl; qcom; de_DE; 477443709)"

This was line 6528 btw.

[sgiehl]

@Thomas-TeS I've just checked that specific useragent with the device detector version that is used with Matomo 4.15.
Seems like the detection didn't work properly there, as it indeed returns Desire 21 pro 5G Build/QKQ1.200927.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/113.0.5672.132 Mobile Safari/537.36 Instagram 284.0.0.22.85 Android (29/10; 446dpi; 1080x2172; HTC; HTC Desire 21 Pro 5G as model.
Anyway. The newest version of device detector correctly returns Desire 21 Pro 5G. So this specific problem will be solved with Matomo 5.
But it would indeed make sense to trim the model name to the length the database is able to store.

[Thomas-TeS]

@sgiehl thanks for the update. For a quick fix we decided to trim the string and that worked too. Nonetheless, good to hear that this'll be fixed with the new major version :)