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

[Ingmar B. <sw...@gm...>]

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