Reconnecting to mysql

Hi list,

I'm trying (against sanity it seems) to make DBI/DBD::mysql reconnect
to my mysql server by subclassing DBI.

The reason for this is that I have a script that runs over a long
period of time doing a steady flow of sql-statements and sometimes
during this time is the database stoped for maintainance/backup. I
don't want my script to go nuts at that point. {mysql_auto_reconnect
=> 1} isn't enough since the database can be down at the time of my
query and that makes DBD::mysql sad.

What I want to do feels like it ought to be fairly simple if I only
knew how to/what to do/call in the DBD::mysql. The scenario looks like
this:

my_script.pl:
....
my $dbh = MyDBI->connect ();
my $sth = $dbh->prepare ($sql);
# the database is taken down for maintaince
$sth->execute; # the database is still down when MyDBI::st gets the call


MyDBI.pm:
package MyDBI;
use strict;
use DBI;
use DBD::mysql;
use vars qw(@ISA);
@ISA = qw(DBI);

sub connect
{
# I only have this function to be able to add default timouts and
amount of retries
my $this = $class->SUPER::connect($dsn, $uname, $passwd);
return $this;
}

package MyDBI::db;
@MyDBI::db::ISA = qw(DBI::db);


package MyDBI::st;
@MyDBI::st::ISA = qw(DBI::st);

sub execute
{
my $this = shift;
# here be my problems. If database is down I want to try to
reconnect transparent like DBD::mysql does with mysql_auto_reconnect
and if I can't reconnect sleep and try again untill i succeed or
exceed some default timeout
return $this->SUPER::execute (@_);
}


So finaly, the problem as i se it is:
Figure out what to call from MyDBI::st->execute with my $sth ($this),
that will make the MAIN::dbh and the MAIN::sth change (to be the
"new" reconnected sth and dbh instead).

I've noticed that auto_reconnect forgets the prepared statement if the
database is down when one tries execute, takes up db and tries to run
execute again. This is also something I'd like to not have.

Does anyone in the list know how to do this? I don't want to make a
MyDB module that has some of the feautures of DBI/DBD by forwarding
the calls since it's likely there will be feautures missed and it'll
lead to a cluttered module, and I don't want to call
connect_cached/have eval blocks around all my db-calls since I
consider that bad and ugly.

Thanks
Jon

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Reconnecting to mysql

Hello Jon,

> {mysql_auto_reconnect
> => 1} isn't enough since the database can be down at the time of my
> query and that makes DBD::mysql sad.

Sounds like you are using real MySQL server side prepared statements,
rather than the DBD emulation. So, one of the things you should
consider is that, although MySQL supports automatic reconnection, the
MySQL sever side components of the prepared statements are lost when
MySQL exits. Therefore, all of your DBI statement handles are
invalid/worthless, without their MySQL counterparts, as
mysql_auto_reconnect doesn't re-prepair the statements. If you were to
use driver emulated prepared statements, auto_reconnect should be
sufficient, to the best of my knowledge.

So, what you may be able to do:
* disable mysql_auto_reconnect
* have a connect sub, where you also (re)prepair all of your prepaired
statements one after the other (you could save them in a global hash).
You could loop the connect() part every few seconds until it succeeds
and then re-prepair the statements.
* If you don't like/use evals around your DBI code, you need to set
Raise Error to 0 and check the return value from connect, prepare and
execute for errors and call the connect sub again if necessary and
then re-execute the statement.
* You can use $dbh->ping to check whether your connection is still
live (as long as mysql_auto_reconnect is off). However, rather than
checking that before every execute/prepare, you should probably just
check the return value from DBI to catch any errors.

Best Regards,

Christian

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Reconnecting to mysql

On 11/9/06, Christian Hoermann <0oo0oo0.c@> wrote:
> Hello Jon,

Hi there

>
> > {mysql_auto_reconnect
> > => 1} isn't enough since the database can be down at the time of my
> > query and that makes DBD::mysql sad.
>
> Sounds like you are using real MySQL server side prepared statements,
> rather than the DBD emulation. So, one of the things you should
> consider is that, although MySQL supports automatic reconnection, the
> MySQL sever side components of the prepared statements are lost when
> MySQL exits. Therefore, all of your DBI statement handles are
> invalid/worthless, without their MySQL counterparts, as
> mysql_auto_reconnect doesn't re-prepair the statements. If you were to
> use driver emulated prepared statements, auto_reconnect should be
> sufficient, to the best of my knowledge.
>

auto_reconnect is fine if there's been a restart or something like
that, the point is if the DB happends to be unreachable (down for
maintainance) at the time of the reconnect will it fail, also losing
any prepared statements. Losing the statements isn't a big issue in it
self but it won't try to reconnect again if it fails once.

> So, what you may be able to do:
> * disable mysql_auto_reconnect
> * have a connect sub, where you also (re)prepair all of your prepaired
> statements one after the other (you could save them in a global hash).
> You could loop the connect() part every few seconds until it succeeds
> and then re-prepair the statements.
> * If you don't like/use evals around your DBI code, you need to set
> Raise Error to 0 and check the return value from connect, prepare and
> execute for errors and call the connect sub again if necessary and
> then re-execute the statement.
> * You can use $dbh->ping to check whether your connection is still
> live (as long as mysql_auto_reconnect is off). However, rather than
> checking that before every execute/prepare, you should probably just
> check the return value from DBI to catch any errors.
>


Yes this is also my conclution, but the problem is that I want the
reconnect to happend at MyDBI::st->execute (and fetch*) but all I get
into those functions is the statement handle. What I can't figure out
is:

1. How to get a hold of the database handle used in the script using the module
2. How to modify the database handle (reconnect) changing the dbh in
the script, like mysql_auto_reconnect does

That is (in MyDBI::st):

sub execute
{
my $sth = shift;
my (@bind_vars) = @_;

# 1. how to see if I have a connection to the database with the sth?
# 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?

return $sth->SUPER::execute (@bind_vars);
}

Thanks
Jon

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

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/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org