reject mysql insert query in which particular column is zero (0)

Question

Server version: 5.6, memory 16G,8 core processors

I have a table "radacct" which is updated by network routers. the routers send insert query every minute randomly. example of insert query

INSERT INTO radacct(acctsessionid,acctuniqueid,username,realm,nasipaddress,
nasportid,nasporttype,acctstarttime,acctstoptime,acctsessiontime,  acctauthentic, 
connectinfo_start, connectinfo_stop, acctinputoctets,  acctoutputoctets,
calledstationid,callingstationid, acctterminatecause, servicetype, framedprotocol, 
framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) 
VALUES ('25', 'bv4545ae155701','batmanuser', '', '192.168.1.100', '991990188', 
'Ethernet', '2016-08-25 19:15:40', NULL, '0','RADIUS', '','', '0', '0', '', 
'55:6t:33:xy:59:9o', '','Framed-User', 'PPP', '10.57.16.0','0', '0', '');

Table schema:

+----------------------+-------------+------+-----+---------+----------------+
| Field                | Type        | Null | Key | Default | Extra          |
+----------------------+-------------+------+-----+---------+----------------+
| radacctid            | bigint(21)  | NO   | PRI | NULL    | auto_increment |
| acctsessionid        | varchar(32) | NO   | MUL |         |                |
| acctuniqueid         | varchar(32) | NO   | MUL |         |                |
| username             | varchar(64) | NO   | MUL |         |                |
| groupname            | varchar(64) | NO   |     |         |                |
| realm                | varchar(64) | YES  |     |         |                |
| nasipaddress         | varchar(15) | NO   | MUL |         |                |
| nasportid            | varchar(15) | YES  |     | NULL    |                |
| nasporttype          | varchar(32) | YES  |     | NULL    |                |
| acctstarttime        | datetime    | YES  | MUL | NULL    |                |
| acctstoptime         | datetime    | YES  | MUL | NULL    |                |
| acctsessiontime      | int(12)     | YES  | MUL | NULL    |                |
| acctauthentic        | varchar(32) | YES  |     | NULL    |                |
| connectinfo_start    | varchar(50) | YES  |     | NULL    |                |
| connectinfo_stop     | varchar(50) | YES  |     | NULL    |                |
| acctinputoctets      | bigint(20)  | YES  |     | NULL    |                |
| acctoutputoctets     | bigint(20)  | YES  |     | NULL    |                |
| calledstationid      | varchar(50) | NO   |     |         |                |
| callingstationid     | varchar(50) | NO   |     |         |                |
| acctterminatecause   | varchar(32) | NO   |     |         |                |
| servicetype          | varchar(32) | YES  |     | NULL    |                |
| framedprotocol       | varchar(32) | YES  |     | NULL    |                |
| framedipaddress      | varchar(15) | NO   | MUL |         |                |
| acctstartdelay       | int(12)     | YES  |     | NULL    |                |
| acctstopdelay        | int(12)     | YES  |     | NULL    |                |
| xascendsessionsvrkey | varchar(10) | YES  |     | NULL    |                |
+----------------------+-------------+------+-----+---------+----------------+

the issue is that table field "acctinputoctets" and "acctoutputoctets" having "0" is no use to us and accumulated rows having 0 values takes large amount of space on server. I have no control over the insert query. How do i reject such insert queries ?

Update

Before insert trigger

mysql> delimiter $$
mysql> create trigger blockZero before insert on radacct
    -> for each row
    -> begin
    -> if new.val < 1 then
    -> signal sqlstate '45000';
    -> end if;
    -> end;$$
Query OK, 0 rows affected (0.01 sec)

reject the insert queries with 0,0 entries for the two fields in radacct table — sherpaurgen, Sep 01 '16 at 05:35
check constraints don't work. Insert triggers cant say "never mind" — Drew, Sep 01 '16 at 05:36
The best you can do is in the insert trigger (the after insert), to take the id (the AI) and write it to another table. Then an Event sweep can clean them up with deletes — Drew, Sep 01 '16 at 05:37
Maybe this post is helpful to you: http://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions — Carsten Massmann, Sep 01 '16 at 05:49

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Create Before insert trigger

mysql> delimiter $$
mysql> create trigger blockZero before insert on radacct
    -> for each row
    -> begin
    -> if new.acctinputoctets  < 1 then 
    -> signal sqlstate '45000';
    -> end if;
    -> end;$$
Query OK, 0 rows affected (0.01 sec)

https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 01 '16 at 11:25

sherpaurgen

3,028
6
32
45

reject mysql insert query in which particular column is zero (0)

Update

1 Answers1

Create Before insert trigger