Re: [Sqlrelay-discussion] Bug report, open client connections not freed, with code to reproduce problem

[David M. <dav...@fi...>]

Thanks,

I'll check it out and let you know.

Dave
dav...@fi...

On Mon, 2007-10-15 at 12:32 +0200, Ingmar Brouns wrote:
> Hi David,
> 
> I am using:
> 
> sqlrelay 0.39.4
> Oracle 10g
> php 4.3.11
> 
> I've discovered a bug that will cause sqlrelay to think that client
> connections are still open while they are not. This is quite
> troublesome, as once all client connections are taken, sqlrelay will
> have to be restarted. I've been able to isolate the bug in a piece of
> code. In the code, I've denoted two sqlrcur_free statements as #A and
> #B respectively. Trouble will occur in the situation in which #A has
> not been commented out while #B has been commented out. When both have
> been commented out, there is no problem. To run the example, first a
> simple table has to be created: 
> 
> CREATE TABLE t1 
> (
> c1 varchar(20),
> c2 varchar(20),
> c3 varchar(20),
> c4 varchar(20),
> c5 varchar(20),
> c6 varchar(20),
> c7 varchar(20),
> c8 varchar(20),
> c9 varchar(20),
> c10 varchar(20),
> c11 varchar(20),
> c12 varchar(20),
> c13 varchar(20),
> c14 varchar(20),
> c15 varchar(20),
> c16 varchar(20),
> c17 varchar(20)
> );
> 
> It is actually also possible to reveil the bug using only DUAL, but I
> needed a lot of duals to do so. Hence, I think this example is better.
> Below is the example code: 
> 
> //Funtion to connect to a db_schema, initializes the variable $conn
> //connect_to_user_db($DB_SCHEMA);
> 
> $query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype; BEGIN
> OPEN ref_cur FOR 
>         SELECT   
>             t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3, t3.c1, t3.c2,
> t3.c3                              
>         FROM    t1 
>         ,       t1 t2 
>         ,       t1 t3 
>         WHERE 1 <> 1;
>        :query_handle := ref_cur ; END; 
>        ";
>        
>     $cur = sqlrcur_alloc($conn);
>     sqlrcur_prepareQuery($cur, $query);
>     sqlrcur_defineOutputBindCursor($cur, "query_handle");
>     sqlrcur_executeQuery($cur);
>     $query_cursor = sqlrcur_getOutputBindCursor($cur,
> "query_handle"); 
>     sqlrcur_fetchFromBindCursor($query_cursor); 
>     sqlrcur_free($query_cursor);                           //#A
>     sqlrcur_free($cur);
> 
> 
>     $cur = sqlrcur_alloc($conn);
>     $query = "DECLARE TYPE curtype IS REF CURSOR; ref_cur curtype;
> BEGIN OPEN ref_cur FOR SELECT * FROM DUAL; :query_handle := ref_cur ;
> END;";     
>     sqlrcur_prepareQuery($cur, $query);
>     sqlrcur_defineOutputBindCursor($cur, "query_handle");
>     sqlrcur_executeQuery($cur);
>     $query_cursor = sqlrcur_getOutputBindCursor($cur,
> "query_handle"); 
>     sqlrcur_fetchFromBindCursor($query_cursor);
>     //sqlrcur_free($query_cursor);                          //#B
>     sqlrcur_free($cur);
>     sqlrcon_free($conn);
> 
> Just hit the refresh button a couple of times, and with sqlr-status
> you will see the open client connections increase, these are never
> released again. Now the strange thing is that if you look in the
> directory  /usr/local/firstworks/var/sqlrelay/tmp/pids/, then there is
> a file for each connection related to the schema. However, if you grep
> on the process list for these pids, then you will find that these
> processes do not exist. This is why I think that sqlrelay thinks that
> the connection is still there while it is not. When you stop sqlrelay
> using sqlr-stop, then these pid files are not deleted and sqlr-stop
> will complain that these processes do not exist. 
> 
> Please let me know whether the code has helped reproducing the
> problem. If you have any questions, let me know.
> 
> Regards,
> 
> Ingmar
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion