MySQL import and use with Maxmind GeoIP database

Today I was working on GeoIP database functionality to get a user’s country code by his IP address. There are several IP databases on the internet, but I chose the use the GeoLite country database by Maxmind.

I started downloading the CSV file which requires an IP start and IP end column that I needed for converting a IP address to a country code.

After downloading and inspecting the data of the CSV file I created a table with 6 columns and inserted the CSV file with help op PHPMyAdmin. When the data was imported successfully my table looked like this:

mysql maxmind geoip table

With my table filled with Geo data, I was able to test the results by firing the following query:

SELECT * FROM wp_geoip WHERE '{ip-address}' BETWEEN begin_ip_num AND end_ip_num;

This gave the following results:

maxmind ip query wrong results

My test IP address was an address from Amsterdam, Netherlands and the results where 2 rows; one United kingdom and one Netherlands. I knew this was not the result which I wanted so I started searching the internet and posted a question on Stackoverflow.

I soon found out that using a VARCHAR as type was not the best option for the IP start and end columns. Multiple sources (including Stack) explained that an “unsigned INT” was the best option, because VARCHAR will do the following thing:

An IP that starts with 92.11[…] will also be between the range in the first record. For the first records it checks for any string between 92.0 and 92.3

By changing the column settings to unsigned INT alone the problem was not fixed. I needed to convert the IPv4 (dotted) addresses into a proper address, this doing by INET_ATON() MySQL built-in functionality.

The problem now was that I before uploaded a CSV file with help of PHPMyAdmin, where I could not change anything of the data by built-in mysql functionality. So I removed my table, created a table with help of this script found on Github and changed the types of the ip_start and ip_end columns to INT UNSIGNED.

CREATE TABLE IF NOT EXISTS `Maxmind_geoIP` (
`id` INT(1) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, -- the id just for numeric
`maxmind_ipstart`INT(10) UNSIGNED NOT NULL, -- the ip start from maxmind data
`maxmind_ipend` INT(10) UNSIGNED NOT NULL, -- the ip end of maxmind data
`maxmind_locid_start` INT(1) UNSIGNED ZEROFILL NOT NULL, -- the start of maxmind location id
`maxmind_locid_end` INT(1) UNSIGNED ZEROFILL NOT NULL, -- the end of maxmind location id
`maxmind_country_code` VARCHAR(4) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country code
`maxmind_country` VARCHAR(100) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country name

PRIMARY KEY( `id`,`maxmind_ipstart`,`maxmind_ipend`, `maxmind_locid_end`, `maxmind_country` )

) DEFAULT CHARSET=UTF8 COLLATE=UTF8_GENERAL_CI AUTO_INCREMENT=1 ;

And inserted the CSV file with load data infile (Use load data local infile if you are using a local machine):

LOAD DATA INFILE '.../GeoIPCountryWhois.csv' INTO TABLE `Maxmind_geoIP` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(
`maxmind_ipstart` , `maxmind_ipend` , `maxmind_locid_start` , `maxmind_locid_end` , `maxmind_country_code` , `maxmind_country`
);[/code]
Now I created a table again with another name, so that I was able to insert (by <a href="http://www.w3schools.com/sql/sql_insert_into_select.asp" target="_blank">INSERT SELECT</a>) the data from the first table into the second table with the INET_ATON() function like this:
INSERT INTO second_table_name (ip_start, ip_end, locid_start, locid_end, country_code, country)
SELECT INET_ATON(maxmind_ipstart), INET_ATON(maxmind_ipend), maxmind_locid_start, maxmind_locid_end, maxmind_country_code, maxmind_county
FROM Maxmind_geoIP;

After this done the table is accessible for doing queries like:

SELECT * FROM `second_table_name` WHERE INET_ATON('ip-address') BETWEEN ip_start AND ip_end