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=ms...ie.nctu.edu.tw

Rudy, --

my two cents on this,

On Mon, Sep 06, 2004 at 02:01:07PM -0400, Rudy Lippan wrote:
In mod_perl environment auto-reconnect is rather evil then useful,

it also breaks whole session-depended environment, such as
user @variables, established locks, and (possible) semaphores, ...

Only application has benefits from auto-reconnect I could see
is interactive one, similar to mysql command-line client,
where human can decide what to do next.

Other then that, disconnect should throw red flag to apps
to re-establish lots of program context, so
non-interactive apps (including cgi-s) should not rely
on this feature, and have to keep it disabled.

--w

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=ms...ie.nctu.edu.tw

On Mon, Sep 06, 2004 at 02:01:07PM -0400, Rudy Lippan wrote:

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.

http://bugs.mysql.com/bug.php?id=2532

I think $dbh->ping should return false if the connection-id (thread-id) changes.

(Perhaps return "0", rather than "" or undef, in this case.)

Not enough to worry about. Correctness is more important here.

Tim.

p.s. Did the last two items in http://www.mail-archive.com/dbi-dev .../msg02136.html
get done? (I've no time to look.)

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=ms...ie.nctu.edu.tw

On Mon, 6 Sep 2004, Tim Bunce wrote:

Ok. Easy enough. Right now it is only defaulting to on when it sees
$ENV{GATEWAY_INTERFACE} || $ENV{MOD_PERL}, so I will not turn it on when
serverside-prepare is true.


Ah, IC.

That sounds good, so I'll do that. It is not 100%, but should work.

Agreed.

In 2.9002
* Changed the default behaviour of mysql_found_rows, so now
'UPDATE table set field=?' will return the number of rows matched
and not the number of rows physically changed. You can get the old
behaviour back by adding "mysql_found_rows=0" to the dsn passed
to connect.

And as for the connect attributes having to be passed in using the dsn, I don't
think that this is the case. Right now, everything in the dsn is added to the
dbh's attribute hash before new_dbh is called and _login is reading off of
the dbh's attribute hash when deciding what to do.


Thank you,

Rudolf.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=ms...ie.nctu.edu.tw