On Mon, Sep 06, 2004 at 02:01:07PM -0400, Rudy Lippan wrote:
>
> 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?
Yes. That's what I'd recommend. Personally I'd disable it by default
and make applications ask for it explicitly if they want it.
It's just not safe enough to leave on.
> 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.
http://bugs.mysql.com/bug.php?id=2532
> 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?
I think $dbh->ping should return false if the connection-id (thread-id) changes.
(Perhaps return "0", rather than "" or undef, in this case.)
> 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?
Not enough to worry about. Correctness is more important here.
Tim.
p.s. Did the last two items in http://www.mail-archive.com/[EMAIL
PROTECTED]/msg02136.html
get done? (I've no time to look.)
