Duplicate SQL records versus unique constraints

[Christian Balzer]

Hello,

First of, been using freeradius for 5-6 years now and we are happy campers
thanks to it.

Freeradius 2.04 (Debian Lenny package), but I don't think this is
very version specific. 
Configuration is set for direct (sql, not sql_log) logging of accounting
records into mysql.

We've got a number of what can only be described as "maniac" customers
which fire off new sessions every minute or 2 and one of these has managed
to create over 2000 "identical" sessions, as far as  actsessionid and the
default generation of acctuniqueid are concerned. It seems that the NAS in
question is prone to recycle actsessionids under the onslaught of this
user (within days at times). We are talking to the upstream access provider
about this, but I don't think much of anything will come out of it.

In this setting the default key generation for the acctuniqueid of:
key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address,
NAS-Port" 
did little to stop the duplicates, since only the Acct-Session-Id is
variable (when not re-used by the NAS), the rest are more or less static. 
We changed the Client-IP-Address to Framed-IP-Address in the key
generation as this gives a much, much smaller chance of duplicates. 
But of course it does not guarantee uniqueness either.

In the analysis of this event it became clear that the sql module will
happily update any and all records that share the same acctuniqueid with a
new stop and session time, even if these were not NULL or 0 respectively.
Never mind that this whole scenario should not happen in the first place it
seems that overwriting valid data in an already "complete" accounting
record would be big no-no. Could anybody comment on this?

Given this behavior and the still existing (albeit vastly smaller) chance
of future duplicate records making it into the database I have a question:

If we add a CONSTRAINT to enforce uniqueness for acctuniqueid in the DB,
will the failure to insert an accounting record confuse the freerad sql
module and will those failures percolate up towards the radius protocol
level and thus result in the NAS keep on sending that accounting packet?

Regards,

Christian
-- 
Christian Balzer        Network/Systems Engineer                NOC
chibi at gol.com   	Global OnLine Japan/Fusion Network Services
http://www.gol.com/
https://secure3.gol.com/mod-pl/ols/index.cgi/?intr_id=F-2ECXvzcr6656