MaxMinds GeoIP for local MySQL

[palinka]

I found a few bugs last night, still working on getting rid of the warnings but they do not seem to interfere...

Did you find the source of the warnings?

How to make mysqlimport more verbose? I'm guessing it has something to do with empty fields?

16777216,16777471,2077456,2077456,,0,0 = 16777216,16777471,2077456,2077456,!!!EMPTY_FIELD!!!,0,0

[SorenR]

I found a few bugs last night, still working on getting rid of the warnings but they do not seem to interfere...

Did you find the source of the warnings?

How to make mysqlimport more verbose? I'm guessing it has something to do with empty fields?

16777216,16777471,2077456,2077456,,0,0 = 16777216,16777471,2077456,2077456,!!!EMPTY_FIELD!!!,0,0

Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug..

Everywyere I have searched on the Interweb I find DBA's preferring JOIN over complicated queries... Comparing 0.004 seconds to 0.009 seconds will only matter if do millions of queries per minute.

Notice how I defined the primary keys. It needs to be this way if you want to import and "replace" and not "delete & import". Much faster.

New table definitions at the end.

Code: Select all

@echo off

echo START %DATE% %TIME%  1>>import.log 2>&1
echo.  1>>import.log 2>&1

set PATH=%PATH%;"C:\MariaDB\Bin";"C:\Program Files\7-zip"
set IKEY=KEYKEYKEY
set IUSR=DBUSER
set IPAS=DBPASSWORD
set DATA=hmailserver_extra

curl -G https://download.maxmind.com/app/geoip_download -d "edition_id=GeoLite2-Country-CSV" -d "license_key=%IKEY%" -d "suffix=zip" -o GeoLite2-Country-CSV.zip 1>>import.log 2>&1
7z e -y GeoLite2-Country-CSV.zip 1>>import.log 2>&1

:country
fc GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv >NUL && goto location || goto newcountry 1>>import.log 2>&1

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range 1>>import.log 2>&1
copy /y GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv 1>>import.log 2>&1
mysqlimport -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoCountry.csv 1>>import.log 2>&1

:location
fc GeoLite2-Country-Locations-en.csv GeoLocation.csv >NUL && goto same || goto newlocation 1>>import.log 2>&1

:newlocation
copy /y GeoLite2-Country-Locations-en.csv GeoLocation.csv 1>>import.log 2>&1
mysqlimport -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoLocation.csv 1>>import.log 2>&1

:same
echo FINISH %DATE% %TIME%  1>>import.log 2>&1
echo.  1>>import.log 2>&1
goto exit

    SELECT * FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id
    WHERE network_start_integer <= INET_ATON('8.8.8.8') AND INET_ATON('8.8.8.8') <= network_last_integer;

