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
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/
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
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..
From my reading of the code, it's because the SQL IPPool module does multiple queries *outside* of a transaction. The SQL code in rlm_sql does one transaction per query, but that's not what you want. Alan DeKok.
participants (3)
-
Alan DeKok -
Dave -
Peter Nixon