SQLippool problems (duplicate IPs handed out).

[Peter Nixon]

Dave

This is quite possible, as I dont think the MySQL queries currently do the 
correct locking. If you can fix the problem, please send us a patch.

-Peter

On Mon 14 Jan 2008, Dave wrote:
> 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
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html



-- 

Peter Nixon
http://peternixon.net/