$dbh->begin_work dies if the connection timed out (mysql_auto_reconnect) [rt.cpan.org #50401]

[mbeijen]

trafficstars

Migrated from rt.cpan.org#50401 (status was 'open')

Requestors:

• [email protected]

From [email protected] on 2009-10-11 20:51:16:

Hi,

If the connection to the mysql database timed out, I get an error 
message when doing a $dbh->begin_work:

DBD driver has not implemented the AutoCommit attribute at /home/perl/
lib/x86_64-linux-gnu-thread-multi/DBI.pm line 1702

I'm using DBI 1.609, perl 5.10.0

It can be reproduced by setting the "wait_timeout" in the mysql server 
to 1 minute, for example, and then try the following code:

use DBI;
my $dbh = DBI->connect("dbi:mysql:...", $user, $pass, { AutoCommit => 
1 });
# just to show that begin_work works normally
$dbh->begin_work;
$dbh->do(qq{DELETE FROM something WHERE id = 123});
$dbh->commit;

sleep 70;
# but not after a connection timeout
$dbh->begin_work;


I have severe problems with my mod_perl application because after the 
timeout, every request which does an insert as a first statement dies 
and therefor produces a 500 server error. This bug seems to be existing 
at least since 4.00 (or maybe something in DBI changed).
The error message is not appearing if I do a select or any other 
statement directly before the begin_work.

Normally the error message should be that the server connection has 
gone away, which the code is expecting and then doing a reconnect.

regards,
tina

From [email protected] on 2010-04-15 09:26:57:

What is your server version?

Your error reminded me of some code in my own module somewhere:
--8<---
    elsif ($dbt =~ m/^[?M]$/ and (
           exists $ENV{MYSQLDB} or
           exists $ENV{DBI_DSN} && $ENV{DBI_DSN} =~ m/:mysql\b/i)) {
#       delete $attr{AutoCommit};       # MySQL still croaks on this one
        my $db  = $ENV{MYSQLDB} // "test";
-->8---

As you can see, the line is now commented out, which indicates that it 
does not happen anymore (with me). And I do have the same version of 
DBD::mysql:

$ perl -MV=DBD::mysql
DBD::mysql
        /pro/lib/perl5/site_perl/5.10.1/x86_64-linux/DBD/mysql.pm: 4.013

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (x86_64) using 
readline 5.2

FWIW I am NOT a mysql user, so I only post what I remember :)

From [email protected] on 2010-04-17 15:11:39:

my mysql server version is 5.0.51a-24

I managed to create a reproducing script. I only can reproduce it if 
mysql_auto_reconnect is active (or $ENV{MOD_PERL}, which enables 
mysql_auto_reconnect automatically).

here's the working code:

my $dbh = DBI->connect($dsn, $u, $p, { mysql_auto_reconnect => 1 });
$dbh->do(q{set @@wait_timeout=2});
sleep 3;
print "starting\n";
my $sth = $dbh->prepare("select 23");
$sth->execute();
my ($n) = $sth->fetchrow_array;
print "n=$n\n";
print "finished\n";
__END__
starting
n=23
finished


and here the code with begin_work:

my $dbh = DBI->connect($dsn, $u, $p, { mysql_auto_reconnect => 1 });
$dbh->do(q{set @@wait_timeout=2});
sleep 3;
print "starting\n";
$dbh->begin_work;
print "finished\n";
__END__
starting
DBD driver has not implemented the AutoCommit attribute at /.../lib/
i486-linux-gnu-thread-multi/DBI.pm line 1702.


I can see where it fails in dbdimp.c (in dbd_db_STORE_attrib() at 
mysql_autocommit()) but I can't change the code so that the reconnect 
happens.

My solution now is - I'm using DBIx::Class - to explicitly set 
mysql_auto_reconnect to 0 in the DBIC connect.
But I think it's still a bug, I just don't now where it should be 
fixed, in dbdimp.c or somewhere in DBI.pm.

regards,
tina

From [email protected] on 2010-05-01 14:36:59:

Hi there!

I see that if auto_reconnect is set to 0, it works. However, if I were
to change the driver to have auto_reconnect to 0, it would break far
many more installations-- particularly web - than it would fix. I once
did this and it created a lot of grief. I think maybe documenting this
may be the way to reach a happy medium.

On Sat Apr 17 11:11:39 2010, TINITA wrote:
> my mysql server version is 5.0.51a-24
> 
> I managed to create a reproducing script. I only can reproduce it if 
> mysql_auto_reconnect is active (or $ENV{MOD_PERL}, which enables 
> mysql_auto_reconnect automatically).
> 
> here's the working code:
> 
> my $dbh = DBI->connect($dsn, $u, $p, { mysql_auto_reconnect => 1 });
> $dbh->do(q{set @@wait_timeout=2});
> sleep 3;
> print "starting\n";
> my $sth = $dbh->prepare("select 23");
> $sth->execute();
> my ($n) = $sth->fetchrow_array;
> print "n=$n\n";
> print "finished\n";
> __END__
> starting
> n=23
> finished
> 
> 
> and here the code with begin_work:
> 
> my $dbh = DBI->connect($dsn, $u, $p, { mysql_auto_reconnect => 1 });
> $dbh->do(q{set @@wait_timeout=2});
> sleep 3;
> print "starting\n";
> $dbh->begin_work;
> print "finished\n";
> __END__
> starting
> DBD driver has not implemented the AutoCommit attribute at /.../lib/
> i486-linux-gnu-thread-multi/DBI.pm line 1702.
> 
> 
> I can see where it fails in dbdimp.c (in dbd_db_STORE_attrib() at 
> mysql_autocommit()) but I can't change the code so that the reconnect 
> happens.
> 
> My solution now is - I'm using DBIx::Class - to explicitly set 
> mysql_auto_reconnect to 0 in the DBIC connect.
> But I think it's still a bug, I just don't now where it should be 
> fixed, in dbdimp.c or somewhere in DBI.pm.
> 
> regards,
> tina