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

Sophie Loewenthal sophie.loewenthal at trimbletl.com
Fri Nov 6 19:57:12 CET 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 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
>

-------------- next part --------------
cat ./sql/mysql/ippool.conf
# -*- text -*-
##
## ippool.conf -- MySQL queries for rlm_sqlippool
##
##      $Id$

# ## This series of queries allocates an IP address
# allocate-clear = "UPDATE ${ippool_table} \
#  SET nasipaddress = '', pool_key = 0, \
#  callingstationid = '', username = '', \
#  expiry_time = NULL, 3GPP_Imsi = '' \
#  WHERE pool_key = '${pool-key}'"

## This series of queries allocates an IP address
## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
## from the WHERE clause)

#allocate-clear = "UPDATE ${ippool_table} /* allocate-clear */ \
# SET nasipaddress = '', pool_key = 0, \
# callingstationid = '', username = '', calledstationid = '', \
# expiry_time = NULL, 3GPP_Imsi = '' \
# WHERE pool_key = '${pool-key}'"

allocate-clear = "UPDATE ${ippool_table} /* allocate-clear */ \
 SET nasipaddress = '', pool_key = 0, \
 callingstationid = '', calledstationid = '', \
 expiry_time = NULL, 3GPP_Imsi = '' \
 WHERE pool_key = '${pool-key}'"


## The ORDER BY clause of this query tries to allocate the same IP-address
## which user had last session...
#allocate-find = "SELECT framedipaddress FROM ${ippool_table} /* allocate-find */ \
# WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL) \
#  ORDER BY (username <> '%{User-Name}'), \
#   (callingstationid <> '%{Calling-Station-Id}'), \
#    expiry_time \
#     LIMIT 1 \
#      FOR UPDATE"
#

# ## If you prefer to allocate a random IP address every time, i
# ## use this query instead
# 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"

 allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
  WHERE pool_name = '%{control:Pool-Name}' \
  AND username = '%{User-Name}' LIMIT 1"


## If an IP could not be allocated, check to see if the pool exists or not
## This allows the module to differentiate between a full pool and no pool
## Note: If you are not running redundant pool modules this query may be
## commented out to save running this query every time an ip is not allocated.
pool-check = "SELECT id FROM ${ippool_table} /* pool-check */ \
 WHERE pool_name='%{control:Pool-Name}' LIMIT 1"


## This is the final IP Allocation query, which saves the allocated ip details
#allocate-update = "UPDATE ${ippool_table} /* allocate-update */ \
# SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
# callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
# expiry_time = NOW() + INTERVAL ${lease-duration} SECOND, \
# 3GPP_Imsi = '%{3GPP-IMSI}', calledstationid = '%{Called-Station-Id}' \
# WHERE framedipaddress = '%I'"

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}'"


## This series of queries frees an IP number when an accounting
## START record arrives
#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}'"

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}'"

## This series of queries frees an IP number when an accounting
## STOP record arrives
#stop-clear = "UPDATE ${ippool_table} /* stop-clear */ \
# SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
# 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}'"

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}'"

## This series of queries frees an IP number when an accounting
## ALIVE record arrives
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}'"



## This series of queries frees the IP numbers allocate to a
## NAS when an accounting ON record arrives
#on-clear = "UPDATE ${ippool_table} /* on-clear */ \
# SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
# expiry_time = NULL, 3GPP_Imsi = '', calledstationid = '' \
# WHERE nasipaddress = '%{Nas-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}'"

## This series of queries frees the IP numbers allocate to a
## NAS when an accounting OFF record arrives
#off-clear = "UPDATE ${ippool_table} /* off-clear */ \
# SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
# 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}'"


More information about the Freeradius-Users mailing list