Re: Reconnecting to mysql

Hello

>  # 1. how to see if I have a connection to the database with the sth?

If you don't have any problems with mysql_auto_reconnect itself, why
don't you just execute the statement and then check whether it worked
and handle it accordingly. Most methods return undef on failure.
Here's some code you can try below.


# when you connect, make sure RaiseError is set to 0, so that you can
# handle the error manually. After some testing, set PrintWarn to 0 also

my $dbh = MyDBI->connect('DBI:mysql:database=db_name', 'username', 'pw',
{ RaiseError => 0, PrintError => 1, PrintWarn => 1, AutoCommit => 1 })
or sub_to_handle_error();

# on error, sub_to_handle_error() is called, where you can put a loop
to try again;
# alternatively, you can put the loop in MyDBI; see example for execute() below

.......

$sth->execute($some_var) or handle_real_error();
# handle_real_error is called if the sub below returns 0 or undef,
which should only happen
# if reconnecting failed or the error is not due to a connection problem


========== in MyDBI =========

sub execute {
    my $this = shift;
    my $return_value = 0;
    my $i = 0;
    while (!($return_value = $this->SUPER::execute(@_))) {
        # execute returns undef on error, taking us here
        if ($i > 100) {
            return 0;
            # give up... return 0 or undef for real error
        }
        if ($this->err() != 2006) {
            # The error code for loss of connection should be 2006,
you can test this
            # and check here
http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
            return 0;
        }
        sleep(10);
        $i++;
    }
    return $return_value;
}

I haven't tested the code yet; you need to ensure auto_reconnect can
cope with this.



>  # 2. How to modify $dbh that is used in the script so I won't have to
> have a local dbh in every MyDBI::st function?

I don't know enough about DBI and DBD::mysql to tell you how you can
manipulate some parts of the statement handle, without breaking the
rest.

> changing the dbh in
> the script, like mysql_auto_reconnect does

I don't think that's what happens. Auto_reconnect is part of the MySQL
C API; it should happen automatically and transparently, even if the
dbh is out dated.

Best Regards,

Christian

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]