Need help creating mysql trigger

[larry29936]

I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure:

  `ID` int(5) NOT NULL autoincrement,
  `LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(),
  `IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL,
  `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
    `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql shell using the following code:

UPDATE access t2, ip_lookup t1 SET t2.country = t1.country, t2.area = t1.area, t2.city = t1.city 
WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND  t2.FILENAME is not null and t2.country is null;

How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download?

Thanks in advance,

Larry

Edited May 3, 2020 by larry29936

[Barand]

Why do you feel this urge to duplicate the country, area,city data?

You only need it in the ip_lookup table. Join the two tables in a query whenever you want that info for access table records.

[larry29936]

@Barand, This query gives an error of "Operand should contain 1 column(s)" in mysql shell:

SELECT (download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city)
FROM download, ip_lookup
WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;

 

That's why I wanted all data in one table.

Edited May 3, 2020 by larry29936

[Barand]

Remove the ( ) from the SELECT clause.

download
+----+---------------------+------------+----------+
| ID | LOG_TIME            | IP_ADDRESS | FILENAME |
+----+---------------------+------------+----------+
|  1 | 2020-05-03 17:26:56 |         20 | NULL     |
|  2 | 2020-05-03 17:26:56 |        160 | a.txt    |
|  3 | 2020-05-03 17:26:56 |        205 | b.txt    |
+----+---------------------+------------+----------+

ip_lookup
+-------+----------+--------+---------+--------+------------+
| IP_ID | start_ip | end_ip | country | area   | city       |
+-------+----------+--------+---------+--------+------------+
|     1 |        1 |    100 | USA     | NY     | New York   |
|     2 |      101 |    200 | UK      | N West | Manchester |
|     3 |      201 |    300 | Spain   | North  | Barcelona  |
+-------+----------+--------+---------+--------+------------+

mysql> SELECT download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city
    -> FROM download, ip_lookup
    -> WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip;
+----------+---------+--------+------------+
| FILENAME | country | area   | city       |
+----------+---------+--------+------------+
| NULL     | USA     | NY     | New York   |
| a.txt    | UK      | N West | Manchester |
| b.txt    | Spain   | North  | Barcelona  |
+----------+---------+--------+------------+

 

Also, use explicit join syntax and not "FROM A,B WHERE ..."

SELECT download.FILENAME
     , ip_lookup.country
     , ip_lookup.area
     , ip_lookup.city
FROM download
     INNER JOIN
     ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip; 

 

[larry29936]

@Barand, ran the following  in mysql shell and it took 7 minutes to run:

SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city 
FROM download 
INNER JOIN ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip 
WHERE download.FILENAME is not null;

There are 5 million + records in the ip_lookup table so that's probably the hang up. That was one reason why I wanted all the information in the download table.
 

Edited May 3, 2020 by larry29936

[Barand]

Can you post the output from

SHOW CREATE TABLE ip_lookup;

and

SHOW CREATE TABLE download;

 

[larry29936]

@Barand, here's what you asked for:

download	CREATE TABLE `download` (
  `ID` int(5) NOT NULL AUTO_INCREMENT,
  `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IP_ADDRESS` int(64) unsigned NOT NULL,
  `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci	

 

ip_lookup	CREATE TABLE `ip_lookup` (
  `id` int(25) unsigned NOT NULL AUTO_INCREMENT,
  `start_ip` int(64) unsigned NOT NULL,
  `end_ip` int(64) unsigned NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_mysql500_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_ip` (`start_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=5277086 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci	

 

[Barand]

As a compromise, when you add a download record, get the id of the matching ip_lookup record and store that in the download record.

That way you aren't duplicating the contry/area/city data and you get extremely efficient joins.

SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city 
FROM download 
INNER JOIN ip_lookup ON download.ip_id = ip_lookup.id
WHERE download.FILENAME is not null

 

Edited May 4, 2020 by Barand