Table       Create Table
----------  -----------------------------------------------------------------
geocountry  CREATE TABLE geocountry (
              network_start_integer bigint(20) NOT NULL DEFAULT '0',
              network_last_integer bigint(20) NOT NULL DEFAULT '0',
              geoname_id varchar(20) NOT NULL DEFAULT '',
              registered_country_geoname_id varchar(20) DEFAULT NULL,
              represented_country_geoname_id varchar(20) DEFAULT NULL,
              is_anonymous_proxy varchar(1) DEFAULT NULL,
              is_satellite_provider varchar(1) DEFAULT NULL,
              PRIMARY KEY (network_start_integer,network_last_integer),
              KEY network_start_integer (network_start_integer),
              KEY network_last_integer (network_last_integer)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Table        Create Table
-----------  ---------------------------------------------------
geolocation  CREATE TABLE geolocation (
               geoname_id varchar(20) NOT NULL DEFAULT '',
               locale_code varchar(192) DEFAULT NULL,
               continent_code varchar(2) DEFAULT NULL,
               continent_name varchar(192) DEFAULT NULL,
               country_iso_code varchar(2) DEFAULT NULL,
               country_name varchar(192) DEFAULT NULL,
               is_in_european_union varchar(1) DEFAULT NULL,
               PRIMARY KEY (geoname_id)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


:exit
exit 0

[palinka]

Everywyere I have searched on the Interweb I find DBA's preferring JOIN over complicated queries... Comparing 0.004 seconds to 0.009 seconds will only matter if do millions of queries per minute.

Notice how I defined the primary keys. It needs to be this way if you want to import and "replace" and not "delete & import". Much faster.

I tried primary key and it didn't make a difference. The query (and having a key/keys) seem more important.

The reason my query is twice as fast as yours is because the LIMIT 1 in the subquery ends the table scan immediately, and then the outside query just looks at what's left over.

The LIMIT 1 in the outside query doesn't seem to make a difference on the little bit of testing I did. However, *I think* when the IP is in a high range (2xx.a.b.c), it could make a difference. Further testing is required.

Anyway, Mr. Turbo Audi Guy, isn't faster ALWAYS better????

[palinka]

Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug..

From phpmyadmin after deleting the first row and inserting same row from csv:

Code: Select all

INSERT INTO geocountry (
	network_start_integer,
	network_last_integer,
	geoname_id,
	registered_country_geoname_id,
	represented_country_geoname_id,
	is_anonymous_proxy,
	is_satellite_provider
) 
VALUES (
	'16777216',
	'16777471',
	'2077456',
	'2077456',
	'',
	'0',
	'0'
)

Code: Select all

Warning: #1366 Incorrect integer value: '' for column `geoip`.`geocountry`.`represented_country_geoname_id` at row 1

So, I was right. It was the blank field.

Interestingly, phpmyadmin would not take the query unless I wrapped all the values in single quotes - including the empty value. This is obviously not a problem for mysqlimport.

Nothing you can do about the empty values unless maxmind changes their converter or you can always use my slow as molasses method: line by line import.

As part of my soon-to-be-abandoned maxmind project, I use powershell import-csv, which is capable of spitting out properly wrapped and/or transformed values. It takes almost 10 minutes to load the 320k+ line maxmind csv, but if you do that, you can turn blanks into zeros and you won't get the warnings anymore.

I think I can live with the warnings, since they don't do any harm and the table still loads just fine.

[SorenR]

Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug..

From phpmyadmin after deleting the first row and inserting same row from csv:

Code: Select all

INSERT INTO geocountry (
	network_start_integer,
	network_last_integer,
	geoname_id,
	registered_country_geoname_id,
	represented_country_geoname_id,
	is_anonymous_proxy,
	is_satellite_provider
) 
VALUES (
	'16777216',
	'16777471',
	'2077456',
	'2077456',
	'',
	'0',
	'0'
)

Code: Select all

Warning: #1366 Incorrect integer value: '' for column `geoip`.`geocountry`.`represented_country_geoname_id` at row 1

So, I was right. It was the blank field.

Interestingly, phpmyadmin would not take the query unless I wrapped all the values in single quotes - including the empty value. This is obviously not a problem for mysqlimport.

Nothing you can do about the empty values unless maxmind changes their converter or you can always use my slow as molasses method: line by line import.

As part of my soon-to-be-abandoned maxmind project, I use powershell import-csv, which is capable of spitting out properly wrapped and/or transformed values. It takes almost 10 minutes to load the 320k+ line maxmind csv, but if you do that, you can turn blanks into zeros and you won't get the warnings anymore.

I think I can live with the warnings, since they don't do any harm and the table still loads just fine.

So, you did not check my tabledefinitions ... Network_start and Network_end are bigint's and the rest is varchar. varchar don't care about blanks. I don't plan on using the boolean values but you could define a default value "0" if missing.

[palinka]

Here's my fixed up powershell version.

* Added the lightning speed import
* Deleted create scheduled task
* Added colorful email notifications

https://github.com/palinkas-jo-reggelt/GeoLite2SQL

[palinka]

I just tried the city data for the first time and ran it on my IP. The supposed accuracy_radius is 1 meter for my IP but the latitude/longitude coordinates were off by several blocks. Still the same town, though. Most online geoip locators put me in the next town over.

I tried searching by latitude/longitude, but those are not unique or keyed columns and the search takes forever. Plus, there were no results. Then I switched to LIKE instead of equals and dropped the last digit of each (latitude LIKE xx.xxx AND longitude LIKE -xx.xxx - 3 digits instead of 4), but that didn't finish before typing this reply. [edit - still no results, and "Query took 221.7181 seconds"]

Result: more accurate than others, but not anything close to the listed accuracy radius.

My script still needs some cleaning up. I'll post later.

[palinka]

Finally got around to cleaning up the script. Now does both city and country locations. Pick the one you like or do both.

It takes a little longer than Sorens because for error checking reasons, I count the records in the CSV and that adds several seconds to the country CSV and minutes to the city CSV with over 3 million records. It's still orders of magnitude faster than my old script.

Plus, you get a nice color-coded email at the end.

And of course, you get the debug log as an attachment:

Code: Select all

:::  Backup Routine Wednesday, April 21, 2021 :::
 
4/21/2021 5:00:44 AM : GeoIP Update Start
4/21/2021 5:00:44 AM : ----------------------------
4/21/2021 5:00:44 AM : Checking for script update at GitHub
4/21/2021 5:00:45 AM : Backup & Upload script is latest version: 2.04
4/21/2021 5:00:45 AM : ----------------------------
4/21/2021 5:00:45 AM : Deleting old files
4/21/2021 5:00:45 AM : Folder C:\scripts\GeoLite2SQL\GeoLite2-City-CSV successfully deleted
4/21/2021 5:00:45 AM : Old zip file C:\scripts\GeoLite2SQL\Script-Created-Files\GeoLite2-City-CSV.zip successfully deleted
4/21/2021 5:00:45 AM : ----------------------------
4/21/2021 5:00:45 AM : Downloading MaxMind data
4/21/2021 5:00:49 AM : MaxMind data successfully downloaded in 4 seconds
4/21/2021 5:00:53 AM : MaxMind data successfully unzipped in 3 seconds
4/21/2021 5:00:53 AM : Locations CSV successfully renamed
4/21/2021 5:00:53 AM : ----------------------------
4/21/2021 5:00:53 AM : Counting database records for comparison
4/21/2021 5:00:58 AM : 3,494,500 database records prior to starting update
4/21/2021 5:00:58 AM : ----------------------------
4/21/2021 5:00:58 AM : Counting CSV records for comparison
4/21/2021 5:14:26 AM : Counted 3,495,740 IPv4 records in new CSV in 13 minutes 28 seconds
4/21/2021 5:14:26 AM : ----------------------------
4/21/2021 5:14:26 AM : Converting CSV
4/21/2021 5:14:54 AM : City IP CSV successfully converted to integer-range in 27 seconds
4/21/2021 5:14:54 AM : ----------------------------
4/21/2021 5:14:54 AM : Drop and recreate database tables
4/21/2021 5:14:54 AM : Database tables successfully dropped and created
4/21/2021 5:14:54 AM : ----------------------------
4/21/2021 5:14:54 AM : Import city IP information
4/21/2021 5:19:56 AM : 3,495,740 city IP records imported in 5 minutes 1 second
4/21/2021 5:19:56 AM : ----------------------------
4/21/2021 5:19:56 AM : Import city name information
4/21/2021 5:20:45 AM : 122,058 city name records imported in 49 seconds
4/21/2021 5:20:45 AM : ----------------------------
4/21/2021 5:20:45 AM : Successfully imported 3,495,740 records in 20 minutes 0 seconds
4/21/2021 5:20:45 AM : [INFO] Net change of -1,240 records since last update
4/21/2021 5:20:46 AM : GeoIP update finished

Also, it checks github for the latest version, so if there are changes you get notified.

https://github.com/palinkas-jo-reggelt/GeoLite2SQL

[gotspatel]

Just tested with this ip 45.58.142.12 it says china but it is actually netherlands, any idea Why?

I also tested many other IP They come correct.

Also a small glitch in GeoLite2SQL-Config.dist.ps1 Line 58

It should be $CountryLocationLang = "en"

[palinka]

Just tested with this ip 45.58.142.12

it says china but it is actually netherlands, any idea Why?

Also a small glitch in GeoLite2SQL-Config.dist.ps1 Line 58

It should be $CountryLocationLang = "en"

Thanks for the heads up. But it should actually be $LocationLanguage. I changed a couple of config variables because the script is no longer exclusive to the country database version. I just updated github.

As far as the IP being wrong: you'll have to ask MaxMind, I guess. I've noticed many discrepancies as well. My php custom log has a link to ip-api.com for more info on the location. Sometimes the country written into the log (from MaxMind) and the ip-api.com data don't match.

I don't know how many companies actually collect geoip data, but for sure nearly all the geoip websites rely on someone else's data. I believe MaxMind collects their own data. There are probably only a couple other companies that do that.

[gotspatel]

Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/

[palinka]

Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

That's very strange. Sometimes different versions of powershell behave differently.

The below code works for me. Its what should be on github.

GeoLite2SQL-Config.ps1

Code: Select all

$LocationLanguage     = "en"

GeoLite2SQL-Country.ps1

Code: Select all

$CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$LocationLanguage.csv"
$LocationsRenamed = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLocations.csv"



<#  Rename Locations CSV  #>
Try {
	Rename-Item $CountryLocations $LocationsRenamed -ErrorAction Stop
	Debug "Locations CSV successfully renamed"
}
Catch {
	Debug "[ERROR] : Unable to rename locations CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to rename locations CSV. See error log."
	EmailResults
	Exit
}

Alternatively, you could try this:

Code: Select all

$CountryLocations = $PSScriptRoot + "\GeoLite2-Country-CSV\GeoLite2-Country-Locations-" + $LocationLanguage + ".csv"

[palinka]

If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/

Are you SURE the error is on MaxMind?

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.

[gotspatel]

Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

That's very strange. Sometimes different versions of powershell behave differently.

The below code works for me. Its what should be on github.

GeoLite2SQL-Config.ps1

Code: Select all

$LocationLanguage     = "en"

GeoLite2SQL-Country.ps1

Code: Select all

$CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$LocationLanguage.csv"
$LocationsRenamed = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLocations.csv"



<#  Rename Locations CSV  #>
Try {
	Rename-Item $CountryLocations $LocationsRenamed -ErrorAction Stop
	Debug "Locations CSV successfully renamed"
}
Catch {
	Debug "[ERROR] : Unable to rename locations CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to rename locations CSV. See error log."
	EmailResults
	Exit
}

Alternatively, you could try this:

Code: Select all

$CountryLocations = $PSScriptRoot + "\GeoLite2-Country-CSV\GeoLite2-Country-Locations-" + $LocationLanguage + ".csv"

Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"

[gotspatel]

If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/

Are you SURE the error is on MaxMind?

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.

I am sure about that Particular IP as Previously I had hosted my mail server there.

[palinka]

Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"

Its not missing. It was removed. Use GeoLite2SQL-Country.ps1 or GeoLite2SQL-City.ps1 (or both). I probably have to update the readme file.

Both of these scripts create new, separate tables. If you run both, you will end up with 5 tables:

* Your old geolite2sql script table (not removed or overwritten)
* geocountry
* geolocations (geocountry name data)
* geocity
* geocitylocations

This way, you can continue to run the old table while playing with the new ones. After you get the new one going, change the query in eventhandlers.vbs as shown in the info part of the script at the top.

In eventhandlers.vbs, you can use this subroutine to get country info (this one is for COUNTRY - not city lookup):

Code: Select all

Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=databasename; User=username; Password=supersecretpassword;"

    If oConn.State <> 1 Then
		EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If

    m_CountryCode = "NX"
    m_CountryName = "NOT FOUND"

    Set oRecord = oConn.Execute("SELECT country_code, country_name FROM (SELECT * FROM geocountry WHERE INET_ATON('" & sIPAddress & "') <= network_last_integer	LIMIT 1) AS a INNER JOIN geolocations AS b on a.geoname_id = b.geoname_id WHERE network_start_integer <= INET_ATON('" & sIPAddress & "') LIMIT 1;")
    Do Until oRecord.EOF
        m_CountryCode = oRecord("country_code")
        m_CountryName = oRecord("country_name")
        oRecord.MoveNext
    Loop
    oConn.Close
    Set oRecord = Nothing
End Sub

Then you can call it like this. I use it at the top of OnHELO, but you can also use it at OnClientConnect.

Code: Select all

	REM	- GeoIP Lookup
	Dim m_CountryCode, m_CountryName
	Call GeoIPLookup(oClient.IPAddress, m_CountryCode, m_CountryName)

	If m_CountryCode = Empty Then m_CountryCode = "NX"
	If m_CountryName = Empty Then m_CountryName = "No.GeoIP.Record"
	If m_CountryCode = "NX" Then EventLog.Write("Error in GeoIP lookup - IP: " & oClient.IPAddress)

	If (oClient.Port = 25) Then
		REM	 - ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ|KR|NX)$"
		If Lookup(strBase, m_CountryCode) Then bolGeoIP = True
	Else
		REM	- ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|NX)$"
		If Lookup(strBase, m_CountryCode) Then 
			bolGeoIP = True
		End If
	End If

	If bolGeoIP Then
		REM	- Connection PASSED examination
		' Log it or do nothing since its OK to proceed
	Else
		REM	- Disconnect all others.
		Result.Value = 2
		Result.Message = ". 01 This mail server does not accept mail submission from " & m_CountryName & ". If you believe that this failure is in error, please contact the intended recipient via alternate means."
		' Log it, autoban it, firewall ban it or whatever
		Exit Sub
	End If

