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):
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: