Need help with mysql "before insert" trigger

[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]

I suspect it may be your use of inet_aton() and trying to match that against varbinary(16) but I don't know how your tables are currently defined or how the llokup start and end values were set (Mine were set using inet6_aton() to handlw both IPv4 and IPv6).

My tables:

CREATE TABLE `download` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `address` varchar(40) DEFAULT NULL,
  `ip_address` varbinary(16) DEFAULT NULL,
  `ref` int(25) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(No data yet)

CREATE TABLE `ip_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` varbinary(16) DEFAULT NULL,
  `end` varbinary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+----+----------+----------+--------------+--------------+
| id | start    | end      | ip_start     | ip_end       |
+----+----------+----------+--------------+--------------+
|  1 | FF010101 | FF320117 | 255.1.1.1    | 255.50.1.23  |
|  2 | FF320118 | FF320164 | 255.50.1.24  | 255.50.1.100 |
|  3 | FF320165 | FF3201C8 | 255.50.1.101 | 255.50.1.200 |
|  4 | FF3201C9 | FF3204FF | 255.50.1.201 | 255.50.4.255 |
+----+----------+----------+--------------+--------------+

My trigger:

CREATE TRIGGER `download_BEFORE_INSERT` BEFORE INSERT ON `download` 
FOR EACH ROW
BEGIN
    SET NEW.ip_address = inet6_aton(NEW.address),
        NEW.ref = (SELECT id FROM ip_lookup i WHERE NEW.ip_address BETWEEN i.start AND i.end );
END

Add download data

INSERT INTO download (address) VALUES 
('255.50.1.20'), 
('255.50.1.165'), 
('255.50.1.100'), 
('255.50.1.222');

View results: (as you can see - the trigger worked)

SELECT d.id
     , d.address
     , hex(d.ip_address) as hex_address
     , d.ref
     , inet6_ntoa(i.start) as lookup_start
     , inet6_ntoa(i.end)   as look_up_end
FROM download d
     JOIN
     ip_lookup i ON d.ref = i.id;
+----+--------------+-------------+------+--------------+--------------+
| id | address      | hex_address | ref  | lookup_start | look_up_end  |
+----+--------------+-------------+------+--------------+--------------+
|  1 | 255.50.1.20  | FF320114    |    1 | 255.1.1.1    | 255.50.1.23  |
|  3 | 255.50.1.100 | FF320164    |    2 | 255.50.1.24  | 255.50.1.100 |
|  2 | 255.50.1.165 | FF3201A5    |    3 | 255.50.1.101 | 255.50.1.200 |
|  4 | 255.50.1.222 | FF3201DE    |    4 | 255.50.1.201 | 255.50.4.255 |
+----+--------------+-------------+------+--------------+--------------+

 

[larry29936]

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

Edited May 17, 2020 by larry29936

[larry29936]

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

[gizmola]

1 hour ago, larry29936 said:

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

Yes. 

[larry29936]

@gizmola, thanks for getting back to me.

[gizmola]

10 hours ago, larry29936 said:

@gizmola, thanks for getting back to me.

No worries.  I've been storing ipv4 and ipv6 addresses using varbinary(16) for quite a while, and it works well.

[Barand]

14 hours ago, larry29936 said:

The ip's will have to import into varbinary(40) fields instead of varbinary(16) fields so I can work with them

You seem to be confusing your types.

The string version of an IPv6 address ( xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx )requires varchar(40). Well, 39 to be precise. When converted to a numeric value with inet6_aton() then the output is varbinary(16).

[larry29936]

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

[Barand]

That will treat the symptom but the cure would be to fix the ip_lookup records that have the overlapping start and end values.

[larry29936]

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

[Barand]

I had to check my table in 2 halves (id < 3,000,000 and id > 3,000,000) otherwise it timed out.

mysql> SELECT count(*) FROM ip_lookup;
+----------+
| count(*) |
+----------+
|  5852250 |
+----------+
1 row in set (10.15 sec)

mysql> 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;
+---------+-------+------+
| found   | start | end  |
+---------+-------+------+
| 5000003 | ┌ 0n  | ┌ 0ê |
+---------+-------+------+
1 row in set (27.00 sec)

SELECT id
     , inet6_ntoa(start) as startA
     , inet6_ntoa(end) as endA
FROM ip_lookup a 
WHERE id BETWEEN 5000001 AND 5000005;
+---------+----------------+----------------+
| id      | startA         | endA           |
+---------+----------------+----------------+
| 5000001 | 218.255.48.86  | 218.255.48.102 |
| 5000002 | 218.255.48.103 | 218.255.48.119 |   <<  overlapping
| 5000003 | 218.255.48.110 | 218.255.48.136 |   <<
| 5000004 | 218.255.48.137 | 218.255.48.153 |
| 5000005 | 218.255.48.154 | 218.255.48.170 |
+---------+----------------+----------------+

 

[larry29936]

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

Edited May 20, 2020 by larry29936

[Barand]

It's a copy from the CLI screen

Lose the "->""s

[larry29936]

DUH, how stupid of me!

[larry29936]

@Barand, it's showing most of my IPV6 ranges as overlapping

 

 Showing rows 0 - 499 (49184 total, Query took 2.0331 seconds.)

Edited May 20, 2020 by larry29936

[Barand]

I guess it's true: "Test data is that data for which the query works", so mine only has the one overlap I put in deliberately to see if I could find it.