[raddetine]

Good and Helpful tutorial, Thank You!

[ScIrUsna]

How bans work?

[DetoNater]

i am using table import in mysql workbench since i hate command line, when i click the table import wizard, the files .csv or .json only is asked but how can i upload a .sql file(the first one we've prepared) into the ipv4 table ??

[Konstantinos]

I didn't know this thread existed, good thing someone commented on it.

About a week ago, I decided to try out my own version and came across a really interesting article which is way faster than a range check (scanning through only 1 row instead of 40k+) but the table however is 4 times bigger - super creative method nevertheless.

I find out that for me, LOAD DATA INFILE is the easiest and fastest way to update it and the whole process takes 1-2 minutes (along with downloading the file, extracting and moving to a certain directory due to secure_file_priv option).

[DetoNater]

i cant query the complete file into the database, actually i am able to upload file sizes high as 200mb and the one we prepared is around ~4.5mb so upload is not an issue for me in pma, but after sometime mysql says query executed .......(but not complete) once again i see the ipv4 table... it is empty with no data in it..

[Konstantinos]

Extract the .zip and go to pma. Select database, SQL tab and execute:

pawn Код:

LOAD DATA LOCAL INFILE '/path_to_file/GeoIPCountryWhois.csv' INTO TABLE list_ipv4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (@ip_from_string,@ip_to_string,range_start,range_end,country_code,@country_name);

It took about 8 seconds to insert 144,865 rows.

[DetoNater]

Quote:

Originally Posted by Konstantinos Extract the .zip and go to pma. Select database, SQL tab and execute: pawn Код: LOAD DATA LOCAL INFILE '/path_to_file/GeoIPCountryWhois.csv' INTO TABLE list_ipv4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (@ip_from_string,@ip_to_string,range_start,range_end,country_code,@country_name); It took about 8 seconds to insert 144,865 rows.

the one which we have prepared and i have it in .sql extension()...... or the default maxmind's which is downloaded without modifying as .csv

[Konstantinos]

Quote:

Originally Posted by DetoNater the one which we have prepared and i have it in .sql extension()...... or the default maxmind's which is downloaded without modifying as .csv

The default .csv file.

[DetoNater]

MySQL said: Documentation

#1148 - The used command is not allowed with this MySQL version

[X337]

You have to enable that first, check this.

[Evocator]

Anyone importing an updated .csv file from maxmind note that they added new geo codes:

• HM which refers to a mountain in the Australian territory -
  https://en.wikipedia.org/wiki/Heard_...Donald_Islands
• EH Western Sahara - https://en.wikipedia.org/wiki/Western_Sahara

So either add these to the ISO 3166 list or remove them from the list_ipv4 INSERT statement before running the query.

[Vince]

I had already added the EH code to the Pastebin file a few weeks ago but as of yet there don't seem to be any IP blocks associated with the HM code (at least not two weeks ago when I re-imported the IP data). Thanks for the heads up, though!

[eSPeZet]

Every single step worked like a charm, except the importing..
When I import a few lines (as a test) with the sql console, then it works fine..but i can not do that with 168k lines..So i had to take MySQL Workbench (connected to my Host who has Linux) and i am getting this error, when I try to import:

ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (samp.list_ipv4, CONSTRAINT fk_ipv4_countrycode FOREIGN KEY (country_code) REFERENCES list_countries (country_code))
Operation failed with exitcode 1