IP Allocation FAILED : Best practice for inserting new IPs into mysql
Sophie Loewenthal
sophie.loewenthal at trimbletl.com
Thu Nov 19 10:15:19 CET 2015
Hi,
An update from this thread started on 06-Nov-15,
I replaced the modified ,
allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
WHERE pool_name = '%{control:Pool-Name}' \
AND username = '%{User-Name}' LIMIT 1"
with the packaged version,
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"
restarted Radius and had messages like this,
Wed Nov 18 14:00:57 2015 : Auth: Login incorrect: [358279006099939]
(from client vodafone-nas port 1287820 cli 31611958234)
Wed Nov 18 14:00:58 2015 : Error: Discarding duplicate request from
client vodafone-nas port 28256 - ID: 192 due to unfinished request 13544
Wed Nov 18 14:00:58 2015 : Auth: Login OK: [000005322] (from client
vodafone-nas port 302486 cli 31655203712)
Wed Nov 18 14:00:58 2015 : Error: Discarding duplicate request from
client vodafone-nas port 25728 - ID: 58 due to unfinished request 13546
Wed Nov 18 14:00:58 2015 : Error: Discarding duplicate request from
client vodafone-nas port 29408 - ID: 88 due to unfinished request 13547
Wed Nov 18 14:00:58 2015 : Error: Discarding duplicate request from
client vodafone-nas port 24928 - ID: 201 due to unfinished request 13552
Wed Nov 18 14:00:58 2015 : Error: Discarding duplicate request from
client vodafone-nas port 24128 - ID: 33 due to unfinished request 13553
Wed Nov 18 14:00:58 2015 : Info: Allocated IP: 10.26.66.193 from
vodafone (did vodafone.trimbletl.com cli 31655269342 port 1106258 user
357466031473772)
SNIP
Wed Nov 18 14:12:43 2015 : Error: Discarding duplicate request from
client vodafone-nas port 27968 - ID: 160 due to unfinished request 16110
Wed Nov 18 14:12:43 2015 : Error: [sql] Couldn't update SQL accounting
STOP record - Deadlock found when trying to get lock; try restarting
transaction
Wed Nov 18 14:12:43 2015 : Error: rlm_sql_mysql: Cannot store result
Wed Nov 18 14:12:43 2015 : Error: rlm_sql_mysql: MySQL error 'Deadlock
found when trying to get lock; try restarting transaction'
Wed Nov 18 14:12:43 2015 : Error: Discarding duplicate request from
client vodafone-nas port 27792 - ID: 24 due to unfinished request 16113
Wed Nov 18 14:12:43 2015 : Error: Discarding duplicate request from
client vodafone-nas port 24576 - ID: 3 due to unfinished request 16114
Wed Nov 18 14:12:43 2015 : Info: Released IP 10.26.104.93 (did
vodafone.trimbletl.com cli 31646114654 user 91-000048cd)
Wed Nov 18 14:12:44 2015 : Info: Allocated IP: 10.26.0.120 from
vodafone (did vodafone.trimbletl.com cli 31621118394 port 1025809 user
60-0000383f)
Wed Nov 18 14:12:44 2015 : Info: Released IP 10.26.76.69 (did
vodafone.trimbletl.com cli 31655425301 user 0000065a2)
Wed Nov 18 14:12:44 2015 : Info: Released IP 10.26.78.252 (did
vodafone.trimbletl.com cli 31615836229 user 0000054f4)
Wed Nov 18 14:12:44 2015 : Info: Allocated IP: 10.26.18.84 from
vodafone (did vodafone.trimbletl.com cli 31655816408 port 988246 user
358279004474134)
When I rolled back the changes the duplicated request and deadlocking
type messages disappeared.
I shall try this again when I have truncated radius.radacct on the weekend.
Kind regards,
Sophie
On 06-Nov-15 9:52 PM, Sophie Loewenthal wrote:
> 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
>>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list