Bug #7698 for DBD-mysql: Know but Unfixed Major Bug

Sat Sep 18 23:30:48 2004 Guest - Ticket created

Subject:

Know but Unfixed Major Bug

It seems to be a widespread error. I get a "MySQL has gone away error" in my script after a few "execute" calls, under a FreeBSD installation of DBD::mysql. The exact same script ran perfectly under an older version of DBD::mysql in Linux. The problem is also mentioned here: http://mailwatch.sourceforge.net/faq.html

Sun Oct 10 15:22:32 2004 Guest - Correspondence added

From:

Colin Alston

[guest - Sat Sep 18 23:30:48 2004]: Show quoted text

> It seems to be a widespread error. I get a "MySQL has gone away error" > in my script after a few "execute" calls, under a FreeBSD > installation of DBD::mysql. The exact same script ran perfectly > under an older version of DBD::mysql in Linux. The problem is also > mentioned here: > http://mailwatch.sourceforge.net/faq.html

Indeed. The question remains, Why on EARTH is this abomination still being packaged?!?!?! Its compleatly BROKEN!

Sat Jan 29 19:36:25 2005 Guest - Correspondence added

as far as I observed (using freebsd) this is caused, after the server disconnect (server variable interactive_timeout). a workaround is to increase this on the server. using the auto-reconnect settings via DBI, does not fix the problem. At least not, if you connect to the server via TCP/IP (even to own host). Auto-reconnect seems to work, if the connection uses local/unix socket

Mon Feb 07 05:30:04 2005 Guest - Correspondence added

From:

spurkis [...] cpan.org

[guest - Sat Jan 29 19:36:25 2005]: Show quoted text

> as far as I observed (using freebsd) this is caused, after the server > disconnect (server variable interactive_timeout). > > a workaround is to increase this on the server. > > using the auto-reconnect settings via DBI, does not fix the problem. At > least not, if you connect to the server via TCP/IP (even to own host). > Auto-reconnect seems to work, if the connection uses local/unix socket

Thanks for pointing this out -- it seems this is why my DB failover code works on Linux, but not on Darwin. It would be good to be able to rely on auto_reconnect doing it's job... does anyone know if this will be fixed in the next release?

Wed Feb 09 23:00:07 2005 rlippan [...] remotelinux.com - Correspondence added

[guest - Mon Feb 7 05:30:04 2005]: Show quoted text

> [guest - Sat Jan 29 19:36:25 2005]: >

> > as far as I observed (using freebsd) this is caused, after the

> server

> > disconnect (server variable interactive_timeout). > > > > a workaround is to increase this on the server. > > > > using the auto-reconnect settings via DBI, does not fix the problem.

> At

> > least not, if you connect to the server via TCP/IP (even to own

> host).

> > Auto-reconnect seems to work, if the connection uses local/unix

> socket > > Thanks for pointing this out -- it seems this is why my DB failover > code works on Linux, but > not on Darwin. > > It would be good to be able to rely on auto_reconnect doing it's > job... does anyone know if > this will be fixed in the next release?

auto-reconect seems to work fine for me, do you have a test script where it fails? If so could you send it along with a trace(9). And don't forget that auto_reconnect will not re-connect when AutoCommit is turned off. In this case, you should either turn AutoCommit on or increase the timeout on your server.

Fri Mar 04 13:58:37 2005 rlippan [...] remotelinux.com - Status changed from 'new' to 'stalled'

Wed Jun 29 07:33:59 2005 Guest - Correspondence added

From:

Martin

Hi, I added the comment on "Sat Jan 29 19:36:25 2005": Show quoted text

>as far as I observed (using freebsd) this is caused, after the server >disconnect (server variable interactive_timeout). > >a workaround is to increase this on the server. > >using the auto-reconnect settings via DBI, does not fix the problem. At >least not, if you connect to the server via TCP/IP (even to own host). >Auto-reconnect seems to work, if the connection uses local/unix so

