Automatically re-connecting to the database.

Tim &al,

DBD::mysql has had the ability to automatically reconnect to the server in the
event that the server closed the connection to the client. This is useful in the
event of timeouts in a mod_perl environment, so you can just connect to the
database and not have to worry about how long the connection was idle because
the client will automatically reconnect to the server in the event of a timeout.
It is also useful in the case where a query or an insert exceeds the max allowed
packet size, for if a statement exceeds max allowed packet, the server will
close the connection on you.

The problem arises when you have temp tables or prepared statements. The two
major cases (that I can think of) where autoreconnect can cause problems are
with the auto_reconnect attribute and with ping.


first, for the the $dbh->{mysql_auto_reconnect} attribute:

Consider this:

my $sth = $dbh->prepare($some_statement); # server-side woohoo!
$sth1->execute($max_packet_size." "); # db gone away.
$dbh->do(q{SELECT 1}); #reconnects to the db.
$sth->execute(); # oops no prepared statement.

So do we say that mysql_auto_reconnect will be disabled when server-side
prepared statements are in use? Do we keep a list of prepared statements and
"un-prepare" them on re-connect and re-prepare them when used or re-prepare on
reconnect?

Of course this does not address temp tables, but that could probably be handled
in documentation; however, prepared statements are within the purview of the
driver.

And for $dbh->ping:

Some code:
sub app_init {
yo_db_give_me_connexion();
prepare_some_statements();
create_temp_tables();
}

for (;;) {
if (!$dbh->ping) {
app_init() or die die die "I am aweary, aweary,/ Oh God, that I wer$
dead!";
}
do_stuff();
take_a_nap();
}

DBD::mysql's ping function uses the mysql API function mysql_ping() which will,
in the event that the server closed the connection, automatically reconnect to
the server and returns TRUE. But if you have code that relies on prepared
statements or temporary tables, you will need to re-prepare those statements or
re-create the temporary tables in the event that you lost the connection to the
server, but since mysql_ping() will always return TRUE when it is able to
establish a connection to the database, how will you know that you need to
re-prepare statements or re-create the temp tables?

Now trying to emulate ping() client-side can cause problems. What call does the
driver use to check the connection? A select? Can that cause the db to start a
tx when autocommit=0? Server version? that can be cached client-side. And for
the rest of the API it looks like either 1 too much overhead or 2. can be cached
server-side.

And then there is the case of backwards compatibility. How many applications
have never had the if(0 == ping()) code tested, because ping would try the
re-connect?

Thoughts, ideas,

Rudolf.



--
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