HOW-TO Import the MaxMind GeoIP Free Country CSV file into MySQL and save diskspace - errata - MASTER-MASTER

...filed under miscellaneous

Vicent de Lau's good howto (http://vincent.delau.net/php/geoip.html) has a small (or should I say tiny) error in it IF YOU ran MASTER-MASTER with auto_increment_increment < 1, TINYINT in the create cc table needs to change to SMALLINT.

When you try to do:

INSERT INTO cc SELECT DISTINCT NULL,cc,cn FROM csv;

You will get:

ERROR 1062 (23000) at line 1: Duplicate entry '255' for key 1

This is because the:
CREATE TABLE cc (
ci TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
cc CHAR(2) NOT NULL,
cn VARCHAR(50) NOT NULL
);
CREATE TABLE ip (
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
ci TINYINT UNSIGNED NOT NULL
);


With MASTER-MASTER has to be:
CREATE TABLE cc (
ci SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
cc CHAR(2) NOT NULL,
cn VARCHAR(50) NOT NULL
);
CREATE TABLE ip (
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
ci SMALLINT UNSIGNED NOT NULL
);