from my latest test with DBI 1.48 and DBD::mysql 2.9007 it looks like there is no diff between local socket and tcp/ip anymore. I couldnt find how reconnect should work exactly (how quick it should kick in). From the test below, one query is lost. DBD::mysql detects the connection is lost, and returns the error for the current query. It does fix it for the next query. perl -wle 'use DBI; $dbh = DBI->connect("dbi:mysql:dbname=test", "root", undef); $dbh->{mysql_auto_reconnect} = 1; print $dbh->do("select 1"); $id = $dbh->selectrow_array("select connection_id()"); print $id; print $dbh->do("kill $id"); print "---"; print $dbh->do("select 1"); print "---"; print $dbh->do("select 1"); ' 1 4166 0E0 --- DBD::mysql::db do failed: Lost connection to MySQL server during query at -e line 1. Use of uninitialized value in print at -e line 1. --- 1 As you can see the first query, after the "kill", returns undef instead of 1. (freebsd 4.11 / mysql 4.1.11) I am not sure what the expected behaviour is. So it might be a question of documentation. If the cost of fixing it is to run a ping in front of each query, I wouldn't recommand this as a solution, if the driver can repeat, only those queries on which the error occurs (so use the query itself to determine the connection state), this would be nice.

Wed Jun 29 07:33:59 2005 The RT System itself - Status changed from 'stalled' to 'open'

Thu Nov 10 07:19:04 2005 Guest - Correspondence added

From:

mortenb

[guest - Wed Jun 29 07:33:59 2005]: Show quoted text

> perl -wle 'use DBI; > $dbh = DBI->connect("dbi:mysql:dbname=test", "root", undef); > $dbh->{mysql_auto_reconnect} = 1; > print $dbh->do("select 1"); > $id = $dbh->selectrow_array("select connection_id()"); print $id; > print > $dbh->do("kill $id"); > print "---"; print $dbh->do("select 1"); > print "---"; print $dbh->do("select 1"); ' > > 1 > 4166 > 0E0 > --- > DBD::mysql::db do failed: Lost connection to MySQL server during query > at -e line 1. > Use of uninitialized value in print at -e line 1. >

Hi Is this bug fixed?, I'm unable to reproduce it with the script above. Running standard RHEL4.1 - mysql-4.1.12-3.RHEL4.1, DBD::Mysql 2.9004 1 8341 0E0 --- 1 --- 1 Could someone please verify that this works with newer mysql's and DBD::mysql thanks

Thu Mar 02 09:32:26 2006 Guest - Correspondence added

From:

Tom Kulmacz

Hey, I'm also suffering from this bug... or so it appears. I've attached a level 9 trace... On Wed Feb 09 23:00:07 2005, RUDY wrote: Show quoted text

> [guest - Mon Feb 7 05:30:04 2005]: >

> > [guest - Sat Jan 29 19:36:25 2005]: > >

> > > as far as I observed (using freebsd) this is caused, after the

> > server

> > > disconnect (server variable interactive_timeout). > > > > > > a workaround is to increase this on the server. > > > > > > using the auto-reconnect settings via DBI, does not fix the

problem. Show quoted text

> > At

> > > least not, if you connect to the server via TCP/IP (even to own

> > host).

> > > Auto-reconnect seems to work, if the connection uses local/unix

> > socket > > > > Thanks for pointing this out -- it seems this is why my DB failover > > code works on Linux, but > > not on Darwin. > > > > It would be good to be able to rely on auto_reconnect doing it's > > job... does anyone know if > > this will be fixed in the next release?

> > auto-reconect seems to work fine for me, do you have a test script

where Show quoted text

> it fails? If so could you send it along with a trace(9). > > And don't forget that auto_reconnect will not re-connect when

AutoCommit Show quoted text

> is turned off. In this case, you should either turn AutoCommit on or > increase the timeout on your server.

