Jump to content

larry29936

Members
  • Content Count

    111
  • Joined

  • Last visited

Community Reputation

0 Neutral

About larry29936

  • Rank
    Advanced Member

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. @Barand, it's showing most of my IPV6 ranges as overlapping Showing rows 0 - 499 (49184 total, Query took 2.0331 seconds.)
  2. @Barand, I'm getting a sql error when I run your code: SELECT * -> FROM ( -> SELECT CASE WHEN start <= @xend AND end >= @xstart -> THEN id -> ELSE NULL -> END as found -> , @xstart := start as start -> , @xend := end as end -> FROM ip_lookup -> JOIN (select @xstart := null, @xend := null) init -> WHERE id > 3000000 -> ) search -> WHERE found IS NOT NULL; Here's the error: MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-> FROM ( -> SELECT CASE WHEN start <= @xend AND end >= @xstart ' at line 2 Happens both local and on host server.
  3. @Barand, cycling through 5 million pairs of start and end ip's in the lookup table is a chore. I can't think of a way to automate the check for overlapping or duplicate ranges. Do you have any suggestions?
  4. @Barand, I used the original ip_lookup.csv to populate the ip_lookup table and the start and end ip fields are in dot notation for IPv4 ip's and the IPv6 ip's look correct with the start ip's ending with :: and the end ip's ending with :f or :ff. The problem area for me is the download table. I've emptied the original table and modified it to match yours. I exported the download table from the website and tried to import it into the login_time, address, and filename fields into the modified table but I keep getting the following error: " ERROR 1242 (21000): Subquery returns more than 1 row" when I use the following in mysql shell: LOAD DATA LOCAL INFILE '/home/larry/web/sql/download.csv' INTO TABLE download FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id,log_time, address, filename); I get the same error if I try to import via phpMyadmin. It appears to be coming from the trigger: CREATE TRIGGER `ip_address_and_refer` BEFORE INSERT ON `download` FOR EACH ROW BEGIN SET NEW.ip_address = inet6_aton(NEW.address), NEW.refer = (SELECT id FROM lookup i WHERE NEW.ip_address BETWEEN i.start AND i.end ); END Do I need to add a "limit 1" to the trigger?
  5. @gizmola, thanks for getting back to me.
  6. @Barand, before I start I want to be sure about something. In your create table for ip_lookup you have both start and end set as varbinary(16). Will that handle full IPv6 addresses?
  7. @Barand, the ip_lookup table is 5,200,000 rows, around half of them are IPv6 ip's and includes location data. The ip's will have to import into varbinary(40) fields instead of varbinary(16) fields so I can work with them. The download table currently has about 2000 rows. There's nothing I can do about previously entered IPv6 ip's in the download table but there aren't that many, less than 100. I think it will take forever to reconfigure everything and import the data. I have nothing but time but it will be pretty boring to do everything on my local server then have to export everything to my web host as well as modify my php code to handle the changes. I guess I'll be busy for the next few days. Thanks for the help.
  8. I’m trying to create a trigger that does two things, first take an ip that’s in dot notation and run inet_aton on it and put the result in another field. Second, checks a lookup table to identify an ip range that the result of the first action falls into and enters the id of that row in the table. This is on a mysql database on my web host. Here’s what I’ve tried: DELIMITER // CREATE TRIGGER before_ins_download BEFORE INSERT ON download FOR EACH ROW begin set new.ip_address = inet_aton(new.`ADDRESS`), new.refer=(select `id` from `ip_lookup` as i where new.ip_address between i.start and i.end limit 1 ); END; // DELIMITER ; It works without the new.refer part but after I added that it just seems to hang and never gets to the closing DELIMITER; . All ip columns are indexed. Does anyone see a mistake in my code? Thanks for looking.
  9. @Barand, Thanks for taking the time and effort. Like you, I'm not familiar with working with IPv6 ip's. Time for me to do more research. At my age, I've got nothing but time. I think the problem is that I'm not getting the full IPv6 ip so they won't convert.
  10. UPDATE: Emptied table and imported download.csv. Still have problem of "Incorrect string value".
  11. @Barand, I dropped the download table and recreated it then imported the data. Still getting the "ERROR 1411 (HY000): Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet6_aton". SET start_ip = inet6_aton(start_ip) , end_ip = inet6_aton(end_ip); worked for the ip_lookup table so I tried the same thing on the download table except it was a .sql file, not a .csv.
  12. Thanks for the reminder, I've been up working on this for the last 17 hours. I guess I should put it down until tomorrow when my brain can function properly again. Thank you for all your help. Have a good evening.
  13. I'm just importing a .csv that's been gzipped thru phpMyadmin. The csv is more than 5 million rows of data. I guess I could import it into a temporary table then run code to get it into the proper format in the ip_lookup table. I should probably do the same for the download table and re-write the code for inserting rows in it. What data type do you think I should use to import the ip's into the temporary tables? It's apparent that varbinary isn't going to work. Seeing as the ip's are strings in the import files, would a varchar(16) work?
  14. @Barand, now I'm getting that "Incorrect string value" error on the ip_lookup table instead of the download table. ☹️ Could malformed IPv6 ip's cause this? I hope I don't need to empty the table and re-import the .csv because then I have to run code to replace the country codes with country names and then might not fix the problem. mysql> UPDATE download d -> INNER JOIN -> ip_lookup on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip)) -> SET d.refer = ip_lookup.id -> WHERE d.FILENAME is not null; ERROR 1411 (HY000): Incorrect string value: '`test`.`ip_lookup`.`end_ip`' for function inet6_aton mysql>
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.