Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 10.12.2003 10:14:09 von Andreas Haupt

Hello,

I discovered a problem with the option mentioned in the subject. When a
handle is idle for some hours the automatic reconnect does not work at the
first try. Actually it failes with no error message in $dbh->errstr. The
second operation with this handle then works again.

My problem is: also the first operation should not fail.

As a workaround: is there a way to see if a handle has timed out other
than doing an operation and look if it fails?

I'm using Perl 5.8.0 with DBD::mysql 2.9002. MySQL-Server version is
3.23.56.

Greetings
Andreas

--
Andreas Haupt E-Mail: ahaupt@ifh.de
DESY Zeuthen
Platanenallee 6
15738 Zeuthen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 10.12.2003 14:28:03 von jlewis

On Wed, 10 Dec 2003, Andreas Haupt wrote:

> I discovered a problem with the option mentioned in the subject. When a
> handle is idle for some hours the automatic reconnect does not work at the
> first try. Actually it failes with no error message in $dbh->errstr. The
> second operation with this handle then works again.
>
> My problem is: also the first operation should not fail.
>
> As a workaround: is there a way to see if a handle has timed out other
> than doing an operation and look if it fails?
>
> I'm using Perl 5.8.0 with DBD::mysql 2.9002. MySQL-Server version is
> 3.23.56.

I joined the list yesterday to post a similar problem. In my case, I'm
finding that sometimes (usually, but not always) after a connection is
idle for some hours, mysql_auto_reconnect doesn't work at all.

I have the same code running on 2 systems talking to the same mysql
server. i.e. System A runs the client code and server. System B runs the
client code talking to the server on A. System A is a much older system
(Red Hat 7.2, perl-DBD-MySQL-1.2216-4, mysql-server-3.23.58-1.72, perl
5.6.0). With these older versions, I believe auto_reconnect was the
default, as I never had to worry about it (beyond retrying a query when a
query failed due to the connection having been lost).

System B is Fedora Core 1 (perl-5.8.1-92, perl-DBD-MySQL-2.9002-1). On
this system, setting mysql_auto_reconnect does not reliably cause the
client to reconnect when its connection has timed out. When it fails,
queries can be tried again and again, but the client will not reconnect.

------------------------------------------------------------ ----------
Jon Lewis *jlewis@lewis.org*| I route
Senior Network Engineer | therefore you are
Atlantic Net |
_________ http://www.lewis.org/~jlewis/pgp for PGP public key_________

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 10.12.2003 14:28:03 von jlewis

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 10.12.2003 15:44:13 von Rudy Lippan

On Wed, 10 Dec 2003, Andreas Haupt wrote:

> Hello,
>
> I discovered a problem with the option mentioned in the subject. When a
> handle is idle for some hours the automatic reconnect does not work at the
> first try. Actually it failes with no error message in $dbh->errstr. The
> second operation with this handle then works again.
>
> My problem is: also the first operation should not fail.

Can you send me a DBI->trace(9).

> As a workaround: is there a way to see if a handle has timed out other
> than doing an operation and look if it fails?
>

You can ping() to see if the database is still connected.

Rudy.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 10.12.2003 15:44:13 von Rudy Lippan

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 12.12.2003 07:50:18 von Andreas Haupt

On Wed, 10 Dec 2003, Rudy Lippan wrote:

> On Wed, 10 Dec 2003, Andreas Haupt wrote:
>
> > Hello,
> >
> > I discovered a problem with the option mentioned in the subject. When a
> > handle is idle for some hours the automatic reconnect does not work at the
> > first try. Actually it failes with no error message in $dbh->errstr. The
> > second operation with this handle then works again.
> >
> > My problem is: also the first operation should not fail.
>
> Can you send me a DBI->trace(9).

Yes, here it is...

-> trace for DBD::mysql::db (DBI::db=HASH(0x8904c74)~0x8904bd8 9)
thr#8154f38
<- trace= 9 at ../server/VamosServer.pm line 1170 via
.../server/VamosServer.pm line 664
-> prepare for DBD::mysql::db (DBI::db=HASH(0x8904c74)~0x8904bd8 '
SELECT * FROM __Changes
WHERE timestamp > ?
ORDER BY timestamp
') thr#8154f38
dbih_setup_handle(DBI::st=HASH(0x9c50ce4)=>DBI::st=HASH(0x9e 4ef48),
DBD::mysql::st, 9c50bc4, Null!)
dbih_make_com(DBI::db=HASH(0x8904bd8), 8919848, DBD::mysql::st, 208,
0) thr#8154f38
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), Err,
DBI::db=HASH(0x8904bd8)) SCALAR(0x889f39c) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), State,
DBI::db=HASH(0x8904bd8)) SCALAR(0x8260828) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), Errstr,
DBI::db=HASH(0x8904bd8)) SCALAR(0x88e8d3c) (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), TraceLevel,
DBI::db=HASH(0x8904bd8)) 9 (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), FetchHashKeyName,
DBI::db=HASH(0x8904bd8)) 'NAME' (already defined)
dbih_setup_attrib(DBI::st=HASH(0x9e4ef48), HandleError,
DBI::db=HASH(0x8904bd8)) undef (not defined)
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0x9c50ce4) at
/afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm line 705 via
/afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm line 734
-> execute for DBD::mysql::st (DBI::st=HASH(0x9c50ce4)~0x9e4ef48
1071152564.95859) thr#8154f38
-> dbd_st_execute for 09bab394
Binding parameters: SELECT * FROM __Changes
WHERE timestamp > '1071152564.95859'
ORDER BY timestamp

