syntax errors on mysql ip pools

[Marcelus Trojahn]

Hello,

I've been trying for a few days to configure a new freeradius server with
mysql IP pools support and I noticed there's a few errors with the syntax of
some queries on sqlippool.conf for mysql.

First of all, some queries would never match because the schema provided
with freeradius for the radippools table would set the 'expiry_time' field
as default to NULL and then the queries would try something like expiry_time
< NOW(), which would never match if the field as NULL.

So, the correct schema for the database would be:
CREATE TABLE radippool (
  id int(11) unsigned NOT NULL auto_increment,
  pool_name varchar(30) NOT NULL,
  framedipaddress varchar(15) NOT NULL default '',
  nasipaddress varchar(15) NOT NULL default '',
  calledstationid VARCHAR(30) NOT NULL,
  callingstationid VARCHAR(30) NOT NULL,
  expiry_time DATETIME NOT NULL,
  username varchar(64) NOT NULL default '',
  pool_key varchar(30) NOT NULL,
  PRIMARY KEY (id)
);

And the, the complete ippool.conf should be:

-- begin -----------

# ## This series of queries allocates an IP address
 allocate-clear = "UPDATE ${ippool_table} \
  SET nasipaddress = '', pool_key = 0, \
  callingstationid = '', username = '', \
  expiry_time = '0000-00-00' \
  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} \
  SET nasipaddress = '', pool_key = 0, \
  callingstationid = '', username = '', \
  expiry_time = '0000-00-00' \
  WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
  AND nasipaddress = '%{Nas-IP-Address}'"

## 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} \
 WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW() \
 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"

## 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} \
 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} \
 SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
 callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
 expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE framedipaddress = '%I'"

## This series of queries frees an IP number when an accounting
## START record arrives
start-update = "UPDATE ${ippool_table} \
 SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
 WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}'"

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

-- end of file --------------

I might add I'm not any Mysql expert so any opinions about what I said are
really welcome... I've tested it and apparently it works and I couldn't find
any potential bugs so far...

I hope my english is not that rusty :)

--
Marcelus Trojahn
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20081013/8d600518/attachment.html>