[palinka]

If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/

Are you SURE the error is on MaxMind?

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.

I am sure about that Particular IP as Previously I had hosted my mail server there.

I guess that qualifies.

https://www.maxmind.com/en/geoip2-city- ... comparison

The US is listed as only 66% accurate. The only places that are 100% accurate are small islands.

[gotspatel]

Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"

Its not missing. It was removed. Use GeoLite2SQL-Country.ps1 or GeoLite2SQL-City.ps1 (or both). I probably have to update the readme file.

Both of these scripts create new, separate tables. If you run both, you will end up with 5 tables:

* Your old geolite2sql script table (not removed or overwritten)
* geocountry
* geolocations (geocountry name data)
* geocity
* geocitylocations

This way, you can continue to run the old table while playing with the new ones. After you get the new one going, change the query in eventhandlers.vbs as shown in the info part of the script at the top.

In eventhandlers.vbs, you can use this subroutine to get country info (this one is for COUNTRY - not city lookup):

Thanks for the Headsup will check it out

[SorenR]

Hmm...
Checked with my online GeoIP provider..

Code: Select all

{
   "ip":"45.58.142.12",
   "continent_code":"NA",
   "continent_name":"North America",
   "country_code2":"US",
   "country_code3":"USA",
   "country_name":"United States",
   "country_capital":"Washington, D.C.",
   "state_prov":"Nevada",
   "district":"Paradise",
   "city":"Las Vegas",
   "zipcode":"89120-3470",
   "latitude":"36.08625",
   "longitude":"-115.10385",
   "is_eu":false,
   "calling_code":"+1",
   "country_tld":".us",
   "languages":"en-US,es-US,haw,fr",
   "country_flag":"https://ipgeolocation.io/static/flags/us_64.png",
   "geoname_id":"5510070",
   "isp":"Sharktech",
   "connection_type":"",
   "organization":"Sharktech",
   "currency":{
       "code":"USD",
       "name":"US Dollar",
       "symbol":"$"},
   "time_zone":{
       "name":"America/Los_Angeles",
       "offset":-8,
       "current_time":"2021-04-22 05:00:37.937-0700",
       "current_time_unix":1619092837.937,
       "is_dst":true,
       "dst_savings":1}
   }

