Re: SQL IP Pool maximum timeout.
Alan DeKok <aland@deployingradius.com>
Dave wrote:
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database,
That would seem to fit the 24-hour expiry time you set.
again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..?
No. The leases are set to expire after 24 hours, as you said.
If you don't want the leases to expire, edit the SQL queries so that they don't set or look for an expiry field.
I should probably leave this one to Peter to answer, but ... that wasn't my understanding of how the expiration works in sqlippool. The 'allocate-clear' query looks like this: allocate-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE pool_key = '${pool-key}'" Which, by my understanding, should only clear IP's for which we are seeing a REPEAT login on the same 'pool-key' (although I think it should probably add a test for the same NASIPAddress in the WHERE clause, I keep meaning to ask Peter about that). In other words, it should only be clearing IP's for which a 'stop' query has gone astray, on the basis that you can't have more than one connection to an individual NAS port. It certainly shouldn't just free up all IP's based on expiry_time. I did see one 'rogue' SQL file out there from a very early version of sqlippool which does seem to use expiry_time - not sure if it was an original, or a contributed version, but it was definitely broken. Dave - can you copy and paste your sqlippool.conf, so we can see what your actual queries look like? -- hugh
On Sun 08 Jul 2007, Hugh Messenger wrote:
Alan DeKok <aland@deployingradius.com>
Dave wrote:
Im still having trouble with this problem, I switched the pool key to NAS port, the expiry time is 24 hours, and it seems after 24 hours, it wipes all the existing entries from the database,
That would seem to fit the 24-hour expiry time you set.
again re-assigning IP's that are already in use, these IPs could be used indefinitely by some customers assuming they don't disconnect, I guess the sqlippool.conf seems to think that the stop packets are lost..?
No. The leases are set to expire after 24 hours, as you said.
If you don't want the leases to expire, edit the SQL queries so that they don't set or look for an expiry field.
I should probably leave this one to Peter to answer, but ... that wasn't my understanding of how the expiration works in sqlippool. The 'allocate-clear' query looks like this:
allocate-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE pool_key = '${pool-key}'"
Which, by my understanding, should only clear IP's for which we are seeing a REPEAT login on the same 'pool-key' (although I think it should probably add a test for the same NASIPAddress in the WHERE clause, I keep meaning to ask Peter about that).
Thats probably not a bad idea.
In other words, it should only be clearing IP's for which a 'stop' query has gone astray, on the basis that you can't have more than one connection to an individual NAS port. It certainly shouldn't just free up all IP's based on expiry_time.
Exactly..
I did see one 'rogue' SQL file out there from a very early version of sqlippool which does seem to use expiry_time - not sure if it was an original, or a contributed version, but it was definitely broken.
Yes. It is very difficult for us to support the various config files floating around there. If you are not running the conf file from latest cvs (preferably the code from cvs as well) then its difficult for us to help you. -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Peter Nixon wrote:
In other words, it should only be clearing IP's for which a 'stop' query has gone astray, on the basis that you can't have more than one connection to an individual NAS port. It certainly shouldn't just free up all IP's based on expiry_time.
Exactly..
Will it re-allocate IP's once the expiry time has passed? That seems to be the problem, I think. Alan DeKok.
On Mon 09 Jul 2007, Alan DeKok wrote:
Peter Nixon wrote:
In other words, it should only be clearing IP's for which a 'stop' query has gone astray, on the basis that you can't have more than one connection to an individual NAS port. It certainly shouldn't just free up all IP's based on expiry_time.
Exactly..
Will it re-allocate IP's once the expiry time has passed? That seems to be the problem, I think.
Yes. That IS the purpose of the expiry time :-) As long as you are getting accounting updates the expiry time continues to be extended. See: alive-update = "UPDATE ${ippool_table} \ SET expiry_time = 'now'::timestamp(0) + '${lease-duration} seconds'::interval \ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' AND username = '%{SQL-User-Name}' \ AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'" Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
-snip-
that wasn't my understanding of how the expiration works in sqlippool. The 'allocate-clear' query looks like this:
allocate-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE pool_key = '${pool-key}'"
Which, by my understanding, should only clear IP's for which we are seeing a REPEAT login on the same 'pool-key' (although I think it should probably add a test for the same NASIPAddress in the WHERE clause, I keep meaning to ask Peter about that).
Thats probably not a bad idea.
I take that back. It seems like a good idea, but that will break things for ISPs who have multiple NAS in failover or OSPF groups and therefore can happily assign the same IP to the same user even if they are connected to a different physical NAS. Cheers -- Peter Nixon http://peternixon.net/
On Wed 11 Jul 2007, Peter Nixon wrote:
-snip-
that wasn't my understanding of how the expiration works in sqlippool. The 'allocate-clear' query looks like this:
allocate-clear = "UPDATE radippool \ SET NASIPAddress = '', pool_key = 0, CallingStationId = '', \ expiry_time = NOW() - INTERVAL 1 SECOND \ WHERE pool_key = '${pool-key}'"
Which, by my understanding, should only clear IP's for which we are seeing a REPEAT login on the same 'pool-key' (although I think it should probably add a test for the same NASIPAddress in the WHERE clause, I keep meaning to ask Peter about that).
Thats probably not a bad idea.
I take that back. It seems like a good idea, but that will break things for ISPs who have multiple NAS in failover or OSPF groups and therefore can happily assign the same IP to the same user even if they are connected to a different physical NAS.
I changed my mind 10min after sending this reply. I have added a NASIPAddress check by default with a comment of why you may want to disable it in certain circumstances. Cheers -- Peter Nixon http://peternixon.net/
Peter Nixon said:
I take that back. It seems like a good idea, but that will break things for ISPs who have multiple NAS in failover or OSPF groups and therefore can happily assign the same IP to the same user even if they are connected to a different physical NAS.
I changed my mind 10min after sending this reply. I have added a NASIPAddress check by default with a comment of why you may want to disable it in certain circumstances.
FYI, I added this to my MySQL config, been running it live for a few days, works fine in my setup. I think this is the right way to go, making it the default, as the "certain circumstances" would tend to be the exception rather than the rule. I've also tested that "accounting on/off" change we discussed off-list, and it now correctly frees all relevant IP's after a NAS reboot.
Cheers
-- hugh
On Wed 11 Jul 2007, Hugh Messenger wrote:
Peter Nixon said:
I take that back. It seems like a good idea, but that will break things for ISPs who have multiple NAS in failover or OSPF groups and therefore can happily assign the same IP to the same user even if they are
connected
to a different physical NAS.
I changed my mind 10min after sending this reply. I have added a NASIPAddress check by default with a comment of why you may want to disable it in certain circumstances.
FYI, I added this to my MySQL config, been running it live for a few days, works fine in my setup. I think this is the right way to go, making it the default, as the "certain circumstances" would tend to be the exception rather than the rule.
I've also tested that "accounting on/off" change we discussed off-list, and it now correctly frees all relevant IP's after a NAS reboot.
Great. Looks like rlm_sqlippool is ready to take over the world :-) -- Peter Nixon http://peternixon.net/
Peter Nixon wrote:
Great. Looks like rlm_sqlippool is ready to take over the world :-)
My latest tests look promising. Stock clients work. No, there's no secret agenda. The agenda is public, but the implementation details are secret. Alan DeKok.
Its posted a few posts back in this thread, that version is still the one Im using.
Dave - can you copy and paste your sqlippool.conf, so we can see what your actual queries look like?
-- hugh
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Alan DeKok -
Dave -
Hugh Messenger -
Peter Nixon