IP Allocation FAILED : Best practice for inserting new IPs into mysql
Hi, Please find attached a debug of 62 lines. My ippool seems full: [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from proximus-carli (did proximus.trimbletl.com cli 32770XXXX port 29449 user 45486XXXXX) How could I check if the ippool had run out of IPs or was close to? I tried with these but clearly this was not good enough . Is there a better method of currently allocated addresses? mysql> select count(*) from radippool where username = ''; +----------+ | count(*) | +----------+ | 383727 | +----------+ and with, mysql> select count(*) from radippool where expiry_time is NULL ; +----------+ | count(*) | +----------+ | 396466 | +----------+ I am surprised this filled up, because we don't have enough devices to fill up our IP space. What could happen if NAS did not send back accounting stop packets? Can I add more IP addresses into radippol with FreeRADIUS running, or should I shut down FreeRADIUS during this insert? Kind regards, Sophie
Hi Sophie, Please see the following link: https://gist.github.com/chapeupreto/109abbdd7b20c977dd58 It has an SQL query that shows the number of total and allocated IP addresses for each pool that exist in radippool table. With that SQL query in mind, we defined a threshold value considering the total number of IP minus the number of allocated IP. This way, we have a script running in crontab that checks the result of that query and the threshold value and then triggers an e-mail and/or SMS warning about the running out of IP in the pools. As for your second question, yes you can add more IP addresses into radippool without the need to stop the server. Hope this helps you! Best regards, ----- Mensagem original ----- De: "Sophie Loewenthal" <sophie.loewenthal@trimbletl.com> Para: Freeradius-Users@lists.freeradius.org Enviadas: Sexta-feira, 6 de Novembro de 2015 8:41:09 Assunto: IP Allocation FAILED : Best practice for inserting new IPs into mysql Hi, Please find attached a debug of 62 lines. My ippool seems full: [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from proximus-carli (did proximus.trimbletl.com cli 32770XXXX port 29449 user 45486XXXXX) How could I check if the ippool had run out of IPs or was close to? I tried with these but clearly this was not good enough . Is there a better method of currently allocated addresses? mysql> select count(*) from radippool where username = ''; +----------+ | count(*) | +----------+ | 383727 | +----------+ and with, mysql> select count(*) from radippool where expiry_time is NULL ; +----------+ | count(*) | +----------+ | 396466 | +----------+ I am surprised this filled up, because we don't have enough devices to fill up our IP space. What could happen if NAS did not send back accounting stop packets? Can I add more IP addresses into radippol with FreeRADIUS running, or should I shut down FreeRADIUS during this insert? Kind regards, Sophie - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html --
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 -- Sophie Loewenthal System Engineer ITOPS / Trimble Transport & Logistics GSM:+32.471.900703 On 06-Nov-15 12:16 PM, Rod Elias wrote:
Hi Sophie,
Please see the following link: https://gist.github.com/chapeupreto/109abbdd7b20c977dd58 It has an SQL query that shows the number of total and allocated IP addresses for each pool that exist in radippool table.
With that SQL query in mind, we defined a threshold value considering the total number of IP minus the number of allocated IP. This way, we have a script running in crontab that checks the result of that query and the threshold value and then triggers an e-mail and/or SMS warning about the running out of IP in the pools.
As for your second question, yes you can add more IP addresses into radippool without the need to stop the server.
Hope this helps you!
Best regards,
----- Mensagem original ----- De: "Sophie Loewenthal" <sophie.loewenthal@trimbletl.com> Para: Freeradius-Users@lists.freeradius.org Enviadas: Sexta-feira, 6 de Novembro de 2015 8:41:09 Assunto: IP Allocation FAILED : Best practice for inserting new IPs into mysql
Hi,
Please find attached a debug of 62 lines.
My ippool seems full: [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from proximus-carli (did proximus.trimbletl.com cli 32770XXXX port 29449 user 45486XXXXX)
How could I check if the ippool had run out of IPs or was close to?
I tried with these but clearly this was not good enough . Is there a better method of currently allocated addresses? mysql> select count(*) from radippool where username = ''; +----------+ | count(*) | +----------+ | 383727 | +----------+
and with, mysql> select count(*) from radippool where expiry_time is NULL ; +----------+ | count(*) | +----------+ | 396466 | +----------+
I am surprised this filled up, because we don't have enough devices to fill up our IP space. What could happen if NAS did not send back accounting stop packets?
Can I add more IP addresses into radippol with FreeRADIUS running, or should I shut down FreeRADIUS during this insert?
Kind regards, Sophie
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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 -- Sophie Loewenthal System Engineer ITOPS / Trimble Transport & Logistics GSM:+32.471.900703 On 06-Nov-15 12:16 PM, Rod Elias wrote:
Hi Sophie,
Please see the following link: https://gist.github.com/chapeupreto/109abbdd7b20c977dd58 It has an SQL query that shows the number of total and allocated IP addresses for each pool that exist in radippool table.
With that SQL query in mind, we defined a threshold value considering the total number of IP minus the number of allocated IP. This way, we have a script running in crontab that checks the result of that query and the threshold value and then triggers an e-mail and/or SMS warning about the running out of IP in the pools.
As for your second question, yes you can add more IP addresses into radippool without the need to stop the server.
Hope this helps you!
Best regards,
----- Mensagem original ----- De: "Sophie Loewenthal" <sophie.loewenthal@trimbletl.com> Para: Freeradius-Users@lists.freeradius.org Enviadas: Sexta-feira, 6 de Novembro de 2015 8:41:09 Assunto: IP Allocation FAILED : Best practice for inserting new IPs into mysql
Hi,
Please find attached a debug of 62 lines.
My ippool seems full: [sqlippool] pool appears to be full [sqlippool] expand: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> IP Allocation FAILED from proximus-carli (did proximus.trimbletl.com cli 32770XXXX port 29449 user 45486XXXXX)
How could I check if the ippool had run out of IPs or was close to?
I tried with these but clearly this was not good enough . Is there a better method of currently allocated addresses? mysql> select count(*) from radippool where username = ''; +----------+ | count(*) | +----------+ | 383727 | +----------+
and with, mysql> select count(*) from radippool where expiry_time is NULL ; +----------+ | count(*) | +----------+ | 396466 | +----------+
I am surprised this filled up, because we don't have enough devices to fill up our IP space. What could happen if NAS did not send back accounting stop packets?
Can I add more IP addresses into radippol with FreeRADIUS running, or should I shut down FreeRADIUS during this insert?
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 --
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
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 --
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
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
On Nov 19, 2015, at 4:15 AM, Sophie Loewenthal <sophie.loewenthal@trimbletl.com> wrote:
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"
Which is wrong. Because the whole reason for the "allocate-find" is to update the particular record.
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"
Which is correct. The "FOR UPDATE" is important, so that it doesn't allocate the same IP to two different users.
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'
<shrug> Use a database that works. Sorry. But... if your database can't handle transactions like this, you should throw it in the garbage, and use a real database. I suggest Postgresql. I have *never* seen any issues like this with Postgresql. Only with MySQL and it's derivatives.
When I rolled back the changes the duplicated request and deadlocking type messages disappeared.
Because the change allows for race conditions. i.e. the same IP can get assigned to two different users. Removing locks will make it faster. But it will also make it wrong. Alan DeKok
<shrug> Use a database that works.
Upgrade is being planned. On 20-Nov-15 3:47 PM, Alan DeKok wrote:
On Nov 19, 2015, at 4:15 AM, Sophie Loewenthal <sophie.loewenthal@trimbletl.com> wrote:
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" Which is wrong. Because the whole reason for the "allocate-find" is to update the particular record.
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" Which is correct. The "FOR UPDATE" is important, so that it doesn't allocate the same IP to two different users. 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' <shrug> Use a database that works.
Sorry. But... if your database can't handle transactions like this, you should throw it in the garbage, and use a real database. I suggest Postgresql. I have *never* seen any issues like this with Postgresql. Only with MySQL and it's derivatives.
When I rolled back the changes the duplicated request and deadlocking type messages disappeared. Because the change allows for race conditions. i.e. the same IP can get assigned to two different users.
Removing locks will make it faster. But it will also make it wrong.
Alan DeKok
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 20 Nov 2015, at 09:59, Sophie Loewenthal <sophie.loewenthal@trimbletl.com> wrote:
<shrug> Use a database that works.
Upgrade is being planned.
An alternative is to use v3.1.x and rlm_redis_ippool. That has significantly higher performance than any SQL based solution, and is guaranteed not to deadlock, as Redis is largely single threaded and lockless. It scales by distributing different ip pools between different cluster nodes. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
participants (4)
-
Alan DeKok -
Arran Cudbard-Bell -
Rod Elias -
Sophie Loewenthal