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@trimbletl.com> Para: "FreeRadius users mailing list" <freeradius-users@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@trimbletl.com> Para: "FreeRadius users mailing list" <freeradius-users@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