Need help with mysql "before insert" trigger

larry29936 · May 17, 2020

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 · May 17, 2020

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 · May 17, 2020

@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 · May 17, 2020

@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 · May 17, 2020

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 · May 17, 2020

@gizmola, thanks for getting back to me.

gizmola · May 18, 2020

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 · May 18, 2020

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 · May 18, 2020

@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 · May 18, 2020

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 · May 18, 2020

@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 · May 18, 2020

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 · May 20, 2020

@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 · May 20, 2020

It's a copy from the CLI screen

image.png.af2601b4c3452d8089ceec3ed7ac992c.png

Lose the "->""s

larry29936 · May 20, 2020

DUH, how stupid of me!

larry29936 · May 20, 2020

@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 · May 20, 2020

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.

Sign In

Need help with mysql "before insert" trigger

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information