MaxMind lookups do not get updated


Description

After running Analytics for some time with an invalid MaxMind token, some IP lookups may fail with messages such as INVALID_LICENSE_KEY, LICENSE_EXPIRED or OUT_OF_QUERIES.

These messages may still appear in Analytics reports without getting updated with the correct information, even after updating the MaxMind settings with a valid token and manually running the Refresh Lookups wizard from the Control Panel using the Update blank entries mode.

Solution

To solve the problem, use one of the two possible solutions:

  1. Run the Refresh Lookups dialog from Control Panel -> Analytics menu and select Update all entries mode.
    Note that this approach may cause excessive calls to the MaxMind service if the Analytics database already contains a number of correctly resolved IP addresses.
  2. Execute the following query on your Analytics database, and after that run the Refresh Lookups dialog from Control Panel -> Analytics menu and select Update blank entries mode.
    DECLARE @empty_location UNIQUEIDENTIFIER; SET @empty_location = (SELECT TOP 1 [LocationId] FROM [Locations] WHERE [BusinessName] = '' AND [Country] = '');
    UPDATE [GeoIps] SET [CachedValue] = '', [Fetched] = 0, [Status] = 0 WHERE [CachedValue] LIKE '%<businessname>INVALID_LICENSE_KEY</businessname>%' OR [CachedValue] LIKE '%<businessname>LICENSE_EXPIRED</businessname>%' OR [CachedValue] LIKE '%<businessname>OUT_OF_QUERIES</businessname>%' GO
    UPDATE [Visits] SET  [LocationId] = @empty_location, [BusinessName] = '', [City] = '' WHERE [BusinessName] = 'INVALID_LICENSE_KEY' OR [BusinessName] = 'LICENSE_EXPIRED' OR [BusinessName] = 'OUT_OF_QUERIES' GO