-> prepare for DBD::mysql::db (DBI::db=HASH(0x9ba07b0)~0x9bcd4d0 'SELECT * FROM adconfig') dbih_setup_handle(DBI::st=HASH(0x9bc9be8)=>DBI::st=HASH(0x9bd8c8c), DBD::mysql::st, 9bc9bf4, Null!) dbih_make_com(DBI::db=HASH(0x9bcd4d0), 9b95c08, DBD::mysql::st, 220, 0) thr#0 dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), Err, DBI::db=HASH(0x9bcd4d0)) SCALAR(0x9a4ed28) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), State, DBI::db=HASH(0x9bcd4d0)) SCALAR(0x9a4ed88) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), Errstr, DBI::db=HASH(0x9bcd4d0)) SCALAR(0x9a4ed58) (already defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), TraceLevel, DBI::db=HASH(0x9bcd4d0)) 9 (already defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), FetchHashKeyName, DBI::db=HASH(0x9bcd4d0)) 'NAME' (already defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), HandleSetErr, DBI::db=HASH(0x9bcd4d0)) undef (not defined) dbih_setup_attrib(DBI::st=HASH(0x9bd8c8c), HandleError, DBI::db=HASH(0x9bcd4d0)) undef (not defined) <- prepare= DBI::st=HASH(0x9bc9be8) at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 58 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 -> execute for DBD::mysql::st (DBI::st=HASH(0x9bc9be8)~0x9bd8c8c) -> dbd_st_execute for 09bd8e54 ---> parse_params with statement SELECT * FROM adconfig num params 0 mysql_st_internal_execute MySQL server has gone away error 2006 recorded: MySQL server has gone away <- dbd_st_execute returning imp_sth->row_num 18446744073709551614 !! ERROR: 2006 'MySQL server has gone away' (err#0) <- execute= undef at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 59 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 DBD::mysql::st execute failed: MySQL server has gone away at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 59, <GEN1> line 1. !! ERROR: 2006 CLEARED by call to fetchrow_hashref method -> fetchrow_hashref in DBD::_::st for DBD::mysql::st (DBI::st=HASH(0x9bc9be8)~0x9bd8c8c) >> FETCH DISPATCH (DBI::st=HASH(0x9bd8c8c) rc2/1 @2 g0 ima404 pid#4653) at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 1 -> FETCH for DBD::mysql::st (DBI::st=HASH(0x9bd8c8c)~INNER 'NAME') -> dbd_st_FETCH_attrib for 09bd8e54, key NAME statement contains no result error 4 recorded: statement contains no result ERROR: 4 'statement contains no result' (err#0) 1 <- FETCH= undef at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 >> fetch DISPATCH (DBI::st=HASH(0x9bd8c8c) rc2/1 @1 g0 ima0 pid#4653) at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 !! ERROR: 4 CLEARED by call to fetch method 1 -> fetch for DBD::mysql::st (DBI::st=HASH(0x9bd8c8c)~INNER) -> dbd_st_fetch for 09bd8e54, chopblanks 0 fetch() without execute() error 19 recorded: fetch() without execute() !! ERROR: 19 'fetch() without execute()' (err#0) 1 <- fetch= undef row-1 at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 !! ERROR: 19 'fetch() without execute()' (err#0) <- fetchrow_hashref= undef row-1 at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 60, <GEN1> line 1. !! ERROR: 19 CLEARED by call to finish method -> finish for DBD::mysql::st (DBI::st=HASH(0x9bc9be8)~0x9bd8c8c) <- finish= 1 at /home/sexinfo/public_html/cgi-bin/am/AdDb.pm line 61 via /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x9bd8c8c)~INNER) <- DESTROY= undef at /home/sexinfo/public_html/cgi-bin/am/adcycled line 469 DBI::db=HASH(0x859e5f4) trace level set to 0x0/9 (DBI @ 0x0/0) in DBI 1.50-nothread (pid 4866)

Fri Sep 15 13:17:58 2006 CAPTTOFU [...] cpan.org - Correspondence added

I'm not sure what the behaviour is supposed to be. I get this: radha:~/perl patg$ ./bug7698.pl DBD::mysql::db do failed: Lost connection to MySQL server during query at ./bug7698.pl line 9. 1414---1---1 Is it supposed to automatically reconnect? This is on OSX 10.4.7

Mon Oct 02 18:10:37 2006 CAPTTOFU [...] cpan.org - Status changed from 'open' to 'resolved'