I thought I had this problem licked, but I still suffer from it. Anyone know why FR 2.1.3 with sqlippool (mysql) might decide to hand out the same IP more than once while its processing more than one request at a time? Wed Jan 14 22:05:59 2009 : Info: Allocated IP: 75.119.xxx,211 from IP-Pool (did jarvis cli 00:18:D2:00:3E:63 port 449756 user pcollyer) Wed Jan 14 22:05:59 2009 : Info: Allocated IP: 75.119.xxx.211 from IP-Pool (did jarvis cli 00:18:D2:00:5B:0D port 449755 user jhogeterp) Wed Jan 14 22:05:59 2009 : Info: Allocated IP: 75.119.xxx.211 from IP-Pool (did jarvis cli 00:18:D2:00:2E:C1 port 449752 user mellerpoultry) The requests all came in at the same time, to the second (among others), its like FR took 3 requests and looked at the database at the exact same time, saw it was an available IP and all those 3 requests assigned it. My NAS rejects two of the 3 because the IP is assigned, and Freeradius clears the IP from the ip-pool to be later distributed (even though its still in use) Im open to any suggestions. Its hard to debug!
The requests all came in at the same time, to the second (among others), its like FR took 3 requests and looked at the database at the exact same time, saw it was an available IP and all those 3 requests assigned it.
That can't be avoided. SELECT (allocate-find) will always work much faster than UPDATE (allocate-update).
My NAS rejects two of the 3 because the IP is assigned,
I think that you make a good point here. If the allocate-update query was made to fail in the case that the IP address was already issued to another thread between allocate-find and allocate-update (by expanding it with AND expiry_time IS NULL in WHERE), point of failure will be in sqlippool module and not on the NAS. Logic can then perhaps try to issue a new IP address (best just once more in order not to create a loop). That way issuing same IP address to multiple threads can be handled by the sqlippool module. Ivan Kalik Kalik Informatika ISP
Can't you do the select and update as part of one transaction? For example with MySQL: START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; The transaction may need to be changed to serializable as well. I don't know how the DHCP RFC handles preallocations. You could probably use Repeatable Read level if it is acceptable to mark an address as taken when a DHCPOFFER is sent while waiting for a DHCPREQUEST from the client, so long as the address if confirmed to be free before the DHCPACK is sent, or a DHCPNAK in the case that the address was offered to multiple clients. Ben Wiechman From: freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.org [mailto:freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.or g] On Behalf Of Padam J Singh Sent: Thursday, January 15, 2009 9:39 AM To: FreeRadius users mailing list Subject: Re: Handing out duplicate IP addresses Hello Ivan, Would adding a mutex around the select-update code in the sqlippool module solve this issue? Padam tnt@kalik.net wrote: The requests all came in at the same time, to the second (among others), its like FR took 3 requests and looked at the database at the exact same time, saw it was an available IP and all those 3 requests assigned it. That can't be avoided. SELECT (allocate-find) will always work much faster than UPDATE (allocate-update). My NAS rejects two of the 3 because the IP is assigned, I think that you make a good point here. If the allocate-update query was made to fail in the case that the IP address was already issued to another thread between allocate-find and allocate-update (by expanding it with AND expiry_time IS NULL in WHERE), point of failure will be in sqlippool module and not on the NAS. Logic can then perhaps try to issue a new IP address (best just once more in order not to create a loop). That way issuing same IP address to multiple threads can be handled by the sqlippool module. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- PGP Id 9EED2E09
I dont know if this means anything, but Im not using DHCP, Im using PPPoE. I didnt know FR could even be a dhcp server. Im just throwing out ideas, but is there a way to not thread the sqlippool module ? Let one request at a time for an IP, sure it would be slower, but for my use would be fine. Ben Wiechman wrote:
Can’t you do the select and update as part of one transaction?
For example with MySQL:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
The transaction may need to be changed to serializable as well. I don’t know how the DHCP RFC handles preallocations.
You could probably use Repeatable Read level if it is acceptable to mark an address as taken when a DHCPOFFER is sent while waiting for a DHCPREQUEST from the client, so long as the address if confirmed to be free before the DHCPACK is sent, or a DHCPNAK in the case that the address was offered to multiple clients.
Ben Wiechman
*From:* freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.org [mailto:freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.org] *On Behalf Of *Padam J Singh *Sent:* Thursday, January 15, 2009 9:39 AM *To:* FreeRadius users mailing list *Subject:* Re: Handing out duplicate IP addresses
Hello Ivan,
Would adding a mutex around the select-update code in the sqlippool module solve this issue?
Padam
tnt@kalik.net <mailto:tnt@kalik.net> wrote:
The requests all came in at the same time, to the second (among others),
its like FR took 3 requests and looked at the database at the exact same
time, saw it was an available IP and all those 3 requests assigned it.
That can't be avoided. SELECT (allocate-find) will always work much faster than UPDATE (allocate-update).
My NAS rejects two of the 3 because the IP is assigned,
I think that you make a good point here. If the allocate-update query was made to fail in the case that the IP address was already issued to another thread between allocate-find and allocate-update (by expanding it with AND expiry_time IS NULL in WHERE), point of failure will be in sqlippool module and not on the NAS. Logic can then perhaps try to issue a new IP address (best just once more in order not to create a loop). That way issuing same IP address to multiple threads can be handled by the sqlippool module.
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- PGP Id 9EED2E09 ------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Change this in ippool.conf: 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'" AND expiry_time IS NULL ^^^^^^^^^^^^^^^ this is new That should cause module to fail if another thread has updated wth this IP address while update was being executed. Ivan Kalik Kalik Informatika ISP Dana 15/1/2009, "Dave" <dave@optionsdsl.ca> piše:
I dont know if this means anything, but Im not using DHCP, Im using PPPoE. I didnt know FR could even be a dhcp server.
Im just throwing out ideas, but is there a way to not thread the sqlippool module ? Let one request at a time for an IP, sure it would be slower, but for my use would be fine.
Ben Wiechman wrote:
Can�t you do the select and update as part of one transaction?
For example with MySQL:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
The transaction may need to be changed to serializable as well. I don�t know how the DHCP RFC handles preallocations.
You could probably use Repeatable Read level if it is acceptable to mark an address as taken when a DHCPOFFER is sent while waiting for a DHCPREQUEST from the client, so long as the address if confirmed to be free before the DHCPACK is sent, or a DHCPNAK in the case that the address was offered to multiple clients.
Ben Wiechman
*From:* freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.org [mailto:freeradius-users-bounces+ben=wisper-wireless.com@lists.freeradius.org] *On Behalf Of *Padam J Singh *Sent:* Thursday, January 15, 2009 9:39 AM *To:* FreeRadius users mailing list *Subject:* Re: Handing out duplicate IP addresses
Hello Ivan,
Would adding a mutex around the select-update code in the sqlippool module solve this issue?
Padam
tnt@kalik.net <mailto:tnt@kalik.net> wrote:
The requests all came in at the same time, to the second (among others),
its like FR took 3 requests and looked at the database at the exact same
time, saw it was an available IP and all those 3 requests assigned it.
That can't be avoided. SELECT (allocate-find) will always work much faster than UPDATE (allocate-update).
My NAS rejects two of the 3 because the IP is assigned,
I think that you make a good point here. If the allocate-update query was made to fail in the case that the IP address was already issued to another thread between allocate-find and allocate-update (by expanding it with AND expiry_time IS NULL in WHERE), point of failure will be in sqlippool module and not on the NAS. Logic can then perhaps try to issue a new IP address (best just once more in order not to create a loop). That way issuing same IP address to multiple threads can be handled by the sqlippool module.
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- PGP Id 9EED2E09 ------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Can't you do the select and update as part of one transaction?
For example with MySQL:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
AFAIK it is the transaction. Problem is that update will work for all three users. Adding expiry_time IS NULL to update will cause it to work only for the first user. Ivan Kalik Kalik Informatika ISP
AFAIK it is the transaction. Problem is that update will work for all three users. Adding expiry_time IS NULL to update will cause it to work only for the first user.
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Can I ask what happens when the module fails ? My guess is it will still return an access-accept with all other attributes accumulated minus the "Framed-Ip-Address" ? Id rather it return a big fat reject, and let the NAS resend a new request?
My guess is it will still return an access-accept with all other attributes accumulated minus the "Framed-Ip-Address" ?
Id rather it return a big fat reject, and let the NAS resend a new request?
You can configure more than one sqlippool instance. Then put as many as you like in a fail-over (redundant) section. If first one fails server will try the second etc. Ivan Kalik Kalik Informatika ISP
Dave wrote:
Can I ask what happens when the module fails ?
If the module is allocated two IP addresses to the same user, it is failing in a big way. My reading of the code is that it *intends* to do the queries && allocations as part of one SQL transaction. It appears, though, that something else is preventing it from working correctly. Alan DeKok.
Can I ask what happens when the module fails ?
If the module is allocated two IP addresses to the same user, it is failing in a big way.
It's the other way around. Not two IPs to the same user but same IP to multiple users. It's a threading issue. When multiple threads ask for an IP at the same time it is possible for same IP to be issued to different users. That's because allocate-find works 10 or more times faster than allocate-update. There is a chance that several allocate-finds will complete before first allocate-update makes the IP unavailable. That's not such a problem. The point of failure is then on the NAS. It will make one connection with that IP and refuse the others. The problem is that first allocate-update will allocate IP to userA. Second one will change the allocation to userB. Third one to userC etc. Now, chances are that NAS will create the connection for the one that completed *first* (userA) an refuse the others. So NAS will have IP allocated to userA, and radius database to userB (or C or whichever was *last*). When that last user tries to connect again radius will release the IP as stale entry for that user. Now the NAS will have this IP allocated to userA while radius database will show it as free. I think that best thing to do is to prevent subsequent updates by altering the allocate-update (adding "AND expiry_time IS NULL" at the end should do it). That way only first one will update the row while others will fail (update 0 rows). It should be possible for logic to detect that no rows were updated and fail the module. Dave would like a more drastic solution - to reject the user in this case. I think that's a bit OTT. You can always reject users using unlang if some module fails. And if it just fails you can do some fail-over/redundant stuff. Ivan Kalik Kalik Informatika ISP
I made this modification to the ippool.conf and still testing it, I have not seen the issue yet again but I havent loaded the server down. I made some other modifications to the server and database to try to make it move as fast possible but removing non-used modules, database indexing, other stuff. I will keep an eye on it, but so far its already working better than it has in years.
I think that best thing to do is to prevent subsequent updates by altering the allocate-update (adding "AND expiry_time IS NULL" at the end should do it). That way only first one will update the row while others will fail (update 0 rows). It should be possible for logic to detect that no rows were updated and fail the module.
tnt@kalik.net wrote:
When multiple threads ask for an IP at the same time it is possible for same IP to be issued to different users. That's because allocate-find works 10 or more times faster than allocate-update. There is a chance that several allocate-finds will complete before first allocate-update makes the IP unavailable.
But the whole thing *should* be wrapped in a transactional block. See the source code to rlm_sqlippool. If mysql doesn't respect transactional boundaries, that's another issue.
I think that best thing to do is to prevent subsequent updates by altering the allocate-update (adding "AND expiry_time IS NULL" at the end should do it). That way only first one will update the row while others will fail (update 0 rows). It should be possible for logic to detect that no rows were updated and fail the module.
OK. I've added that to the default MySQL query. I'd like to know if this is a problem for Postgresql, too. Alan DeKok.
When multiple threads ask for an IP at the same time it is possible for same IP to be issued to different users. That's because allocate-find works 10 or more times faster than allocate-update. There is a chance that several allocate-finds will complete before first allocate-update makes the IP unavailable.
But the whole thing *should* be wrapped in a transactional block. See the source code to rlm_sqlippool.
If mysql doesn't respect transactional boundaries, that's another issue.
Found this: problem (not unlike here, regarding DHCP implementation): "> > > Unfortunately, the mysql docs tell me that it is not currently
possible > to modify a table and select from the same table in a
subquery.
My worry > is that by splitting the above into two queries, I'll run into issues > with concurrent clients attempting randomization at the same
time,
> getting the same randomizationID, and one or more randomizations getting > overwritten. Would putting the two queries into a transaction prevent > the selected row from being read by other clients? "
answer "> I would also re-iterate that transactions alone are not a solution to
Steve's problem. Without the proper isolation level, the data returned by subsequent select/update calls could be no better than not using transactions at all. Of course, it's possible that the default isolation level will work for this use case, but this needs to be verified."
solution :-D "> You have to use a real database for that (e.g. PostgreSQL -- where
they're supported in the form of save points) ;)"
Ivan Kalik Kalik Informatika ISP
participants (5)
-
Alan DeKok -
Ben Wiechman -
Dave -
Padam J Singh -
tnt＠kalik.net