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/ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html