IP Allocation FAILED : Best practice for inserting new IPs into mysql

[Sophie Loewenthal]

Hi again!

 >Why are you using...instead of the one that is commented out?
I inherited this implementation from a team that had left before I 
arrived. There has been some customisation for integration with our 
inhouse application, but for now I've not finished the puzzle!  I had 
not touched Radius before.

This was last edited in 2013,
-rw-r----- 1 root radiusd  6330 Oct 10  2013 ippool.conf

All users are authenticating correctly, and most received an IP.

However, a minority were allocated a free IP by radius, but the GGSN 
rejected them because another of our devices had had this allocated 
earlier. But I see no match in radipool for this, but I did see an 
accounting record in radacct containing a start time but no stop time 
dated a week earlier.  Very strange.

Message from GGSN that Vodafone kindly found :
Disconnect Reason: conflict-in-ip-addr-assignment

Kind regards,
Sophie

On 11/6/2015 9:10 PM, Rod Elias wrote:
> Hi there!
>
> I read your ippool.conf file.
> I don't know if there's something related, but did you change that file?
>
> Why are you using
>
>   allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>    WHERE pool_name = '%{control:Pool-Name}' \
>    AND username = '%{User-Name}' LIMIT 1"
>
>
> instead of the one that is commented out?
>
>
> # 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"
>
>
> If it helps, check it out the contents of my ippool.conf here: https://gist.github.com/anonymous/ca4e6e6c4f1503441802
> I'm using radius version 2.2.2
> In my case, I prefer not to allocate a random IP address every time.
>
> Despite that problem, are your users authenticating (ie. receiving dynamic IP) normally?
>
> Thanks!
>
>
> ----- Mensagem original -----
> De: "Sophie Loewenthal" <sophie.loewenthal at trimbletl.com>
> Para: "FreeRadius users mailing list" <freeradius-users at lists.freeradius.org>
> Enviadas: Sexta-feira, 6 de Novembro de 2015 16:57:12
> Assunto: Re: IP Allocation FAILED : Best practice for inserting new IPs into mysql
>
> Hi Rob,
>
>       Please find file attached in full and a comments stripped version
> below.  Radius version is 2.1.12-1.
>
> Kind regards,
> Sophie
>
> freeradius-mysql-2.1.12-1.el6.x86_64
> freeradius-utils-2.1.12-1.el6.x86_64
> freeradius-2.1.12-1.el6.x86_64
>
> grep -v ^# ./sql/mysql/ippool.conf |grep -v ^$
> allocate-clear = "UPDATE ${ippool_table} /* allocate-clear */ \
>    SET nasipaddress = '', pool_key = 0, \
>    callingstationid = '', calledstationid = '', \
>    expiry_time = NULL, 3GPP_Imsi = '' \
>    WHERE pool_key = '${pool-key}'"
>    allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
>     WHERE pool_name = '%{control:Pool-Name}' \
>     AND username = '%{User-Name}' LIMIT 1"
> pool-check = "SELECT id FROM ${ippool_table} /* pool-check */ \
>    WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
> allocate-update = "UPDATE ${ippool_table} /* allocate-update */ \
>    SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
>    callingstationid = '%{Calling-Station-Id}', \
>    expiry_time = NOW() + INTERVAL ${lease-duration} SECOND, \
>    3GPP_Imsi = '%{3GPP-IMSI}', calledstationid = '%{Called-Station-Id}' \
>    WHERE framedipaddress = '%I' AND username = '%{User-Name}'"
> start-update = "UPDATE ${ippool_table} /* start-update */ \
>    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}'"
> stop-clear = "UPDATE ${ippool_table} /* stop-clear */ \
>    SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>    expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
>    WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
>    AND username = '%{User-Name}' \
>    AND callingstationid = '%{Calling-Station-Id}' \
>    AND framedipaddress = '%{Framed-IP-Address}'"
> alive-update = "UPDATE ${ippool_table} /* alive-update */ \
>    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}'"
> on-clear = "UPDATE ${ippool_table} /* on-clear */ \
>    SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>    expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
>    WHERE nasipaddress = '%{Nas-IP-Address}'"
> off-clear = "UPDATE ${ippool_table} /* off-clear */ \
>    SET nasipaddress = '', pool_key = 0, callingstationid = '', \
>    expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
>    WHERE nasipaddress = '%{Nas-IP-Address}'"
>
>
>
> On 11/6/2015 7:32 PM, Rod Elias wrote:
>> Hi again!
>>
>> Which radius version are you using?
>>
>>
>> Also, could you please post your ippool.conf file?
>>
>> Thanks!
>>
>>
>> ----- Mensagem original -----
>> De: "Sophie Loewenthal" <sophie.loewenthal at trimbletl.com>
>> Para: "FreeRadius users mailing list" <freeradius-users at lists.freeradius.org>
>> Enviadas: Sexta-feira, 6 de Novembro de 2015 12:55:43
>> Assunto: Re: IP Allocation FAILED : Best practice for inserting new IPs into mysql
>>
>> Hi Rod,
>>
>>        Yes this really did help me. Thanks for the idea for checking and
>> alerting via cron ( maybe put into Zabbix )
>>
>> Interestingly this has not run out of space. So I don't know why I see
>> messages like "[sqlippool] pool appears to be full"
>>
>> +-------------------+--------+-----------+
>> | pool_name         | total  | allocated |
>> +-------------------+--------+-----------+
>> | proximus-carli    | 130047 |      2767 |
>> | proximus-mechelen | 130047 |         1 |
>> | vodafone          |  32510 |     23192 |
>> | vodafone-m2m      | 130048 |      NULL |
>> +-------------------+--------+-----------+
>>
>>
>> Kind regards,
>> Sophie
>>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>