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