SQLippool problems (duplicate IPs handed out).

[Dave]

Peter Nixon wrote:

I downloaded and installed ver 2.0.0 and have been fighting with it for
hours.  I was going to move to the rlm_ippool module to fix this, but
using this module or the sqlippool module regardless of what is set in
radgroupreply or radreply I always get the error
rlm_ippool: Could not find Pool-Name attribute.
rlm_sqlippool: Could not find Pool-Name attrubute

I have it set in radgroupreply or radreply as Pool-Name := pool2 for example

I havent really changed much except make the config mods to make 2.0
work with mysql

> 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
>>     
>
>
>
>