https://www.opentracker.net/feature/ip-tracker/ says

Code: Select all

P address: 45.58.142.12
City: Amsterdam
Region name: Noord-Holland
Country name: Netherlands
Life Expectency: 78.3
Avg income: 23.409 EUR
Timezone: Europe/Amsterdam
Sub continent: Western Europe
Country code: NL
Geo-targeting: true
   ◉ Latitude: 37.751
   ◎ Longitude: -97.822
World currency: EUR
EU member: true
org: Sharktech
isp: Sharktech
Connection: undefined
Continent: Europe
Population: 15.864.000
IP range tracked:
Surface area: 41.526 km sq.
GNP: 371.362 mln.
Demographic data: true
Ad (re)targeting: true

Where do they get their info from?

[SorenR]

https://www.iplocation.net/ip-lookup

Suggests Amsterdam, Las Vegas or Luoyang

[gotspatel]

Very Confusing

https://www.iplocation.net/ip-lookup

Shows Netherlands, USA, China

[palinka]

I count the number of records in the MaxMind CSV. I think its an important error checking variable. I was using (get-content).count but it takes a long time. Over 10 minutes on the city IP data file with >3M records. It uses a lot of memory too.

I changed the counting method to IO.StreamReader and cut the city IP data count time down to 2 seconds. The country IP data (>300k records) went from around 20 seconds to less than 1 second. A nice speed boost!

https://github.com/palinkas-jo-reggelt/GeoLite2SQL

[RvdH]

Code: Select all

Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=databasename; User=username; Password=supersecretpassword;"

    If oConn.State <> 1 Then
		EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If

    m_CountryCode = "NX"
    m_CountryName = "NOT FOUND"

    Set oRecord = oConn.Execute("SELECT country_code, country_name FROM (SELECT * FROM geocountry WHERE INET_ATON('" & sIPAddress & "') <= network_last_integer	LIMIT 1) AS a INNER JOIN geolocations AS b on a.geoname_id = b.geoname_id WHERE network_start_integer <= INET_ATON('" & sIPAddress & "') LIMIT 1;")
    Do Until oRecord.EOF
        m_CountryCode = oRecord("country_code")
        m_CountryName = oRecord("country_name")
        oRecord.MoveNext
    Loop
    oConn.Close
    Set oRecord = Nothing
End Sub

Kinda weird to loop through the records when you LIMIT the results to return 1 record (as you either have one or none at all)

Code: Select all

Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=databasename; User=username; Password=supersecretpassword;"

    If oConn.State <> 1 Then
		EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If

    m_CountryCode = "NX"
    m_CountryName = "NOT FOUND"

    Set oRecord = oConn.Execute("SELECT country_code, country_name FROM (SELECT * FROM geocountry WHERE INET_ATON('" & sIPAddress & "') <= network_last_integer	LIMIT 1) AS a INNER JOIN geolocations AS b on a.geoname_id = b.geoname_id WHERE network_start_integer <= INET_ATON('" & sIPAddress & "') LIMIT 1;")
    If Not oRecord.EOF Then
        m_CountryCode = oRecord("country_code")
        m_CountryName = oRecord("country_name")
    End If
    oConn.Close
    Set oRecord = Nothing
End Sub

[palinka]

Kinda weird to loop through the records when you LIMIT the results to return 1 record (as you either have one or none at all)

Kinda looks the same to me.

Start loop > only one result, so its the last one > EOF > done

Start looking > only one result, so its the last one > EOF > done

By the way, the purpose of LIMIT 1 is not what you may believe. There are two in the query. The one in the subquery shuts down the table scan of network_last_integer making the query a LOT faster. The 2nd one is not really necessary because by definition you're only going to have one result with or without LIMIT 1. The 2nd one (at the end) was experimental and I never removed it. It does no harm, but it also does no good.

