Hi, This is what happens: SQL> UPDATE radippool SET nasipaddress = '', pool_key = 0,callingstationid = '', expiry_time = current_timestamp - interval '1' second(1) WHERE pool_key = '2398432'; 0 rows updated. SQL> SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''),expiry_time FOR UPDATE; no rows selected SQL> Thank you. On 9/26/06, Peter Nixon <listuser@peternixon.net> wrote:

On Tue 26 Sep 2006 00:49, Guilherme Franco wrote:

...

Mr. Nixon,

Please accept my humble apologies for the earlier spam.

I would like to test your experimental module sqlippool in Oracle and in return, contribute somehow with my working configs and tables to the CVS.

I understand that you are a busy man, so please excuse me if I did bother you.

This question is not directed only to you but I'm asking it because module rlm_sqlippool have pnixon ownership. Please correct me with I'm wrong.

With all the help from yours and everyone from freeradius-user-list, I've managed to make some progress with the sqlippool in Oracle, with just one error left.

I'm not a SQL newbie, but I'm struggling to debug that error because I can't see the originating query as it is apparently mounted on the fly in lib/rlm_sqlippool* objects.

I have C knowledge but didn't discovered what might be causing the error in rlm_sqlippool.c.

Currently, I'm using freeradius-snapshot-20060925.

I've compiled it with --enable-developer and ran radiusd -X through gdb.

The error happens even when I'm using the default v1.4 of sqlippool.conf, without modifying it: ---------------------------------------------------------- Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 0 rlm_sql (sql): Reserving sql socket id: 2 radius_xlat: 'BEGIN' BEGIN rlm_sql_oracle: execute query failed in sql_query: ORA-06550: line 1, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe rlm_sql_oracle: OCI_SERVER_NORMAL sqlippool_command: database query error -----------------------------------------------------------

Next is an output of Oracle's compatible version of sqlippool.conf that I've made. It works, but the IP from the sql pool does not get allocated because of the previous error:

----------------------------------------------------------- UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = current_timestamp - interval '1' second(1) WHERE pool_key = '2398432' SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''), expiry_time FOR UPDATE sqlippool_query1: SQL query did not succeed

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

What happens when you run this query manually?

--

Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Hello, But how can my first query work if the pool-key was not saved anywhere in the database? When I do the same query without the "where pool_key = something", it works: UPDATE radippool SET nasipaddress = '', pool_key = 0,callingstationid = '', expiry_time = current_timestamp - interval '1' second(1); 4 rows updated. SQL> select * from radippool; ID POOL_NAME NASIPADDRESS ---------- ------------------------------ ------------------------------ 1 FOO NAS_PORT ---------- EXPIRY_TIME 26-SEP-06 09.27.54 AM --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- FRAMEDIPADDRESS 192.168.1.1 POOL_KEY CALLINGSTATIONID ------------------------------ 0 Sorry, in the second query I pasted an old query earlier for you. The second query works, it is: SQL> SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''),expiry_time FOR UPDATE; FRAMEDIPADDRESS ------------------------------ 192.168.1.1 Thanks. On 9/26/06, Peter Nixon <listuser@peternixon.net> wrote:

On Tue 26 Sep 2006 14:45, Guilherme Franco wrote:

...

Hi,

This is what happens:

SQL> UPDATE radippool SET nasipaddress = '', pool_key = 0,callingstationid = '', expiry_time = current_timestamp - interval '1' second(1) WHERE pool_key = '2398432';

0 rows updated.

SQL> SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''),expiry_time FOR UPDATE;

no rows selected

So there you go. You found the problem.. Why doesn't it find any rows?

--

Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Sorry if I did not made myself clear. Because of the very first problem, I think: Processing the post-auth section of radiusd.conf modcall: entering group post-auth for request 0 rlm_sql (sql): Reserving sql socket id: 2 radius_xlat: 'BEGIN' BEGIN rlm_sql_oracle: execute query failed in sql_query: ORA-06550: line 1, column 5: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe rlm_sql_oracle: OCI_SERVER_NORMAL sqlippool_command: database query error As a result, it DOES NOT assign an IP to the user: rlm_sqlippool: ip=[ ] len=0 radius_xlat: 'COMMIT' COMMIT rlm_sqlippool: IP number could not be allocated. rlm_sql (sql): Released sql socket id: 2 rlm_sql (sql): Processing sql_postauth radius_xlat: 'test_user2' rlm_sql (sql): sql_set_user escaped user --> 'test_user2' modcall: group post-auth returns noop for request 0 Sending Access-Accept of id 44 to 172.16.4.1 port 2243 Finished request 0 The access is accepted but without an IP. That's the problem. Thank you very much. On 9/26/06, Peter Nixon <listuser@peternixon.net> wrote:

So what exactly is the problem. You posted me a debug log. I told you what the problem was. If you say that it now works then there is no problem.

If you have ANOTHER problem, then post the debug for it.

The first query is not SUPPOSED to work if the pool_key doesn't exist. I CLEARS existing IP leases. Do you have any active leases right now? If not why would you be trying to clear them?

Peter

On Tue 26 Sep 2006 15:41, Guilherme Franco wrote:

...

Hello,

But how can my first query work if the pool-key was not saved anywhere in the database?

When I do the same query without the "where pool_key = something", it works:

UPDATE radippool SET nasipaddress = '', pool_key = 0,callingstationid = '', expiry_time = current_timestamp - interval '1' second(1);

4 rows updated.

SQL> select * from radippool;

ID POOL_NAME NASIPADDRESS ---------- ------------------------------ ------------------------------ 1 FOO NAS_PORT ---------- EXPIRY_TIME 26-SEP-06 09.27.54 AM --------------------------------------------------------------------------- USERNAME --------------------------------------------------------------------------- ----- FRAMEDIPADDRESS 192.168.1.1 POOL_KEY CALLINGSTATIONID ------------------------------ 0

Sorry, in the second query I pasted an old query earlier for you. The second query works, it is:

SQL> SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''),expiry_time FOR UPDATE;

FRAMEDIPADDRESS ------------------------------ 192.168.1.1

Thanks.

On 9/26/06, Peter Nixon <listuser@peternixon.net> wrote:

...

On Tue 26 Sep 2006 14:45, Guilherme Franco wrote:

...

Hi,

This is what happens:

SQL> UPDATE radippool SET nasipaddress = '', pool_key = 0,callingstationid = '', expiry_time = current_timestamp - interval '1' second(1) WHERE pool_key = '2398432';

0 rows updated.

SQL> SELECT framedipaddress FROM radippool WHERE pool_name = 'FOO' AND expiry_time < current_timestamp AND ROWNUM = 1 ORDER BY (select username from radippool where username <> ''), (select callingstationid from radippool where callingstationid <> ''),expiry_time FOR UPDATE;

no rows selected

So there you go. You found the problem.. Why doesn't it find any rows?

--

Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

--

Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html