error 0 recorded:
<- dbd_st_execute -2 rows
<- execute= undef at /afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm
line 706 via /afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm line 734
-> errstr in DBD::_::common for DBD::mysql::db
(DBI::db=HASH(0x8904c74)~0x8904bd8) thr#8154f38
<- errstr= '' at /afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm line
711 via /afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm line 734
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x9e4ef48)~INNER)
thr#8154f38
<- DESTROY= undef at /afs/ifh.de/user/a/ahaupt/vamos.rpc/data/Db.pm
line 734

> > As a workaround: is there a way to see if a handle has timed out other
> > than doing an operation and look if it fails?
> >
>
> You can ping() to see if the database is still connected.

OK, I will try this in the meanwhile.

> Rudy.

Thanks and Greetings
Andreas

--
Andreas Haupt E-Mail: ahaupt@ifh.de
DESY Zeuthen
Platanenallee 6
15738 Zeuthen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 12.12.2003 07:50:18 von Andreas Haupt

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 18.12.2003 19:08:01 von Rudy Lippan

On Fri, 12 Dec 2003, Andreas Haupt wrote:

> On Wed, 10 Dec 2003, Rudy Lippan wrote:
>
> > On Wed, 10 Dec 2003, Andreas Haupt wrote:
> >
> > > Hello,
> > >
> > > I discovered a problem with the option mentioned in the subject. When a
> > > handle is idle for some hours the automatic reconnect does not work at the
> > > first try. Actually it failes with no error message in $dbh->errstr. The
> > > second operation with this handle then works again.
> > >
> > > My problem is: also the first operation should not fail.
> >

I just thought of something. Do you have AutoCommit turned off? Because
DBD::mysql will not reconnect to the database when AutoCommit is turned
off (If you are in a tx, it does not make sense to reconnect to the
database).

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 18.12.2003 19:08:01 von Rudy Lippan

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 18.12.2003 22:58:39 von jlewis

On Thu, 18 Dec 2003, Rudy Lippan wrote:

> I just thought of something. Do you have AutoCommit turned off? Because
> DBD::mysql will not reconnect to the database when AutoCommit is turned
> off (If you are in a tx, it does not make sense to reconnect to the
> database).

I found that when investigating this before posting. AutoCommit defaults
to on, and I verified it was on in my case.

------------------------------------------------------------ ----------
Jon Lewis *jlewis@lewis.org*| I route
Senior Network Engineer | therefore you are
Atlantic Net |
_________ http://www.lewis.org/~jlewis/pgp for PGP public key_________

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 18.12.2003 22:58:39 von jlewis

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 19.12.2003 07:49:57 von Andreas Haupt

On Thu, 18 Dec 2003, Rudy Lippan wrote:

> On Fri, 12 Dec 2003, Andreas Haupt wrote:
>
> > On Wed, 10 Dec 2003, Rudy Lippan wrote:
> >
> > > On Wed, 10 Dec 2003, Andreas Haupt wrote:
> > >
> > > > Hello,
> > > >
> > > > I discovered a problem with the option mentioned in the subject. When a
> > > > handle is idle for some hours the automatic reconnect does not work at the
> > > > first try. Actually it failes with no error message in $dbh->errstr. The
> > > > second operation with this handle then works again.
> > > >
> > > > My problem is: also the first operation should not fail.
> > >
>
> I just thought of something. Do you have AutoCommit turned off? Because
> DBD::mysql will not reconnect to the database when AutoCommit is turned
> off (If you are in a tx, it does not make sense to reconnect to the
> database).

Yes, this I read as well. But the behaviour is different with AutoCommit
switched off: in this case no automatic reconnect is done at all. In my
case the reconnect is done but the first operation failes.

I approved it once more. AutoCommit turned on in my case (at least
$dbh->{'mysql_auto_reconnect'} tells me it is).

Greetings
Andreas

--
Andreas Haupt E-Mail: ahaupt@ifh.de
DESY Zeuthen
Platanenallee 6
15738 Zeuthen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Bug with mysql_auto_reconnect in DBD::mysql 2.9002

am 19.12.2003 07:49:57 von Andreas Haupt