0

I am using Freeradius AAA server on Ubuntu 18 server which uses mySQL ver 5.7.27 as DB to store entries. INNODB is being used. It have a table called radacct which stores users accounting /session data. To make radacct table slim and fit, a scheduled bash script runs every minute which copies all rows whose acctstoptime column is NOT NULL. (means there session is closed), from radacct table to another table , after copying it deletes these entries from radacct table. This part is working fine.

But is there any way I can use any method like mysql trigger to copy and then delete entries in real time as soon the acctstoptime gets update?

Example as soon as the acctstoptime updates with time (means not null) then it should copy immediately to another table and then delete this entry?

I tried below mysql TRIGGER

BEGIN
SET @usractstop = (SELECT count(*) from radacct where username=New.username and acctstoptime is NOT NULL);
IF (@usractstop = 1) THEN
SET @usractstopid  = (SELECT radacctid from radacct where username=New.username and acctstoptime is NOT NULL);
insert into radacct_archive select * from radacct where radacctid = @usractstopid;
DELETE FROM radacct WHERE radacctid = @usractstopid;
END IF;
END

copy part is working, but on delete its showing error

ERROR 1442 (HY000): Can’t update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any suggestions/advice ? Some more info on db and table

SHOW TABLE STATUS WHERE Name = 'radacct';"
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| radacct | InnoDB |      10 | Dynamic    |    1 |          16384 |       16384 |               0 |       212992 |         0 |        2323234 | 2019-11-05 19:06:21 | 2019-11-07 10:27:08 | NULL       | latin1_swedish_ci |     NULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+

RADACCT TABLE:
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| radacctid           | bigint(21)       | NO   | PRI | NULL    | auto_increment |
| acctsessionid       | varchar(64)      | NO   | MUL |         |                |
| acctuniqueid        | varchar(32)      | NO   | UNI |         |                |
| username            | varchar(64)      | NO   | MUL |         |                |
| realm               | varchar(64)      | YES  |     |         |                |
| nasipaddress        | varchar(15)      | NO   | MUL |         |                |
| nasportid           | varchar(15)      | YES  |     | NULL    |                |
| nasporttype         | varchar(32)      | YES  |     | NULL    |                |
| acctstarttime       | datetime         | YES  | MUL | NULL    |                |
| acctupdatetime      | datetime         | YES  |     | NULL    |                |
| acctstoptime        | datetime         | YES  | MUL | NULL    |                |
| acctinterval        | int(12)          | YES  | MUL | NULL    |                |
| acctsessiontime     | int(12) unsigned | 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    |                |

+---------+----------------------------------------------------------------------
SHOW CREATE TABLE RADACCT;
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
| radacct | CREATE TABLE `radacct` (
  `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
  `acctsessionid` varchar(64) NOT NULL DEFAULT '',
  `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
  `username` varchar(64) NOT NULL DEFAULT '',
  `realm` varchar(64) DEFAULT '',
  `nasipaddress` varchar(15) NOT NULL DEFAULT '',
  `nasportid` varchar(15) DEFAULT NULL,
  `nasporttype` varchar(32) DEFAULT NULL,
  `acctstarttime` datetime DEFAULT NULL,
  `acctupdatetime` datetime DEFAULT NULL,
  `acctstoptime` datetime DEFAULT NULL,
  `acctinterval` int(12) DEFAULT NULL,
  `acctsessiontime` int(12) unsigned DEFAULT NULL,
  `acctauthentic` varchar(32) DEFAULT NULL,
  `connectinfo_start` varchar(50) DEFAULT NULL,
  `connectinfo_stop` varchar(50) DEFAULT NULL,
  `acctinputoctets` bigint(20) DEFAULT NULL,
  `acctoutputoctets` bigint(20) DEFAULT NULL,
  `calledstationid` varchar(50) NOT NULL DEFAULT '',
  `callingstationid` varchar(50) NOT NULL DEFAULT '',
  `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
  `servicetype` varchar(32) DEFAULT NULL,
  `framedprotocol` varchar(32) DEFAULT NULL,
  `framedipaddress` varchar(15) NOT NULL DEFAULT '',
  `framedipv6address` varchar(45) NOT NULL DEFAULT '',
  `framedipv6prefix` varchar(45) NOT NULL DEFAULT '',
  `framedinterfaceid` varchar(44) NOT NULL DEFAULT '',
  `delegatedipv6prefix` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`radacctid`),
  UNIQUE KEY `acctuniqueid` (`acctuniqueid`),
  KEY `username` (`username`),
  KEY `framedipaddress` (`framedipaddress`),
  KEY `framedipv6address` (`framedipv6address`),
  KEY `framedipv6prefix` (`framedipv6prefix`),
  KEY `framedinterfaceid` (`framedinterfaceid`),
  KEY `delegatedipv6prefix` (`delegatedipv6prefix`),
  KEY `acctsessionid` (`acctsessionid`),
  KEY `acctsessiontime` (`acctsessiontime`),
  KEY `acctstarttime` (`acctstarttime`),
  KEY `acctinterval` (`acctinterval`),
  KEY `acctstoptime` (`acctstoptime`),
  KEY `nasipaddress` (`nasipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=2323234 DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)
5
  • Trigger cannot perform this action. Show table's DDL (or at least specify its engine).
    – Akina
    Nov 7 '19 at 5:53
  • Does you may affect on the queries which uses this table's data (alter queries texts)?
    – Akina
    Nov 7 '19 at 5:54
  • I have updated the question with more details for table/engine type. What is the workaround to achieve this task in real time?
    – iBBi
    Nov 7 '19 at 6:29
  • Show create table is more informative then show table status. Please add or replace. What is the workaround to achieve this task in real time? InnoDB... hmm... Think about partitioning by acctstoptime IS NULL. After such partitioning you may add WHERE ... AND acctstoptime IS NULL into your queries, and they will not access the partition which stores the info about closed sessions. Closing session with setting acctstoptime will automatically move the record from one partition to another.
    – Akina
    Nov 7 '19 at 6:56
  • ok i have updated show create table as well.
    – iBBi
    Nov 7 '19 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.