larry29936

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.

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

UPDATE: Emptied table and imported download.csv. Still have problem of "Incorrect string value".

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

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.

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?

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

@Barand, I really appreciate your help. Your suggested code fails in both phpMyadmin and mysql shell. Results follow: mysql> UPDATE download d -> INNER JOIN -> d.ADDRESS 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 1146 (42S02): Table 'd.ADDRESS' doesn't exist I also tried it with UPDATE download as d, no luck.

Here's a sample of what the imported files look like: INSERT INTO `download` (`ID`, `LOG_TIME`, `ADDRESS`, `FILENAME`, `country`, `area`, `city`, `ip_address`, `refer`) VALUES (1, '2020-04-14 14:59:14', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL), (2, '2020-04-14 15:00:17', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL), (3, '2020-04-14 15:03:39', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL), (4, '2020-04-14 15:07:08', '72.168.144.48', 'foxclone35-02.iso', 'United States', 'California', 'Littlerock', 'Hš�0', NULL), (5, '2020-04-14 15:08:12', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL), (6, '2020-04-14 15:53:05', '204.187.14.75', NULL, NULL, NULL, NULL, 'Ì»�K', NULL), (7, '2020-04-14 15:53:13', '204.187.14.75', NULL, NULL, NULL, NULL, 'Ì»�K', NULL), (8, '2020-04-14 17:17:05', '83.47.231.251', NULL, NULL, NULL, NULL, 'S/çû', NULL), (9, '2020-04-14 17:17:32', '83.47.231.251', NULL, NULL, NULL, NULL, 'S/çû', NULL), IP_LOOKUP.csv sample data (the last 2 fields don't get imported) 1.0.0.0,1.0.0.255,OC,AU,Queensland,"South Brisbane",-27.4748,153.017 1.0.1.0,1.0.1.255,AS,CN,Fujian,Qingzhou,26.4837,117.925 1.0.2.0,1.0.3.255,AS,CN,Fujian,Fuzhou,26.1008,119.295 1.0.4.0,1.0.7.255,OC,AU,Victoria,"Narre Warren",-38.0267,145.301 1.0.8.0,1.0.15.255,AS,CN,Guangdong,Guangzhou,23.1322,113.267 1.0.16.0,1.0.31.255,AS,JP,Tokyo,Shinjuku,35.6978,139.706 1.0.32.0,1.0.63.255,AS,CN,Guangdong,"Guangzhou Shi",23.379,113.763 1.0.64.0,1.0.66.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 1.0.67.0,1.0.70.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45 1.0.71.0,1.0.75.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 1.0.76.0,1.0.76.255,AS,JP,Hiroshima,Miyajima,34.2991,132.322 1.0.77.0,1.0.77.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45 1.0.78.0,1.0.78.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 1.0.79.0,1.0.79.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45 1.0.80.0,1.0.82.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 1.0.83.0,1.0.83.255,AS,JP,Tottori,Kurayoshi,35.4333,133.817 1.0.84.0,1.0.84.255,AS,JP,Tottori,Yonago,35.4333,133.333 1.0.85.0,1.0.87.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 1.0.88.0,1.0.88.255,AS,JP,Shimane,Matsue,35.4833,133.05 1.0.89.0,1.0.90.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477 Latest table structures: CREATE TABLE `download` ( `ID` int(25) unsigned NOT NULL AUTOINCREMENT, `LOG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `ADDRESS` varbinary(16) NOT NULL, `FILENAME` varchar(60) CHARACTER SET utf8 COLLATE COLLATE=utf8_general_ci; DEFAULT NULL, `refer` int(25) UNSIGNED DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; CREATE TABLE `ip_lookup` ( `id` int(25) UNSIGNED NOT NULL AUTOINCREMENT `start_ip` varbinary(16) NOT NULL, `end_ip` varbinary(16) NOT NULL, `continent` varchar(2) COLLATE utf8_general_ci NOT NULL, `country` varchar(50) COLLATE utf8_general_ci NOT NULL, `area` varchar(50) COLLATE utf8_general_ci NOT NULL, `city` varchar(50) COLLATE utf8_general_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; Yes, the ip's really do display as xxx.xxx.xxx.xxx when browsing the tables in phpMyadmin.

All ip's are now varbinary(16). I emptied the two tables, made the changes from int to varbinary(16) and re-imported the data. All the ip's display in dot notation. inet6_aton should make getting the row id easy but I can't get it to work. Here's what I have: UPDATE download d INNER JOIN d.ADDRESS on download = ip_lookup.start_ip SET d.refer = ip_lookup.id WHERE (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip)) AND d.FILENAME is not null; I've always had a problem with joins, I just can't remember the proper format.

I know that I have 20 or so IPv6 ip's that won't convert because they were truncated on import.

@Barand, all ip's are varbinary(16)

@Barand, I'm going to use it to compare download.ADDRESS to the ip_start and ip_end in the ip_lookup table to get the row id to put into download table so I'm not copying the country, area, and city to the download table. I wish there was a way to create a view on multiple tables, it would be so much easier.

@Barand, my data was imported from a .sql file exported from phpMyadmin on my host's server. Every 500 rows or so it inserts another INSERT statement. Could this be the problem? I'm not seeing any of the insert statements in the imported data. I think I'm going to open the exported file in a spreadsheet and see if there are any stray characters then save as a csv and bring the data in via "LOAD DATA LOCAL INFILE".

When I run either the inet_aton or inet6_aton command on my local mysql server (Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)) I get the following error: mysql> UPDATE download SET ip_address = inet6_aton(ADDRESS); ERROR 1411 (HY000): Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet6_aton When I run the same command on my web host there's no problem. Both have exactly the same table structure and data. The data on the local server was imported from an export of the data on my web host. In both cases, the ADDRESS field is a varbinary(32). I've tried changing the local server to varbinary(16) and re-importing the data but got the same results. I've also restarted the mysql service but it made no difference. Can someone please explain what is going on?

@Barand, so should I re-import the csv ip's into varchar(32) fields or int(32) unsigned fields?

So, I should drop the table and recreate it with the ip fields as int(32) unsigned then re-import the .csv?

Everything I've seen has said to use varbinary fields for IPv6 ip's.

I just checked the csv and it's in xxx.xxx.xxx.xxx format. It's almost 5 million rows of data and the ip's were imported into varbinary(32) fields.

That's the way it imported into the table. I don't remember where I downloaded the .csv file from.

@benanamen - I'm comparing to another table has a start and end ip in that format in order to get the country, area, and city from. I also have to deal with IPv6 ip's.

@Barand, I need the ip in a "0x249f94bd7" format.

The code now reads: $test = inet_aton($ip); echo var_dump($test); $stmt = $pdo->prepare("UPDATE download SET ip_address = (?) WHERE ip_address is null;"); $stmt->execute($test) ; It errors at "$test = inet_aton($ip); I never see the var_dump()

How do I update a table and convert say, 66.249.75.215, from one column to another column as 0x249f94bd7. Both columns are varbinary. I'm using PDOConnect and this isn't working: $stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;"); $stmt->execute() ; I'm getting the following error: Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1411 Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet_aton in /home/larry/web/test/public_html/index.php:31 Stack trace: #0 /home/larry/web/test/public_html/index.php(31): PDOStatement->execute() #1 {main} thrown in /home/larry/web/test/public_html/index.php on line 31

No, the file was imported thru phpMysql import function as it was. I'm going to clear the table and re-import in the mysql shell using inet_aton