@msonnad opened this Issue on March 20th 2020

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)
image

We are using ,
PIWIK : 3.13.2
Mysql : 5.7.19

Thanks,
Manjunath S S

@sgiehl commented on March 20th 2020 Member

Which geolocation provider are you using in Matomo?

@msonnad commented on March 20th 2020

We are using,
image

It's a core plugin from Matomo

And
image
for Db

@msonnad commented on March 20th 2020

@sgiehl any solution for this issue ??

Thanks

@sgiehl commented on March 20th 2020 Member

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 commented on March 20th 2020

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

@sgiehl commented on March 20th 2020 Member

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

@msonnad commented on March 20th 2020

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 commented on March 20th 2020 Member

@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 commented on September 14th 2022

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 commented on September 14th 2022
@sgiehl commented on September 15th 2022 Member

@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 commented on September 15th 2022

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.

Powered by GitHub Issue Mirror