SQLippool problems (duplicate IPs handed out).

Dave dave at optionsdsl.ca
Mon Jan 14 01:41:25 CET 2008


I use the sql IP pool setup with mysql, and been using it fine for a
while, but I have a problem where if I have an influx of connections at
one time (30++)  That freeradius will hand out an IP to my NAS, but it
doesnt get written to the database fast enuff and another thread of
freeradius hands out the same IP to another user, and logs that entry to
the radippool table.  So I get users with duplicate IP addresses.. this
never happens if the connections are coming in slow enough, (1 or 2 at a
time). 

freeradius 1.1.6 with mysql 5.0.26. 

Here is my sqlippool.conf. 
----------------------------------------------------------------------------------------------------------------------------
sqlippool {

## SQL instance to use (from sql.conf)
sql-instance-name = "sql"

## Table to keep ippool info
ippool_table = "radippool"

## lease_duration. fix for lost acc-stop packets
lease-duration = 3600

## Attribute which should be considered unique per NAS
## Using NAS-Port gives behaviour similar to rlm_ippool.
Calling-Station-Id is for NAS that send fixed NAS-Port
 pool-key = "%{NAS-Port}"
# pool-key = "%{Calling-Station-Id}"

## Logging configuration.
sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port}
user %{User-Name})"

sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from
%{check:Pool-Name} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port}
user %{User-Name})"

sqlippool_log_clear = "Released IP %{Framed-IP-Address}\
(did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"

sqlippool_log_failed = "IP Allocation FAILED from %{check:Pool-Name} \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port}
user %{User-Name})"

sqlippool_log_nopool = "No Pool-Name defined \
 (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port}
user %{User-Name})"

# ## This series of queries allocates an IP address
# allocate-clear = "UPDATE ${ippool_table} \
#  SET nasipaddress = '', pool_key = 0, callingstationid = '', username
= '', \
#  expiry_time = '0000-00-00 00:00:00' \
#  WHERE pool_key = '${pool-key}'"

## This will clear all expired leases for lost acc-stop packets
allocate-clear = "UPDATE radippool \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username =
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE expiry_time <= NOW() - INTERVAL 1 SECOND"


# ## The ORDER BY clause of this query tries to allocate the same IP-address
# ## which user had last session...
 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
  WHERE pool_name = '%{check:Pool-Name}' AND expiry_time < NOW() \
  ORDER BY (username <> '%{User-Name}'), (callingstationid <>
'%{Calling-Station-Id}'), expiry_time \
  LIMIT 1 \
  FOR UPDATE"

## If you prefer to allocate a random IP address every time, use this
query instead
#allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
# WHERE pool_name = '%{check:Pool-Name}' AND expiry_time = '0000-00-00
00:00:00' \
# ORDER BY RAND() \
# LIMIT 1 \
# FOR UPDATE"


## If an IP could not be allocated, check to see if the pool exists or not
## This allows the module to differentiate between a full pool and no pool
## Note: If you are not running redundant pool modules this query may be
commented
## out to save running this query every time an ip is not allocated.
pool-check = "SELECT id FROM ${ippool_table} WHERE
pool_name='%{check:Pool-Name}' LIMIT 1"


allocate-update = "UPDATE ${ippool_table} \
 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
 callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
 expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE framedipaddress = '%I'"


## This series of queries frees an IP number when an accounting
## START record arrives
start-update = "UPDATE ${ippool_table} \
 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE nasipaddress = '%{NAS-IP-Address}' AND  pool_key = '${pool-key}'"

## This series of queries frees an IP number when an accounting
## STOP record arrives
stop-clear = "UPDATE ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username =
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}'
AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress =
'%{Framed-IP-Address}'"


## This series of queries frees an IP number when an accounting
## ALIVE record arrives
alive-update = "UPDATE ${ippool_table} \
 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}'
AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress =
'%{Framed-IP-Address}'"


## This series of queries frees the IP numbers allocate to a
## NAS when an accounting ON record arrives
on-clear = "UPDATE ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username =
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress =
'%{Framed-IP-Address}'"

## This series of queries frees the IP numbers allocate to a
## NAS when an accounting OFF record arrives
off-clear = "UPDATE ${ippool_table} \
 SET nasipaddress = '', pool_key = 0, callingstationid = '', username =
'', \
 expiry_time = '0000-00-00 00:00:00' \
 WHERE nasipaddress = '%{Nas-IP-Address}' AND username = '%{User-Name}' \
 AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress =
'%{Framed-IP-Address}'"

}
-----------------------------------------------------------------------------------------------------------------

Anyone have any ideas?

Thanks



More information about the Freeradius-Users mailing list