[Tutorial] GeoIP and handling rangebans with MySQL

[Tutorial] GeoIP and handling rangebans with MySQL - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] GeoIP and handling rangebans with MySQL (/showthread.php?tid=585996)

Pages: 1 2

Re: GeoIP and handling rangebans with MySQL - raddetine - 13.04.2016

Good and Helpful tutorial, Thank You!

Re: GeoIP and handling rangebans with MySQL - ScIrUsna - 13.04.2016

How bans work?

Re: GeoIP and handling rangebans with MySQL - DetoNater - 21.11.2016

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 ??

Re: GeoIP and handling rangebans with MySQL - Konstantinos - 21.11.2016

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).

Re: GeoIP and handling rangebans with MySQL - DetoNater - 14.12.2016

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..

Re: GeoIP and handling rangebans with MySQL - Konstantinos - 14.12.2016

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.

Re: GeoIP and handling rangebans with MySQL - DetoNater - 14.12.2016

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

Re: GeoIP and handling rangebans with MySQL - Konstantinos - 14.12.2016

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.

Re: GeoIP and handling rangebans with MySQL - DetoNater - 14.12.2016

MySQL said: Documentation

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

Re: GeoIP and handling rangebans with MySQL - X337 - 14.12.2016

You have to enable that first, check this.

Re: GeoIP and handling rangebans with MySQL - Evocator - 10.09.2017

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.

Re: GeoIP and handling rangebans with MySQL - Vince - 10.09.2017

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!

Re: GeoIP and handling rangebans with MySQL - eSPeZet - 13.02.2018

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