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

[Rod Elias]

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

--