Hello everyone After fixing my mistake with the $ versus % symbols I tried ippool-dhcp again. My select statement worked and allocated an IP from the pool, but it seems that all the other sql statements (update and commit) generated the error "(0) ERROR: dhcp_sqlippool : database query error in: ...". Here is the relevant part from the debug log: rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '0', expiry_time = current_timestamp - INTERVAL '1' SECOND(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' SECOND(1)' (0) ERROR: dhcp_sqlippool : database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '0', expiry_time = current_timestamp - INTERVAL '1' SECOND(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' SECOND(1)' (0) dhcp_sqlippool : expand: 'SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND (pool_key = '%{DHCP-Client-Hardware-Address}' OR expiry_time < current_timestamp) AND rownum <= 1 ORDER BY CASE WHEN pool_key = '%{DHCP-Client-Hardware-Address}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' -> 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND (pool_key = 'c0:ff:ee:c0:ff:ee' OR expiry_time < current_timestamp) AND rownum <= 1 ORDER BY CASE WHEN pool_key = 'c0:ff:ee:c0:ff:ee' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' rlm_sql (sql): Executing query: 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND (pool_key = 'c0:ff:ee:c0:ff:ee' OR expiry_time < current_timestamp) AND rownum <= 1 ORDER BY CASE WHEN pool_key = 'c0:ff:ee:c0:ff:ee' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{DHCP-Client-Hardware-Address}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = current_timestamp + INTERVAL '7200' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND pool_name = '%{control:Pool-Name}'' -> 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = 'c0:ff:ee:c0:ff:ee', callingstationid = 'c0:ff:ee:c0:ff:ee', username = 'DHCP-c0:ff:ee:c0:ff:ee', expiry_time = current_timestamp + INTERVAL '7200' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND pool_name = 'test_ip_pool'' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = 'c0:ff:ee:c0:ff:ee', callingstationid = 'c0:ff:ee:c0:ff:ee', username = 'DHCP-c0:ff:ee:c0:ff:ee', expiry_time = current_timestamp + INTERVAL '7200' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND pool_name = 'test_ip_pool'' (0) ERROR: dhcp_sqlippool : database query error in: 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = 'c0:ff:ee:c0:ff:ee', callingstationid = 'c0:ff:ee:c0:ff:ee', username = 'DHCP-c0:ff:ee:c0:ff:ee', expiry_time = current_timestamp + INTERVAL '7200' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND pool_name = 'test_ip_pool'' (0) dhcp_sqlippool : Allocated IP 10.99.0.11 [0b00630a] (0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT' rlm_sql (sql): Executing query: 'COMMIT' (0) ERROR: dhcp_sqlippool : database query error in: 'COMMIT' rlm_sql (sql): Released connection (1) I verified that my sql statement for allocate-clear works correcly by executing it via SQL Developer (GUI SQL client for Oracle): UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '0', expiry_time = current_timestamp - INTERVAL '1' SECOND(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' SECOND(1) I would be grateful if anyone could help to find out why error "database query error" is recieved. I also wondered if I really need the allocate-clear statement at all, as it does not seem to do anything useful. It clears the some fields in the table but I as far as I can see the other sql statements will work fine even if the fields are not cleared. So I tried the setting allocate-clear as an empty string but I recieved the following error: /usr/local/etc/raddb/sql/ippool-dhcp/oracle/queries.conf[33]: Configuration item'allocate-clear' must not be empty 2013/6/9 Бен Томпсон <b.thompson@latera.ru>
Many thanks.
2013/6/9 Alan DeKok <aland@deployingradius.com>
Бен Томпсон wrote:
I am still testing ippool-dhcp and after updating to the latest git HEAD I have come across a new issue. For some reason I am seeing the error "Unknown attribute" regarding the variable %{pool-key}.
That's because the Pool-Key attribute doesn't exist.
The example "dhcp_sqlippool" module doesn't have %{pool-key}. It uses ${pool-key}. See the difference?
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html