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

6 Nov 2015


      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