The mailing list for listmasters using Sympa

[address@concealed]

It's been a while since I dealt with this issue on our server (v 5.0.1) so this may not still apply.

The problem that we found was that there were many places where statement handles were not dereferenced completely.  

Our solution was to find every place where a statement handle was defined and explicitly call $sth->finish before releasing it.  

Here is an example:

## Lowercase field from database

sub lowercase_field {

    my ($table, $field) = @_;

    my $total = 0;

    ## Check database connection

    unless ($dbh and $dbh->ping) {

        return undef unless &db_connect();

    }

    unless ($sth = $dbh->prepare("SELECT $field from $table")) {

        do_log('err','Unable to prepare SQL statement : %s', $dbh->errstr);

        return undef;

    }

    unless ($sth->execute) {

        do_log('err','Unable to execute SQL statement : %s', $dbh->errstr);

        $sth->finish();   <====   Add this

        return undef;

    }

    while (my $user = $sth->fetchrow_hashref) {

        my $lower_cased = lc($user->{$field});

        next if ($lower_cased eq $user->{$field});

        $total++;

        ## Updating Db

        my $statement = sprintf "UPDATE $table SET $field=%s WHERE ($field=%s)", $dbh->quote($lower_cased), $dbh->quote($user->{$field});

        unless ($dbh->do($statement)) {

            do_log('err','Unable to execute SQL statement "%s" : %s', $statement, $dbh->errstr);

            $sth->finish();   <====   Add this

            return undef;

        }

    }

    $sth->finish();

    return $total;

}

This needs to be done throughout the code and there are places where it gets tricky.

On Jun 20, 2007, at 5:56 AM, Jahnke-Zumbusch, Dirk wrote:

Hi all,

I still hit the following problem with Oracle 10gR2 as our database

(SYMPA 5.1.2):

wwsympa[26968]: 

 Unable to prepare SQL statement : 

 ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum

open cursors exceeded 

 (DBD ERROR: error possibly near <*> indicator at char 540 in 

  'SELECT user_admin "email", comment_admin "gecos", reception_admin

"reception", .........

So I had a look which statements are holding cursors and never

give them back:

select count(sql_text),sql_text 

 from gv$open_cursor 

 where user_name = 'SYMPA' 

 group by sql_text 

 order by 1 desc;

resulting in:

9180 SELECT user_admin "email", comment_admin "gecos", reception_

153 SELECT count(*) FROM subscriber_table WHERE list_subscriber 

139 SELECT user_subscriber "email", comment_subscriber "gecos", 

49 SELECT count(*) FROM subscriber_table WHERE (list_subscriber

9 SELECT email_user "email", gecos_user "gecos", password_user

7 select SYSDATE from DUAL /* ping */

...

We have ~1,100 lists and about 300 of them are including subscriber

addresses from "include_sql_query" and some fewer also "owner_include"

by parametrized SQL-queries.

The very high number of oipen cursors in the first row of the

open cursor statistic points to a problem with owner_include,

as *_admin columns are addressed.

Does anybody notice the same problem?

Where is the difference in handling "owner_include"s and

"include_sql_query" ?  There are by 10 more open cursors

for the owner_include than for the include_sql_queries.

Best regards

--

Dirk Jahnke-Zumbusch              Deutsches Elektronen-Synchrotron DESY

IT Information Fabrics              Member of the Helmholtz Association

D-22603 Hamburg                        Notkestrasse 85  / 22607 Hamburg

T: +49-40-899.81760   F: +49-40-899.41760  address@concealed