[RvdH]

That is not the code i am actually using, i am using the one with IPv6 (INET6_ATON) support

Code: Select all

Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.1 Driver}; Server=localhost; Database=databasename; User=username; Password=supersecretpassword;"
    
    If oConn.State <> 1 Then
        EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If
    
    m_CountryCode = "NX"
    m_CountryName = "NOT FOUND"
    
    Set oRecord = oConn.Execute("SELECT country_iso_code, country_name FROM (SELECT * FROM geocountry WHERE INET6_ATON('" & sIPAddress & "') <= network_end LIMIT 1) AS a INNER JOIN countrylocations AS b on a.geoname_id = b.geoname_id WHERE network_start <= INET6_ATON('" & sIPAddress & "');")
    If Not oRecord.EOF Then
        m_CountryCode = oRecord("country_iso_code")
        m_CountryName = oRecord("country_name")
    End If
    Set oRecord = Nothing
    
    oConn.Close
    Set oConn = Nothing
End Sub

What i have been teached it is better to do:

Code: Select all

If Not oRecord.EOF Then
    Do Until oRecord.EOF
        m_CountryCode = oRecord("country_iso_code")
        m_CountryName = oRecord("country_name")
        oRecord.MoveNext
    Loop
End If

or in this case

Code: Select all

If Not oRecord.EOF Then
    m_CountryCode = oRecord("country_iso_code")
    m_CountryName = oRecord("country_name")
End If

No need to initiate a loop when no records exist or no more then 1 records exists

[palinka]

That is not the code i am actually using, i am using the one with IPv6 (INET6_ATON) support

Me too.

What i have been teached it is better to do:

Soren taught me, so take it up with him.

[SorenR]

Well... Please note the "LIMIT 1" in the SQL query

Not looping anything

Code: Select all

'******************************************************************************************************************************
'********** GeoLite lookup (MySQL)                                                                                   **********
'******************************************************************************************************************************

'   geocountry  CREATE TABLE geocountry (
'               network_start_integer bigint(20) NOT NULL DEFAULT '0',
'               network_last_integer bigint(20) NOT NULL DEFAULT '0',
'               geoname_id varchar(20) NOT NULL DEFAULT '',
'               registered_country_geoname_id varchar(20) DEFAULT NULL,
'               represented_country_geoname_id varchar(20) DEFAULT NULL,
'               is_anonymous_proxy varchar(1) DEFAULT NULL,
'               is_satellite_provider varchar(1) DEFAULT NULL,
'               PRIMARY KEY (network_start_integer,network_last_integer),
'               KEY network_start_integer (network_start_integer),
'               KEY network_last_integer (network_last_integer)
'             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


'   geolocation  CREATE TABLE geolocation (
'                geoname_id varchar(20) NOT NULL DEFAULT '',
'                locale_code varchar(192) DEFAULT NULL,
'                continent_code varchar(2) DEFAULT NULL,
'                continent_name varchar(192) DEFAULT NULL,
'                country_iso_code varchar(2) DEFAULT NULL,
'                country_name varchar(192) DEFAULT NULL,
'                is_in_european_union varchar(1) DEFAULT NULL,
'                PRIMARY KEY (geoname_id)
'              ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Function GeoLite(strIP) : GeoLite = "VOID"
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM (" & _
                              "    SELECT * FROM geocountry " &_
                              "    WHERE INET_ATON('" & strIP & "') >= network_start_integer " & _
                              "    ORDER BY network_start_integer DESC " & _
                              "    LIMIT 1 " & _
                              ") AS country " & _
                              "LEFT JOIN geolocation AS location on country.geoname_id = location.geoname_id " & _
                              "WHERE INET_ATON('" & strIP & "') <= network_last_integer;")
    If IsNull(oRecord("country_iso_code")) Then
        GeoLite = "VOID"
    Else
        If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code")
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function

