Re: Oracle cursor leak
On Thu, Nov 11, 2004 at 02:23:36PM +0300, Alexander Serkin wrote:
>
OK here is the patched file. You can verify it if you diff it with the
original file.
Kostas
>
> Kostas Zorbadelos wrote:
> ...
> >
> >I resubmit the patch as a text file (output of
> >diff sql_oracle.c.before_patch sql_oracle.c > freeradius_oracle_patch)
> >because from the web page I had
> >problems applying it and I was forced to apply it partly by hand
> >editing of the code...
> >
>
> the same problem. I cannot apply patch taken from the web:
>
> patching file src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c
> patch: malformed patch at line 60: @@ -311,9 +328,11 @@
>
>
> --
> Sincerely Yours,
> Alexander Serkin,
> Skylink, Moscow
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
--
Kostas Zorbadelos
Systems Developer, Otenet SA
mailto: [EMAIL PROTECTED]
Out there in the darkness, out there in the night
out there in the starlight, one soul burns brighter
than a thousand suns.
/*
* sql_oracle.c Oracle (OCI) routines for rlm_sql
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
* Copyright 2000 The FreeRADIUS server project
* Copyright 2000 David Kerry <[EMAIL PROTECTED]>
*/
#include
#include
#include
#include
#include
#include "radiusd.h"
#include
#include "rlm_sql.h"
typedef struct rlm_sql_oracle_sock {
OCIEnv *env;
OCIError *errHandle;
OCISvcCtx *conn;
OCIStmt *queryHandle;
sb2 *indicators;
char **results;
int id;
int in_use;
struct timeval tv;
} rlm_sql_oracle_sock;
#define MAX_DATASTR_LEN 64
/*
*
* Function: sql_error
*
* Purpose: database specific error. Returns error associated with
* connection
*
*/
static char *sql_error(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
static char msgbuf[512];
sb4 errcode = 0;
rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
if (!oracle_sock) return "rlm_sql_oracle: no connection to db";
memset((void *) msgbuf, (int)'\0', sizeof(msgbuf));
OCIErrorGet((dvoid *) oracle_sock->errHandle, (ub4) 1, (text *) NULL,
&errcode, msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
if (errcode) {
return msgbuf;
}
else {
return NULL;
}
}
/*
*
* Function: sql_check_error
*
* Purpose: check the error to see if the server is down
*
*/
static int sql_check_error(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
if (strstr(sql_error(sqlsocket, config), "ORA-03113") ||
strstr(sql_error(sqlsocket, config), "ORA-03114")) {
radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NOT_CONNECTED");
return SQL_DOWN;
}
else {
radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NORMAL");
return -1;
}
}
/*
*
* Function: sql_close
*
* Purpose: database specific close. Closes an open database
* connection and cleans up any open handles.
*
*/
static int sql_close(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
rlm_sql_oracle_sock *oracle_sock = sqlsocket->conn;
if (oracle_sock->conn) {
OCILogoff (oracle_sock->conn, oracle_sock->errHandle);
}
if (oracle_sock->queryHandle) {
OCIHandleFree((dvoid *)oracle_sock->queryHandle, (ub4) OCI_HTYPE_STMT);
}
if (oracle_sock->errHandle) {
OCIHandleFree((dvoid *)oracle_sock->errHandle, (ub4) OCI_HTYPE_ERROR);
}
if (oracle_sock->env) {
OCIHandleFree((dvoid *)oracle_sock->env, (ub4) OCI_HTYPE_ENV);
}
oracle_sock->conn = NULL;
free(oracle_sock);
sqlsocket->conn = NULL;
return 0;
}
/*
*
* Function: sql_init_socket
*
* Purpose: Establish connection to the db
*
*/
static int sql_init_socket(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
rlm_sql_oracle_sock *oracle_sock;
if (!sqlsocket->conn) {
sqlsocket->conn = (rlm_sql_oracle_sock *)rad_malloc(sizeof(rlm_sq
Re: Oracle cursor leak
Kostas Zorbadelos wrote: ... I resubmit the patch as a text file (output of diff sql_oracle.c.before_patch sql_oracle.c > freeradius_oracle_patch) because from the web page I had problems applying it and I was forced to apply it partly by hand editing of the code... the same problem. I cannot apply patch taken from the web: patching file src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c patch: malformed patch at line 60: @@ -311,9 +328,11 @@ -- Sincerely Yours, Alexander Serkin, Skylink, Moscow - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
Kostas Zorbadelos writes: I am not using RADGROUPCHECK and RADGROUPREPLY either so I have commented out all the relevant lines for them (including the queries) in oraclesql.conf. I haven't noticed any problem with cursor allocating in oracle 8i. Our DBA told me that there are database parameters you can tune that could help (look at cursor_sharing and instead of the value EXACT use FORCE (for 8i) or SIMILAR (for 9i)). Kostas Thanks Kostas, I will follow your advice and keep you informed. Best regards Roberto - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
On Wed, Nov 03, 2004 at 07:27:18PM +0100, Roberto Re wrote: > hi, > > I have applied the patch yesterday only, but the problem still exists. > The cursors are allocated and they continuously increase up to the maximum > limit imposed by Oracle to the db. > > I have analysed some of the queries allocating the cursors: there are some > query to RADGROUPCHECK and RADGROUPREPLY tables, which are _*empty*_. > Could it be those ones raising the problem? > > As we are not using those 2 tables , would it be possible to modify the > cfg of Freeradius, so that it does no longer use them? > > I will also try to insert some dummy-values in the two RADGROUP... > > Thanks and regards > Roberto > I am not using RADGROUPCHECK and RADGROUPREPLY either so I have commented out all the relevant lines for them (including the queries) in oraclesql.conf. I haven't noticed any problem with cursor allocating in oracle 8i. Our DBA told me that there are database parameters you can tune that could help (look at cursor_sharing and instead of the value EXACT use FORCE (for 8i) or SIMILAR (for 9i)). Kostas > > > > On Thu, Oct 14, 2004 at 11:13:40AM +0200, Roberto Re wrote: > >> > >> > >> Kostas Zorbadelos wrote: > >> > >> >On Wed, Oct 13, 2004 at 06:25:25PM +0200, Roberto Re wrote: > >> > > >> >>First of all thanks for your attention, Alan > >> >> > >> >>My problem however seems to be more like this: > >> >>http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html > >> >> > >> >>I had already checked the working code, which includes that patch and > >> it > >> >>is exactly the following one: > >> >> > >> >>http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c > >> >> > >> >The code in this url does not include the patch Alan is reffering > >> >to. Of course the patch in > >> >http://bugs.freeradius.org/show_bug.cgi?id=128 addresses the > >> >freeradius crash in case of Oracle errors in sql queries. This happens > >> >with the Oracle 8i client libraries. I was told that Oracle 9 client > >> >libs do not cause the freeradius crash (not tested my self). > >> > >> In my experience with Oracle 9 client (on a Linux RedHat Enterprise) the > >> freeRADIUS dont crash, it dont realease cursors on the oracle side. > >> > >> Roberto > >> > > > > OK, if the crashes do not happen on successive Oracle errors, try the > > patch and let us know if it also solves your problem. > > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > > -- Kostas Zorbadelos Systems Developer, Otenet SA mailto: [EMAIL PROTECTED] Out there in the darkness, out there in the night out there in the starlight, one soul burns brighter than a thousand suns. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
hi, I have applied the patch yesterday only, but the problem still exists. The cursors are allocated and they continuously increase up to the maximum limit imposed by Oracle to the db. I have analysed some of the queries allocating the cursors: there are some query to RADGROUPCHECK and RADGROUPREPLY tables, which are _*empty*_. Could it be those ones raising the problem? As we are not using those 2 tables , would it be possible to modify the cfg of Freeradius, so that it does no longer use them? I will also try to insert some dummy-values in the two RADGROUP... Thanks and regards Roberto > On Thu, Oct 14, 2004 at 11:13:40AM +0200, Roberto Re wrote: >> >> >> Kostas Zorbadelos wrote: >> >> >On Wed, Oct 13, 2004 at 06:25:25PM +0200, Roberto Re wrote: >> > >> >>First of all thanks for your attention, Alan >> >> >> >>My problem however seems to be more like this: >> >>http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html >> >> >> >>I had already checked the working code, which includes that patch and >> it >> >>is exactly the following one: >> >> >> >>http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c >> >> >> >The code in this url does not include the patch Alan is reffering >> >to. Of course the patch in >> >http://bugs.freeradius.org/show_bug.cgi?id=128 addresses the >> >freeradius crash in case of Oracle errors in sql queries. This happens >> >with the Oracle 8i client libraries. I was told that Oracle 9 client >> >libs do not cause the freeradius crash (not tested my self). >> >> In my experience with Oracle 9 client (on a Linux RedHat Enterprise) the >> freeRADIUS dont crash, it dont realease cursors on the oracle side. >> >> Roberto >> > > OK, if the crashes do not happen on successive Oracle errors, try the > patch and let us know if it also solves your problem. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
Kostas Zorbadelos <[EMAIL PROTECTED]> wrote: > However the patch is not included in freeradius 1.0.1. Any plans of > including it in a later version? I'd like to put it in 1.0.2. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak (Roberto Re)
Hi, apply the patch in the bug number 128. The 1.0.1 version doesn't contain it. This patch solves your problem and probably manages better the Oracle connection. Andrea At 17.49 14/10/2004, you wrote: Hi List, I have some problem with Oracle too, when any error occured like index violation oracle just drop one connection, it says re-connect but I don't think it is not re-connecting, and in some days radiusd is out of connection and I got no more connection to Db , login denied message in the end from Radius. Also I have about 75 connections to DB. First i was thinking its cause of more connection to the server but when this happen Radius just stop replying and become Idle until I re-start it again. Is this thing is releated to the cursor leak or a bug ? I am using the latest 1.0.1 of freeradius. Regards, Babar Shafiq Nazmi. ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html --- A computer does exactly what it's told. This is not necessarily what you want. --- Ing. Andrea Gabellini Email: [EMAIL PROTECTED] Tel: 0549 886111 (Italy) Tel. +378 0549 886111 (International) Intelcom San Marino S.p.A. Strada degli Angariari, 3 47891 Rovereta Repubblic of San Marino http://www.omniway.sm http://www.intelcom.sm - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak (Roberto Re)
Hi List, I have some problem with Oracle too, when any error occured like index violation oracle just drop one connection, it says re-connect but I don't think it is not re-connecting, and in some days radiusd is out of connection and I got no more connection to Db , login denied message in the end from Radius. Also I have about 75 connections to DB. First i was thinking its cause of more connection to the server but when this happen Radius just stop replying and become Idle until I re-start it again. Is this thing is releated to the cursor leak or a bug ? I am using the latest 1.0.1 of freeradius. Regards, Babar Shafiq Nazmi. ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
On Thu, Oct 14, 2004 at 11:13:40AM +0200, Roberto Re wrote: > > > Kostas Zorbadelos wrote: > > >On Wed, Oct 13, 2004 at 06:25:25PM +0200, Roberto Re wrote: > > > >>First of all thanks for your attention, Alan > >> > >>My problem however seems to be more like this: > >>http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html > >> > >>I had already checked the working code, which includes that patch and it > >>is exactly the following one: > >> > >>http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c > >> > >The code in this url does not include the patch Alan is reffering > >to. Of course the patch in > >http://bugs.freeradius.org/show_bug.cgi?id=128 addresses the > >freeradius crash in case of Oracle errors in sql queries. This happens > >with the Oracle 8i client libraries. I was told that Oracle 9 client > >libs do not cause the freeradius crash (not tested my self). > > In my experience with Oracle 9 client (on a Linux RedHat Enterprise) the > freeRADIUS dont crash, it dont realease cursors on the oracle side. > > Roberto > OK, if the crashes do not happen on successive Oracle errors, try the patch and let us know if it also solves your problem. -- Kostas Zorbadelos Systems Developer, Otenet SA mailto: [EMAIL PROTECTED] Out there in the darkness, out there in the night out there in the starlight, one soul burns brighter than a thousand suns. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
Kostas Zorbadelos wrote: On Wed, Oct 13, 2004 at 06:25:25PM +0200, Roberto Re wrote: First of all thanks for your attention, Alan My problem however seems to be more like this: http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html I had already checked the working code, which includes that patch and it is exactly the following one: http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c The code in this url does not include the patch Alan is reffering to. Of course the patch in http://bugs.freeradius.org/show_bug.cgi?id=128 addresses the freeradius crash in case of Oracle errors in sql queries. This happens with the Oracle 8i client libraries. I was told that Oracle 9 client libs do not cause the freeradius crash (not tested my self). In my experience with Oracle 9 client (on a Linux RedHat Enterprise) the freeRADIUS dont crash, it dont realease cursors on the oracle side. Roberto - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
On Wed, Oct 13, 2004 at 06:25:25PM +0200, Roberto Re wrote:
> First of all thanks for your attention, Alan
>
> My problem however seems to be more like this:
> http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html
>
> I had already checked the working code, which includes that patch and it
> is exactly the following one:
>
> http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c
>
The code in this url does not include the patch Alan is reffering
to. Of course the patch in
http://bugs.freeradius.org/show_bug.cgi?id=128 addresses the
freeradius crash in case of Oracle errors in sql queries. This happens
with the Oracle 8i client libraries. I was told that Oracle 9 client
libs do not cause the freeradius crash (not tested my self). Anyway
the proposed patch is said to better handle the Oracle connections.
I tested the patch myself and it works OK. Crashes no longer occur and
freeradius handles Oracle connections better.
However the patch is not included in freeradius 1.0.1. Any plans of
including it in a later version?
I resubmit the patch as a text file (output of
diff sql_oracle.c.before_patch sql_oracle.c > freeradius_oracle_patch)
because from the web page I had
problems applying it and I was forced to apply it partly by hand
editing of the code...
Kostas
> Function: sql_free_result , does this function realease memory but not
> any cursors on the oracle side?
>
> Thanks
> Roberto
>
>
>
>
>
> Alan DeKok wrote:
>
> >Roberto Re <[EMAIL PROTECTED]> wrote:
> >
> >>I've installed a FreeRADIUS version 1.0.0 on a Linux Red Hat Enterprise
> >>with Oracle Client 9.1, it never close any cursors it opened, leading to
> >>all sorts of interesting problems when the max-open-cursor limits
> >>were hit.
> >>
> >>How can I fix this problem ?
> >
> >
> > http://bugs.freeradius.org/show_bug.cgi?id=128
> >
> > The patch there may help. If it does, please say so on the list.
> >
> > Alan DeKok.
> >
> >-
> >List info/subscribe/unsubscribe? See
> >http://www.freeradius.org/list/users.html
> >
>
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
--
Kostas Zorbadelos
Systems Developer, Otenet SA
mailto: [EMAIL PROTECTED]
Out there in the darkness, out there in the night
out there in the starlight, one soul burns brighter
than a thousand suns.
75a76,94
> /*
> *
> *Function: sql_check_error
> *
> *Purpose: check the error to see if the server is down
> *
> */
> static int sql_check_error(SQLSOCK *sqlsocket, SQL_CONFIG *config) {
>
> if (strstr(sql_error(sqlsocket, config), "ORA-03113") ||
> strstr(sql_error(sqlsocket, config), "ORA-03114")) {
> radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NOT_CONNECTED");
> return SQL_DOWN;
> }
> else {
> radlog(L_ERR,"rlm_sql_oracle: OCI_SERVER_NORMAL");
> return -1;
> }
> }
247c266
< (ub4) OCI_DEFAULT);
---
> (ub4) OCI_COMMIT_ON_SUCCESS);
249,252c268,269
< if ((x != OCI_NO_DATA) && (x != OCI_SUCCESS)) {
< radlog(L_ERR,"rlm_sql_oracle: execute query failed in sql_query: %s",
< sql_error(sqlsocket, config));
< return SQL_DOWN;
---
> if (x == OCI_SUCCESS) {
> return 0;
255,257c272,273
< x = OCITransCommit(oracle_sock->conn, oracle_sock->errHandle, (ub4) 0);
< if (x != OCI_SUCCESS) {
< radlog(L_ERR,"rlm_sql_oracle: commit failed in sql_query: %s",
---
> if (x == OCI_ERROR) {
> radlog(L_ERR,"rlm_sql_oracle: execute query failed in sql_query: %s",
259c275,278
< return SQL_DOWN;
---
> return sql_check_error(sqlsocket, config);
> }
> else {
> return -1;
261,262d279
<
< return 0;
314,316c331,335
< else if (x != OCI_SUCCESS) {
< radlog(L_ERR,"rlm_sql_oracle: query failed in sql_select_query:
%s",sql_error(sqlsocket, config));
< return SQL_DOWN;
---
>
> if (x != OCI_SUCCESS) {
> radlog(L_ERR,"rlm_sql_oracle: query failed in sql_select_query: %s",
> sql_error(sqlsocket, config));
> return sql_check_error(sqlsocket, config);
495,496c514,517
< if (x == OCI_NO_DATA) {
< return -1;
---
>
> if (x == OCI_SUCCESS) {
> sqlsocket->row = oracle_sock->results;
> return 0;
498,499c519,520
< else if (x != OCI_SUCCESS) {
< /* XXX Check if x suggests we should return SQL_DOWN */
---
>
> if (x == OCI_ERROR) {
502c523
< return SQL_DOWN;
---
> retu
Re: Oracle cursor leak
Roberto Re <[EMAIL PROTECTED]> wrote: > I had already checked the working code, which includes that patch and it > is exactly the following one: Ok. So are you *still* seeing a problem? If so, no one else has reported anything similar yet. > Function: sql_free_result , does this function realease memory but not > any cursors on the oracle side? I have no idea. I've never programmed using the Oracle API. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
First of all thanks for your attention, Alan My problem however seems to be more like this: http://lists.cistron.nl/pipermail/freeradius-devel/2002-December/004052.html I had already checked the working code, which includes that patch and it is exactly the following one: http://www.freeradius.org/cvs-log/radiusd/src/modules/rlm_sql/drivers/rlm_sql_oracle/sql_oracle.c Function: sql_free_result , does this function realease memory but not any cursors on the oracle side? Thanks Roberto Alan DeKok wrote: Roberto Re <[EMAIL PROTECTED]> wrote: I've installed a FreeRADIUS version 1.0.0 on a Linux Red Hat Enterprise with Oracle Client 9.1, it never close any cursors it opened, leading to all sorts of interesting problems when the max-open-cursor limits were hit. How can I fix this problem ? http://bugs.freeradius.org/show_bug.cgi?id=128 The patch there may help. If it does, please say so on the list. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Oracle cursor leak
Roberto Re <[EMAIL PROTECTED]> wrote: > I've installed a FreeRADIUS version 1.0.0 on a Linux Red Hat Enterprise > with Oracle Client 9.1, it never close any cursors it opened, leading to > all sorts of interesting problems when the max-open-cursor limits > were hit. > > How can I fix this problem ? http://bugs.freeradius.org/show_bug.cgi?id=128 The patch there may help. If it does, please say so on the list. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Oracle cursor leak
Hi, I've installed a FreeRADIUS version 1.0.0 on a Linux Red Hat Enterprise with Oracle Client 9.1, it never close any cursors it opened, leading to all sorts of interesting problems when the max-open-cursor limits were hit. How can I fix this problem ? Thanks in advance Roberto - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
