Bug #50401 for DBD-mysql: $dbh->begin_work dies if the connection timed out (mysql_auto

Sun Oct 11 16:51:16 2009 TINITA [...] cpan.org - Ticket created

Subject:

$dbh->begin_work dies if the connection timed out

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

Thu Apr 15 05:26:57 2010 HMBRAND [...] cpan.org - Correspondence added

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

Thu Apr 15 05:27:05 2010 The RT System itself - Status changed from 'new' to 'open'

Sat Apr 17 11:11:39 2010 TINITA [...] cpan.org - Correspondence added

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

Sat Apr 17 11:17:28 2010 TINITA [...] cpan.org - Subject changed from '$dbh->begin_work dies if the connection timed out' to '$dbh->begin_work dies if the connection timed out (mysql_auto_reconnect)'

Sat Apr 17 11:17:28 2010 TINITA [...] cpan.org - Broken in 4.014 added

Sat May 01 10:36:59 2010 CAPTTOFU [...] cpan.org - Correspondence added

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: Show quoted text

> 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

Tue Nov 14 14:55:45 2017 MICHIELB [...] cpan.org - Correspondence added

Ticket migrated to github as https://github.com/perl5-dbi/DBD-mysql/issues/180

Tue Nov 14 14:55:46 2017 MICHIELB [...] cpan.org - Status changed from 'open' to 'resolved'

Bug #50401 for DBD-mysql: $dbh->begin_work dies if the connection timed out (mysql_auto_reconnect)