search

ip2location / ip2location-piwik

IP2Location geolocation service to lookup a visitor's location in Matomo (Piwik) 4.x, 5.x. This service allows you to determine the country, region, city, coordinates, zip code, time zone, ISP, domain, MCC, MNC, mobile brand name, elevation, usage type, address type, IAB category, and more, associated with any given IP address.
http://www.ip2location.com
32 stars 18 forks source link

Value for location_country too long #32

Closed CountCount closed 5 years ago

CountCount commented 5 years ago

I have moved Matomo to a new server and ran into several issues, most of them could be resolved by searching the web. But now I am stuck with SQL errors due to the country:

[Thu Jun 06 11:01:07.902574 2019] [php7:notice] [pid 13791] [client] Error in Matomo (tracker): Error query: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'location_country' at row 1 In query

It tries to set longer name like "sjaelland" or "dnipropetrovska oblast" as country but the country column in the log_visit table is set to be three char long, only.

I am using the latest IP2Location plugin 3.1.14 with Matomo 3.9.1 and downloaded a fresh copy of the database binary today (DB3).

Two examples of full queries showing errors:

INSERT INTO log_visit (idvisitor, config_id, location_ip, idsite, 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_country, location_provider) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '\xeb\x92\xc4HF\xf8\xbbw', 1 => '\xdc\x7f\xb8\xdd\xb3\x87y\xe9', 2 => '*\x02\x16\xf0' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '', 3 => 39, 4 => '2019-06-06 09:01:07', 5 => 0, 6 => 0, 7 => '2019-06-06 09:01:07', 8 => 0, 9 => 0, 10 => 1, 11 => 8, 12 => 202990, 13 => 202991, 14 => 202990, 15 => 202991, 16 => 1, 17 => 1, 18 => 0, 19 => NULL, 20 => NULL, 21 => 1, 22 => 'https://example.com', 23 => 'de', 24 => 'Gecko', 25 => 'FF', 26 => '67.0', 27 => '', 28 => 'generic desktop', 29 => 0, 30 => 'WIN', 31 => '7', 32 => 0, 33 => '11:01:07', 34 => '0', 35 => '1920x1080', 36 => 1, 37 => 0, 38 => 1, 39 => 0, 40 => 0, 41 => 0, 42 => 0, 43 => 0, 44 => 0, 45 => 0, 46 => 0, 47 => 'sjaelland', 48 => 'regioit.de', )

INSERT INTO log_visit (idvisitor, config_id, location_ip, idsite, 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_country, location_provider) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '&\x7f\x98\xfc\x1b^\x1aK', 1 => '\xaf\x8a%\xdc\xf4\x06\x80\xfc', 2 => ' \x03' . "\\0" . '\xca\xef\x18' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '' . "\\0" . '', 3 => 1, 4 => '2019-06-06 09:23:10', 5 => 0, 6 => 0, 7 => '2019-06-06 09:23:10', 8 => 0, 9 => 0, 10 => 0, 11 => 1, 12 => 209015, 13 => 38827, 14 => 209015, 15 => 38827, 16 => 1, 17 => 1, 18 => 0, 19 => NULL, 20 => NULL, 21 => 1, 22 => 'https://example.com', 23 => 'de', 24 => 'Gecko', 25 => 'FF', 26 => '67.0', 27 => '', 28 => 'generic desktop', 29 => 0, 30 => 'WIN', 31 => '10', 32 => 0, 33 => '11:23:12', 34 => '0', 35 => '1600x900', 36 => 1, 37 => 0, 38 => 0, 39 => 0, 40 => 0, 41 => 0, 42 => 0, 43 => 0, 44 => 0, 45 => 0, 46 => 0, 47 => 'dnipropetrovska oblast', 48 => 't-ipconnect.de', )

Is there something wrong with my Matomo database or how the IP2Location DB is read? Which version of the free DB do you recommend?

ip2location commented 5 years ago

I'm not able to reproduce the issue from my side. Can you please remove the plugin, BIN database and upload a new copy to try again?