List:MySQL and Perl« Previous MessageNext Message »
From:Rudy Lippan Date:September 6 2004 6:01pm
Subject:Automatically re-connecting to the database.
View as plain text  
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.


Thread
Automatically re-connecting to the database.Rudy Lippan6 Sep
  • Re: Automatically re-connecting to the database.Vladimir V. Kolpakov6 Sep
  • Re: Automatically re-connecting to the database.Tim Bunce7 Sep
    • Re: Automatically re-connecting to the database.Rudy Lippan7 Sep