ippool-dhcp and Oracle

[Бен Томпсон]

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 at latera.ru>

> Many thanks.
>
>
> 2013/6/9 Alan DeKok <aland at 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
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20130609/79990875/attachment-0001.html>