MySQL accounting stop query

7 Dec 2005

      I noticed that the stop query changed at some point between 0.9.3 and 
1.0.1.  Here are the queries:

## 0.9.3 ##
accounting_stop_query = "UPDATE ${acct_table2}
	SET
	AcctStopTime = '%S',
	AcctSessionTime = '%{Acct-Session-Time}',
	AcctInputOctets = '%{Acct-Input-Octets}',
	AcctOutputOctets = '%{Acct-Output-Octets}',
	AcctTerminateCause = '%{Acct-Terminate-Cause}',
	AcctStopDelay = '%{Acct-Delay-Time}',
	ConnectInfo_stop = '%{Connect-Info}'
	WHERE
	AcctSessionId = '%{Acct-Session-Id}' AND
	UserName = '%{SQL-User-Name}' AND
	NASIPAddress = '%{NAS-IP-Address}' AND
	AcctStopTime = 0"

## 1.0.1 (same as 1.0.5) ##
accounting_stop_query = "UPDATE ${acct_table2}
	SET
	AcctStopTime = '%S',
	AcctSessionTime = '%{Acct-Session-Time}',
	AcctInputOctets = '%{Acct-Input-Octets}',
	AcctOutputOctets = '%{Acct-Output-Octets}',
	AcctTerminateCause = '%{Acct-Terminate-Cause}',
	AcctStopDelay = '%{Acct-Delay-Time}',
	ConnectInfo_stop = '%{Connect-Info}'
	WHERE
	AcctSessionId = '%{Acct-Session-Id}' AND
	UserName = '%{SQL-User-Name}' AND
	NASIPAddress = '%{NAS-IP-Address}'"

Notice the last item in the WHERE clause is missing in the later 
version.  After looking at the data in my own radacct table, it looks 
like the change would cut down on duplicates, but at the cost of 
modifying rows that are potentially days or weeks old with new stoptime 
data.

If the NAS reuses SessionID for the same user 2 weeks after the initial 
use, then the user will end up with a 2 week long session and if 
sqlcounter is in use, will likely be shutoff.

It seems the 0.9.3 version is safer.  I don't see specific mention of 
the change in the ChangeLog.  Was there a reason for the change?

I am working out the details of a modification of my own.  At this point 
the WHERE clause looks like this (untested):

WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND
  	ABS((UNIXTIMESTAMP(AcctStartTime) -
	UNIXTIMESTAMP(DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} +
	%{Acct-Delay-Time:-0}) SECOND))) < 3600)"

Basically, it uses AcctUniqueId which is a hash of AcctSessionId (from 
the NAS), the NAS-IP, UserName, and Nas-Port and then makes sure the 
AcctStarttime already in the table is within an hour of the derrived 
time based on the stoptime and the session length.

NOTE: Those using MySQL 5.0+ can use TIMESTAMPDIFF instead of converting 
and subtracting.

I think this change should help reduce duplicate accounting entries.  It 
will incurr some added load for the date calc, but I'm hoping not too 
much given the AcctUniqueId should be indexed and have a low number of 
duplicates.

Any thoughts?

-- 
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com