sqlippool duplicated IP assignments

[Antonio Modesto Amaral Sousa]

On Fri 06 Dec 2013 02:59:54 PM BRST, Alan DeKok wrote:
> Antonio Modesto Amaral Sousa wrote:
>> I'm using freeradius-2.1.12_1 with MySQL 5.1 and sqlippool configured to
>> assign IP's to my PPPoE clients, I redistribute these routes with iBGP,
>> until yesterday it was working fine, the problem started after a
>> blackout that happened last night.
>
>    When a blackout happens, the PPPoE server SHOULD tell the RADIUS
> server that all of the sessions have been dropped.  If it doesn't, the
> RADIUS server has no idea the sessions have been dropped.
>
>> I have a lot of clients that are
>> being assigned duplicated IP addresses and I just don't know why,
>
>    That shouldn't happen, even in the above scenario.  It should be
> assigned new IPs, not IPs which are already assigned.
>
>    Or, maybe the PPPoE server told FreeRADIUS that the sessions were all
> dropped... then let the users keep the old IP addresses.
>
>> I've
>> configured a long lease time for each assignment so that the NAS has a
>> good time frame to transmit the interim updates, my radippool table is
>> using the engine InnoDB and I think the select lock should be working
>> fine. Any suggestions?
>
>    Blame the PPPoE server for everything.  FreeRADIUS assumes that IP's are:
>
> - assigned when it says to assign them
>
> - NOT assigned to other users
>
> - timed out when Session-Timeout = 0
>
> - closed when the PPPoE server / NAS reboots
>
>
>    FreeRADIUS also assumes that it gets told when something happens with
> the user session.  If the PPPoE server isn't doing the right thing, and
> isn't telling FreeRADIUS what it's doing... FreeRADIUS can do nothing.


Hello Alan,

I noticed something in the sqlippool config:

## 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 = '%{control:Pool-Name}' AND (expiry_time < NOW() OR 
expiry_time IS NULL) \
# 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, i
# ## use this query instead
 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
  WHERE pool_name = '%{control:Pool-Name}' \
  AND expiry_time IS NULL \
  ORDER BY RAND() \
  LIMIT 1 \
  FOR UPDATE"


The first allocate-find method assumes that the IP is free if the 
expiry_time is null, or if it's in the past, but the second one just 
check if the expiry time is null, isn't it wrong? Can I insert the 
other condition? because I noticed that I have some records here that 
weren't updated and are with the expiry_time in the past, probably 
because the NAS didn't told radius that...

>
>    Alan DeKok.
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

--