I have attached new schema and queries for mysql sqlippool. These should work with both 4 and 5 MySQL versions (I have done some manual testing on both; 4 on Linux and 5 on Windows). In version 5 (at least the Windows one I have tested on) CURRENT_TIMESTAMP defaults to '0000-00-00 00:00:00' instead of current date. This has no impact on how queries work. It might have been fixed in later MySQL verions (this is 5.0.18).
I am not sure if I have done diff from the correct files so I included both patches and files. Ivan Kalik Kalik Informatika ISP
--- ippool.sql 2009-01-29 22:33:12.000000000 +0100 +++ ippooln.sql 2009-01-29 22:39:22.000000000 +0100 @@ -8,7 +8,7 @@ nasipaddress varchar(15) NOT NULL default '', calledstationid VARCHAR(30) NOT NULL, callingstationid VARCHAR(30) NOT NULL, - expiry_time DATETIME NULL default NULL, + expiry_time TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, username varchar(64) NOT NULL default '', pool_key varchar(30) NOT NULL, PRIMARY KEY (id)
ippooln.sql
Description: Binary data
--- ippool.conf 2009-01-29 19:13:12.000000000 +0100
+++ ippooln.conf 2009-01-29 23:29:32.000000000 +0100
@@ -8,7 +8,7 @@
# allocate-clear = "UPDATE ${ippool_table} \
# SET nasipaddress = '', pool_key = 0, \
# callingstationid = '', username = '', \
-# expiry_time = NULL \
+# expiry_time = NOW() - INTERVAL 1 SECOND \
# WHERE pool_key = '${pool-key}'"
## This series of queries allocates an IP address
@@ -19,8 +19,8 @@
allocate-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, \
callingstationid = '', username = '', \
- expiry_time = NULL \
- WHERE expiry_time <= NOW() - INTERVAL 1 SECOND
+ expiry_time = NOW() - INTERVAL 1 SECOND \
+ WHERE pool_key = '${pool-key}' \
AND nasipaddress = '%{Nas-IP-Address}'"
@@ -39,7 +39,7 @@
# ## use this query instead
# allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
# WHERE pool_name = '%{control:Pool-Name}' \
-# AND expiry_time =s NULL \
+# AND expiry_time < NOW() \
# ORDER BY RAND() \
# LIMIT 1 \
# FOR UPDATE"
@@ -59,21 +59,21 @@
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' AND expiry_time = NULL"
+ WHERE framedipaddress = '%I'"
-## This series of queries frees an IP number when an accounting
+## This query updates lease duration for an IP address 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
+## This query frees an IP number when an accounting
## STOP record arrives
stop-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
- expiry_time = NULL \
+ expiry_time = NOW() - INTERVAL 1 SECOND \
WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
AND username = '%{User-Name}' \
AND callingstationid = '%{Calling-Station-Id}' \
@@ -81,7 +81,7 @@
-## This series of queries frees an IP number when an accounting
+## This query updates lease duration for an IP address when an accounting
## ALIVE record arrives
alive-update = "UPDATE ${ippool_table} \
SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
@@ -92,18 +92,18 @@
-## This series of queries frees the IP numbers allocate to a
+## This query frees the IP numbers allocated to a
## NAS when an accounting ON record arrives
on-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
- expiry_time = NULL \
+ expiry_time = NOW() - INTERVAL 1 SECOND \
WHERE nasipaddress = '%{Nas-IP-Address}'"
-## This series of queries frees the IP numbers allocate to a
+## This query frees the IP numbers allocated to a
## NAS when an accounting OFF record arrives
off-clear = "UPDATE ${ippool_table} \
SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
- expiry_time = NULL \
+ expiry_time = NOW() - INTERVAL 1 SECOND \
WHERE nasipaddress = '%{Nas-IP-Address}'"
ippooln.conf
Description: Binary data
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
