Mysqli statement execute error : Data too long for column 'location_region'

[msonnad]

We are getting this error very often.

ERROR Piwik\Tracker\Handler[2020-03-20 04:31:34 UTC] [8eafc] )

ERROR Piwik\Tracker\Handler[2020-03-20 04:31:34 UTC] [8eafc] Error query: Mysqli statement execute error : Data too long for column 'location_region' at row 1

ERROR Piwik\Tracker\Handler[2020-03-20 04:31:34 UTC] [8eafc] In query: INSERT INTO piwik_log_visit (idvisitor, config_id, location_ip, idsite, user_id, visit_first_action_time, visit_goal_buyer, visit_goal_converted, visit_last_action_time, visitor_days_since_first, visitor_days_since_order, visitor_returning, 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_device_brand, config_device_model, config_device_type, config_os, config_os_version, visit_total_events, visitor_localtime, visitor_days_since_last, config_resolution, config_cookie, config_director, config_flash, config_gears, config_java, config_pdf, config_quicktime, config_realplayer, config_silverlight, config_windowsmedia, visit_total_time, location_city, location_country, location_latitude, location_longitude, location_region) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

ERROR Piwik\Tracker\Handler[2020-03-20 04:31:34 UTC] [8eafc] Parameters: array (

No Data s tracking for location_region column. And the plugin UserCounty and UserCountryMap both are in an active state.

how to resolve this. This error is filling our logs file memory. Currently, the column length is char(3)

We are using , PIWIK : 3.13.2 Mysql : 5.7.19

Thanks, Manjunath S S

[sgiehl]

Which geolocation provider are you using in Matomo?

[msonnad]

We are using,

It's a core plugin from Matomo

And for Db

[msonnad]

@sgiehl any solution for this issue ??

Thanks

[sgiehl]

It should actually not happen at all. Are you using dbip or maxmind database? and which one? And wher are your visitor many located? Maybe the database returns an invalid value 🤔

[msonnad]

We are using dbip database. Visitors are from USA , EMEA and Asia region manily. Then how we can fix this ??

[sgiehl]

Paid or free database? Can you see the values of those queries that have too long values?

[msonnad]

Actually, we are using GeoLite2-City from MaxMind, Its a free version we are using Its insert query to piwik_log_visit for column location_region. There is no data is tracking for this column.

Thanks,

[sgiehl]

@msonnad if the logging has no value for the region, it actually can't be too long. But maybe it's not printed in the log as some other value contains binary data causing the log to abort. To avoid that error you can change the sql mode on your server and remove STRICT_TRANS_TABLES I think. That way the values should be truncated instead of throwing an error and not inserting them at all.

[agrzchr15]

We use a Intranet Location Geo plugin. Our Regions names follow our Intranet Locations. We altered to: location_region | char(20) Now all regions fit with our intranet names.

[grzchr15]

see https://github.com/grzchr15/piwik-geoip2-andintra

[sgiehl]

@agrzchr15 The location_region column is meant for storing iso region codes. Storing something else is not officially supported and might break again with future updates

[grzchr15]

Thanks for response Please suggest a solution where MAXMIND region_names and INTRANET region names could chain in reports

The Idealflow:

Have a GEO plugin which search an IP in a INTRANET IPAM database and populates the log_visit fields const CONTINENT_CODE_KEY = 'continent_code'; const CONTINENT_NAME_KEY = 'continent_name'; const COUNTRY_CODE_KEY = 'country_code'; const COUNTRY_NAME_KEY = 'country_name'; const REGION_CODE_KEY = 'region_code'; -> either MAXMIND region codes or own region codes const REGION_NAME_KEY = 'region_name'; -> either MAXMIND region names or own region names const CITY_NAME_KEY = 'city_name'; const AREA_CODE_KEY = 'area_code'; -> either MAXMIND region names or own area codes const LATITUDE_KEY = 'lat'; const LONGITUDE_KEY = 'long'; const POSTAL_CODE_KEY = 'postal_code'; const ISP_KEY = 'isp'; const ORG_KEY = 'org';

If the IP is not found in INTRANET IPAM chain to the "offical" MATOMO GEOIP plugin, and populate again this values.

Currently we can select one single GEOIP plugin ( webserver, php, ) but no Chaining hook is available. "offical" MATOMO GEOIP plugin does not support any connection to INTRANET IPAMs.

All reports which use geo information can so work only with maxmind. Storing it not in the same values would need to duplicate all reports if collected elsewhere.

Having just a bit more field size seems still the least impact to make matomo more useful and not to break lots of other things.

If there is any chaining possible in matomo geo plugin, this would allow to select a GEO intranet plugin and the chain to the normal MATOMO GEO plugin ( and no patching every release) .

So to topics: 1.) Chaining 2.) SQL field sizes versus complete new sets of reports, and tables 3.) Missing opportunity to get a report how many users from internet/intranet for a website.

[heurteph-ei]

Isn't it duplicate of #19323? Then can be closed, as #19323 has been solved

[grzchr15]

No, at #19323 it just reacts on some regions from maxmind which are sometimes to long.

This request tries to find a way to have 2 locations providers chained. Websites are accessed by internet and intranet locations And intranet location dbs from ipam use same fields at maxmind but longer names like codes for office locations. So alter table can extend field sizes but this does not solve chaining of loc providers (see above)

[grzchr15]

currently chaining means patching geo location provider and add ipam based location provider before. In case of not found in intra ipam it continues to maxmind based.

[grzchr15]

if chaining option would be existing it would be a stable second geo plugin.

[sgiehl]

Closing this one, as we have meanwhile adjusted our code, so the initially reported error shouldn't happen anymore as long as official plugins are used.

For more flexibility in geo location I've created https://github.com/matomo-org/matomo/issues/22270, so this isn't mixed up in this issue.