'******************************************************************************************************************************
'********** Triggers                                                                                                 **********
'******************************************************************************************************************************

    '
    '   GeoLite ....
    '
    strGeo = GeoLite(oClient.IPAddress)
    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
        EventLogX.LogFile = "geo-blocked"
        Result.Value = 1
        If AutoBan(oClient.IPAddress, "GEO Blocked " & strGeo & " " & strPort, 48, "h") Then _
            EventLogX.Write( LPad("GEO Blocked", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
        Disconnect(oClient.IPAddress)
        Set EventLogX = Nothing
        Exit Sub
    End If

[palinka]

Well... Please note the "LIMIT 1" in the SQL query

By the way, the purpose of LIMIT 1 is not what you may believe. There are two in the query. The one in the subquery shuts down the table scan of network_last_integer making the query a LOT faster. The 2nd one is not really necessary because by definition you're only going to have one result with or without LIMIT 1. The 2nd one (at the end) was experimental and I never removed it. It does no harm, but it also does no good.

Even if you remove both LIMIT 1's, its still impossible to get more than one result.

[RvdH]

Well... Please note the "LIMIT 1" in the SQL query

Not looping anything

Code: Select all

'******************************************************************************************************************************
'********** GeoLite lookup (MySQL)                                                                                   **********
'******************************************************************************************************************************

'   geocountry  CREATE TABLE geocountry (
'               network_start_integer bigint(20) NOT NULL DEFAULT '0',
'               network_last_integer bigint(20) NOT NULL DEFAULT '0',
'               geoname_id varchar(20) NOT NULL DEFAULT '',
'               registered_country_geoname_id varchar(20) DEFAULT NULL,
'               represented_country_geoname_id varchar(20) DEFAULT NULL,
'               is_anonymous_proxy varchar(1) DEFAULT NULL,
'               is_satellite_provider varchar(1) DEFAULT NULL,
'               PRIMARY KEY (network_start_integer,network_last_integer),
'               KEY network_start_integer (network_start_integer),
'               KEY network_last_integer (network_last_integer)
'             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


'   geolocation  CREATE TABLE geolocation (
'                geoname_id varchar(20) NOT NULL DEFAULT '',
'                locale_code varchar(192) DEFAULT NULL,
'                continent_code varchar(2) DEFAULT NULL,
'                continent_name varchar(192) DEFAULT NULL,
'                country_iso_code varchar(2) DEFAULT NULL,
'                country_name varchar(192) DEFAULT NULL,
'                is_in_european_union varchar(1) DEFAULT NULL,
'                PRIMARY KEY (geoname_id)
'              ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Function GeoLite(strIP) : GeoLite = "VOID"
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM (" & _
                              "    SELECT * FROM geocountry " &_
                              "    WHERE INET_ATON('" & strIP & "') >= network_start_integer " & _
                              "    ORDER BY network_start_integer DESC " & _
                              "    LIMIT 1 " & _
                              ") AS country " & _
                              "LEFT JOIN geolocation AS location on country.geoname_id = location.geoname_id " & _
                              "WHERE INET_ATON('" & strIP & "') <= network_last_integer;")
    If IsNull(oRecord("country_iso_code")) Then
        GeoLite = "VOID"
    Else
        If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code")
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function

'******************************************************************************************************************************
'********** Triggers                                                                                                 **********
'******************************************************************************************************************************

    '
    '   GeoLite ....
    '
    strGeo = GeoLite(oClient.IPAddress)
    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
        EventLogX.LogFile = "geo-blocked"
        Result.Value = 1
        If AutoBan(oClient.IPAddress, "GEO Blocked " & strGeo & " " & strPort, 48, "h") Then _
            EventLogX.Write( LPad("GEO Blocked", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
        Disconnect(oClient.IPAddress)
        Set EventLogX = Nothing
        Exit Sub
    End If

'If IsNull(oRecord("country_iso_code")) Then' is literally the same as doing 'If Not oRecord.EOF Then'

If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code"), is a bit of a redundant check....country code is always 2 chars long if any

[RvdH]

@palinka

Yesterday i got this:

Code: Select all

::: MaxMind GeoCountry Database Import Routine woensdag 1 maart 2023 :::
 
1-3-2023 07:00:03 : GeoIP Country Update Start
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Checking for script update at GitHub
1-3-2023 07:00:03 : GeoLite2SQL script is latest version: 3.05
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Deleting old files
1-3-2023 07:00:03 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Downloading MaxMind data
1-3-2023 07:00:11 : [ERROR] : Unable to download MaxMind data : HTTP status 304: The server's response was not valid. The server was not following the defined protocol. Resume the job, and then Background Intelligent Transfer Service (BITS) will try again.

1-3-2023 07:00:11 : [ERROR] : Quitting Script
1-3-2023 07:00:11 : GeoIP Country Update Finished

Any idea? It keeps failing (today again) with HTTP status 304
Previous month(s) no issues whatsoever

If i put a debug on the $URL variable it returns the correct URL and i can download within browser without issue

[palinka]

@palinka

Yesterday i got this:

Code: Select all

::: MaxMind GeoCountry Database Import Routine woensdag 1 maart 2023 :::
 
1-3-2023 07:00:03 : GeoIP Country Update Start
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Checking for script update at GitHub
1-3-2023 07:00:03 : GeoLite2SQL script is latest version: 3.05
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Deleting old files
1-3-2023 07:00:03 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Downloading MaxMind data
1-3-2023 07:00:11 : [ERROR] : Unable to download MaxMind data : HTTP status 304: The server's response was not valid. The server was not following the defined protocol. Resume the job, and then Background Intelligent Transfer Service (BITS) will try again.

1-3-2023 07:00:11 : [ERROR] : Quitting Script
1-3-2023 07:00:11 : GeoIP Country Update Finished

Any idea? It keeps failing (today again) with HTTP status 304
Previous month(s) no issues whatsoever

If i put a debug on the $URL variable it returns the correct URL and i can download within browser without issue

I got the same thing. I'm pretty sure its on MaxMind's side. My "plan" was to just wait until next week and see if it still happens.

The error description is coming from BITS, not from powershell or anything in my script.

[palinka]

I think I found it. I plugged the URL (including license key, etc) into my browser and got the error "Database not found". On a guess, I capitalized "City" and it worked. So it looks like they made some kind of change on their server that enforces capitalization. I'll add a rule to the script that makes sure "City" and "Country" are properly capitalized.

In the meantime, per the instructions:

Code: Select all

.EXAMPLE
	Run script as follows:

	C:\path\to\Geolite2SQL.ps1 country
	C:\path\to\Geolite2SQL.ps1 city

Change that to the following in order to capitalize "City" and "Country" because the parameter var becomes part of the URL:

Code: Select all

.EXAMPLE
	Run script as follows:

	C:\path\to\Geolite2SQL.ps1 Country
	C:\path\to\Geolite2SQL.ps1 City

[RvdH]

I doubt that is the reason, eg:

Code: Select all

	If ($SelectType -match 'country') {
		$Type = "Country"
	} Else {
		$Type = "City"
	}

$Type used in $URL is properly capitalized, not?

[palinka]

^^^^ This is not the problem because I already validate the parameter and change the case. That's already in the script. Something weird is going on and its going to take some experimentation to debug it.

EDIT: ^^^^ refers to my previous post, not RvdH response.

[palinka]

I doubt that is the reason, eg:

Code: Select all

	If ($SelectType -match 'country') {
		$Type = "Country"
	} Else {
		$Type = "City"
	}

$Type used in $URL is properly capitalized, not?

Yes. I cross posted - didn't see your post.

[RvdH]

I see a *.tmp file of 2822 Kb being created while running the script.... and yes GeoLite2-Country-CSV_20230228.zip is exactly 2822 Kb

[edit]
it's a weird one, eh? Maybe we should sit it out and check if it is fixed next week

[palinka]

I see a *.tmp file of 2822 Kb being created while running the script.... and yes GeoLite2-Country-CSV_20230228.zip is exactly 2822 Kb

[edit]
it's a weird one, eh? Maybe we should sit it out and check if it is fixed next week

On Monday I made some changes to the script that had to do with changing the data types in the database so it aligns with MaxMind suggestions. During that process I tested it several times which included downloading the files, of course. I had no issues at all.

My script runs weekly on Wednesday. I got the same error and assumed it was a temporary issue on MaxMind's end. I still think that's probably the case.

So far today, I have downloaded the files using the URL in my browser successfully 100% of the time (I tried 3 times). Using the script, it worked once out of 6 or 7 tries.

Edit - I just tried one more time and noticed something. First, it worked. However, under strange circumstances: I'm running the script from powershell window and the only 2 times the script worked is when I ran the script a second time without closing the window - that gives me file lock issues with the debug log and a bunch of errors related to that file lock. The instructions in the BITS error are "Resume the job, and then Background Intelligent Transfer Service (BITS) will try again." I'm not sure what to make of that, but it doesn't appear to be coincidental.

[palinka]

I think I found a fix.

Change line 338 to:

Code: Select all

	Start-BitsTransfer -Source $URL -Destination $DownloadedZip -Asynchronous -ErrorAction Stop
	Get-BitsTransfer | Complete-BitsTransfer

I'm still testing, but it seems to work fine. Since we all (you, me, Gotspatel also reported this on github) all had this error at exactly the same time, something has to have changed at MaxMind.

Edit: "Get-BitsTransfer | Complete-BitsTransfer" removes the tmp files.

I have no idea why this worked since the beginning until Wednesday of this week. Its a mystery.

[RvdH]

That doesn't fix anything....

What happens when you delete existing GeoLite2-Country-CSV.zip?

Code: Select all

::: MaxMind GeoCountry Database Import Routine donderdag 2 maart 2023 :::
 
2-3-2023 20:25:09 : GeoIP Country Update Start
2-3-2023 20:25:09 : ----------------------------
2-3-2023 20:25:09 : Checking for script update at GitHub
2-3-2023 20:25:10 : GeoLite2SQL script is latest version: 3.05
2-3-2023 20:25:10 : ----------------------------
2-3-2023 20:25:10 : Deleting old files
2-3-2023 20:25:10 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
2-3-2023 20:25:10 : ----------------------------
2-3-2023 20:25:10 : Downloading MaxMind data
2-3-2023 20:25:15 : MaxMind data successfully downloaded in 5 seconds
2-3-2023 20:25:17 : MaxMind data successfully unzipped in 1 second
2-3-2023 20:25:17 : Locations CSV successfully renamed
2-3-2023 20:25:17 : ----------------------------
2-3-2023 20:25:17 : Counting database records for comparison
2-3-2023 20:25:19 : 666.760 database records prior to starting update
2-3-2023 20:25:19 : ----------------------------
2-3-2023 20:25:19 : Counting CSV records for comparison
2-3-2023 20:25:26 : Counted 666.760 records in new IPv4 & IPv6 CSVs in 7 seconds
2-3-2023 20:25:26 : ----------------------------
2-3-2023 20:25:26 : Converting CSVs to hex-range
2-3-2023 20:25:29 : Country IPv4 CSV successfully converted to hex-range in 2 seconds
2-3-2023 20:25:33 : Country IPv6 CSV successfully converted to hex-range in 3 seconds
2-3-2023 20:25:33 : ----------------------------
2-3-2023 20:25:33 : Drop and recreate database tables
2-3-2023 20:25:34 : Database tables successfully dropped and created
2-3-2023 20:25:34 : ----------------------------
2-3-2023 20:25:34 : Import converted CSVs to database
2-3-2023 20:25:57 : [OK] Country IPv4 data imported in 22 seconds
2-3-2023 20:26:13 : [OK] Country IPv6 data imported in 16 seconds
2-3-2023 20:26:13 : [OK] Country name data imported in less than 1 second
2-3-2023 20:26:14 : ----------------------------
2-3-2023 20:26:14 : Successfully imported 666.760 records
2-3-2023 20:26:14 : Finished update in 1 minute 4 seconds
2-3-2023 20:26:14 : [INFO] Net change of 0 records since last update
2-3-2023 20:26:14 : GeoIP Country Update Finished

Net change of 0 records since last update, not that strange as as it uses existing GeoLite2-Country-CSV.zip dated 21-02-2013 and not the downloaded file


if i delete existing GeoLite2-Country-CSV.zip

Code: Select all

::: MaxMind GeoCountry Database Import Routine donderdag 2 maart 2023 :::
 
2-3-2023 20:26:25 : GeoIP Country Update Start
2-3-2023 20:26:25 : ----------------------------
2-3-2023 20:26:26 : Checking for script update at GitHub
2-3-2023 20:26:26 : GeoLite2SQL script is latest version: 3.05
2-3-2023 20:26:26 : ----------------------------
2-3-2023 20:26:26 : Deleting old files
2-3-2023 20:26:26 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
2-3-2023 20:26:26 : ----------------------------
2-3-2023 20:26:26 : Downloading MaxMind data
2-3-2023 20:26:27 : MaxMind data successfully downloaded in less than 1 second
2-3-2023 20:26:28 : [ERROR] : Unable to unzip MaxMind data : The path 'C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV.zip' either does not exist or is not a valid file system path.
2-3-2023 20:26:28 : [ERROR] : Quitting Script
2-3-2023 20:26:28 : GeoIP Country Update Finished

[palinka]

Back to the drawing board.....

[RvdH]

Back to the drawing board.....

[palinka]

Back to the drawing board.....

Laugh it up. Try and help me find a solution to something i had nothing to do with creating.

[gotspatel]

I may be worng but

Instead of this

Code: Select all

Start-BitsTransfer -Source $url -Destination $output -ErrorAction Stop

I tried with this

Code: Select all

Invoke-WebRequest -Uri $url -OutFile $output

and It works, Also to add Start-BitsTransfer works with other websites

so may be problem can be with maxmind and Start-BitsTransfer

[palinka]

I may be worng but

Instead of this

Code: Select all

Start-BitsTransfer -Source $url -Destination $output -ErrorAction Stop

I tried with this

Code: Select all

Invoke-WebRequest -Uri $url -OutFile $output

and It works, Also to add Start-BitsTransfer works with other websites

so may be problem can be with maxmind and Start-BitsTransfer

BITS is supposed to handle network interruptions. Invoke-WebRequest can work, but will fail on minor interruptions. BITS is superior for larger downloads.

But yeah, its a good temporary solution. I'll figure it out sooner or later.

[palinka]

The plot thickens... I received an email from MaxMind:

Code: Select all

Dear MaxMind Customer,

Your account has reached the daily limit for database downloads. Any additional download attempts today from your account will fail. Customers are limited to 2,000 downloads of GeoIP databases per day.

To avoid download errors we recommend that you limit your downloads of each database to no more than once per day per server. You can log in to your account and check your GeoIP download history for information on IP addresses you are downloading databases from.

To ensure that you are downloading your databases efficiently, you may consult the update schedule for GeoIP databases. If you have any questions, please contact us at support@maxmind.com.

 Sincerely,
The Team at MaxMind

I definitely did not run this script 2k times for sure. But I logged in at MaxMind and it shows 2k downloads. BITS must have been running all night retrying and retrying. Testing is finished until tomorrow.

[RvdH]

The plot thickens... I received an email from MaxMind:

Code: Select all

Dear MaxMind Customer,

Your account has reached the daily limit for database downloads. Any additional download attempts today from your account will fail. Customers are limited to 2,000 downloads of GeoIP databases per day.

To avoid download errors we recommend that you limit your downloads of each database to no more than once per day per server. You can log in to your account and check your GeoIP download history for information on IP addresses you are downloading databases from.

To ensure that you are downloading your databases efficiently, you may consult the update schedule for GeoIP databases. If you have any questions, please contact us at support@maxmind.com.

 Sincerely,
The Team at MaxMind

I definitely did not run this script 2k times for sure. But I logged in at MaxMind and it shows 2k downloads. BITS must have been running all night retrying and retrying. Testing is finished until tomorrow.

I'm nowhere near 2000k, eg:

Date Download
Mar 1, 2023 38
Mar 2, 2023 78
Mar 3, 2023 3
Total 119

Invoke-WebRequest works

Code: Select all

Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip -ErrorAction Stop

Note, file is updated: GeoLite2-Country-CSV_20230303.zip

[palinka]

I'm nowhere near 2000k, eg:

I know. I was testing some other code and that's apparently what triggered it.

Anyway, I changed the download block to this:

Code: Select all

<#	Download latest GeoLite2 data  #>
Debug "----------------------------"
Debug "Downloading MaxMind data"
$Timer = Get-Date
$URL = "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-" + $Type + "-CSV&license_key=" + $LicenseKey + "&suffix=zip"
$Try = 1
Do {
	$Success = $False
	Try {
		Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip -Asynchronous -ErrorAction Stop
		If (Test-Path $DownloadedZip) {
			Debug "MaxMind data successfully downloaded in $(ElapsedTime $Timer)"
			Email "[OK] MaxMind data downloaded"
			$Success = $True
			Break
		} Else {
			If ($Try -le 10) {
				Debug "Failed to download MaxMind zip file on try $Try - trying again"
			} Else {
				Throw "Tried 10 times to download MaxMind zip file - giving up"
			}
		}
	}
	Catch {
		Debug "[ERROR] : Unable to download MaxMind data : $($Error[0])"
		Debug "[ERROR] : Quitting Script"
		Email "[ERROR] Failed to download MaxMind data. See error log."
		EmailResults
		Exit
	}
	$Try++
} Until ($Success -eq $True)

The reason I used BitsTransfer is because it will keep trying if there is a hiccup in the transmission. InvokeWebRequest will just timeout and die if there are any issues with transmission.

[palinka]

Here's what I ended up with, which I think is the best error handling I can come up with.

Code: Select all

<#	Download latest GeoLite2 data  #>
Debug "----------------------------"
Debug "Downloading MaxMind data"
$Timer = Get-Date
$URL = "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-" + $Type + "-CSV&license_key=" + $LicenseKey + "&suffix=zip"
$Try = 1
Do {
	Try {
		Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip
		If (Test-Path $DownloadedZip) {
			Debug "MaxMind data successfully downloaded in $(ElapsedTime $Timer)"
			Email "[OK] MaxMind data downloaded"
			Break
		} Else {
			Throw "MaxMind zip file could not be found after apparently successful download"
		}
	}
	Catch {
		Debug "[ERROR] : Unable to download MaxMind data on try # $Try"
		Debug "[ERROR] : Error Message : $($Error[0])"
		If ($Try -le 9) {
			Debug "Trying again in 10 seconds"
			Start-Sleep -Seconds 10
		} Else {
			Debug "Tried 10 times to download MaxMind zip file - giving up"
			Debug "[ERROR] : Quitting Script"
			Email "[ERROR] Failed to download MaxMind data. See error log."
			EmailResults
			Exit
		}
	}
	$Try++
} Until ($Try -gt 10)

I tried this with multiple phoney and real URLs. It seems to work as expected. If there's an error, it tries 10 times with 10 seconds in between before giving up.

I updated github already.

As to BitsTransfer, I tested with multiple files from random sites and it works just fine. I'm convinced MaxMind changed something that causes the error. Again - BitsTransfer is always preferrable because it handles transmission errors very well. You don't need to jump through hoops to make it work - except for this one site on the entire internet: MaxMind.com. I finally gave up.