Jump to content
larry29936

Need help with mysql "before insert" trigger

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

It's a copy from the CLI screen

image.png.af2601b4c3452d8089ceec3ed7ac992c.png

Lose the "->""s

Share this post


Link to post
Share on other sites
Posted (edited)

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

 

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

Edited by larry29936

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.