[Tutorial] GeoIP and handling rangebans with MySQL
#21

Good and Helpful tutorial, Thank You!
Reply
#22

How bans work?
Reply
#23

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 ??
Reply
#24

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).
Reply
#25

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..
Reply
#26

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.
Reply
#27

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
Reply
#28

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.
Reply
#29

MySQL said: Documentation

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

You have to enable that first, check this.
Reply
#31

Anyone importing an updated .csv file from maxmind note that they added new geo codes: So either add these to the ISO 3166 list or remove them from the list_ipv4 INSERT statement before running the query.
Reply
#32

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!
Reply
#33

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
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)