Problem with MySQL and Accouting-On record

6 Nov 2020


      Hi,

	I have a radius server (freeradius 3.0.19) to authenticate a VPN server 
(among other services). I have virtual server to listen from this 
device. In this server I have the config:

accouting {
    ...
    sql_log_um
    ...
}

	where "sql_log_um" es a detail config to a file, like this:

detail sql_log_um {
   filename = ${radacctdir}/eduroam/eduroam_sql
   escape_filenames = no
   permissions = 0600
   header = "%t"
   locking = false
   suppress {
     User-Password
   }
}

	Then I have enabled the buffered-sql listening to the previous file and 
with:

accouting {
   sql
}

	to write accouting packets to a mysql database.

	My problem is that whenever I reboot this VPN server, it sends an 
Accouting-On request which is written to this file like:

Fri Nov  6 13:44:04 2020
	Acct-Status-Type = Accounting-On
	NAS-Identifier = "MikroTik-CCR-VPN-1-Test"
	Acct-Delay-Time = 0
	NAS-IP-Address = 192.168.1.100
	X-Atica-Service = "vpn"
	X-Atica-Service-Filter = "vpn"
	Timestamp = 1604666644

	and then the buffered-sql runs a query like:

UPDATE radacct
SET acctstoptime = FROM_UNIXTIME(1604663265),
     acctsessiontime = '1604663265' - UNIX_TIMESTAMP(acctstarttime),
     acctterminatecause = 'NAS-Reboot'
WHERE acctstoptime IS NULL AND
       nasipaddress = '155.54.213.6' AND
       acctstarttime <= FROM_UNIXTIME(1604663265)

	the problem I have is that is queried is run but the record is not 
deleted from the detail file neither is marked with the Donestamp mark, 
so buffered-sql runs it again and again, without passing to next records.

	My first hypothesis has been that this query lasts too match (this is a 
very large database), so I have changed the query (just to try) to 
something like:

UPDATE radacct
SET acctstoptime = FROM_UNIXTIME(1604663265),
     acctsessiontime = '1604663265' - UNIX_TIMESTAMP(acctstarttime),
     acctterminatecause = 'NAS-Reboot'
WHERE 1 = 0

	just to try and check that the problem is not this, but I'm getting the 
same result.

	Any help? Thanks.

-- 
Angel L. Mateo Martínez
Sección de Telemática
Área de Tecnologías de la Información
y las Comunicaciones Aplicadas (ATICA)
http://www.um.es/atica
Tfo: 868889150
Fax: 868888337

Problem with MySQL and Accouting-On record

Ángel L. Mateo