Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 17:10:56 von Tim Cutts

I note that this was discussed in November, and Rudy asked for some
example code which demonstrates the problem. Here is some from my
site, where we are seeing the same problem (dropped connections after 8
hours):

use strict;
use DBI;

print "Connecting\n";
my $db =
DBI->connect("DBI:mysql:database=blahblah;host=xxxx;port=nnn n",
"someone",
"secret",
{RaiseError => 1});

die "Could not connect" unless (defined($db));

sleep(30000);

eval {
# I imagine any valid SQL for your database will work here
my $st = $db->prepare("select count(*) from contig");
$st->execute;
my $res = $st->fetchrow_arrayref;
if ($res) {
printf "Rows in contig (after long connection) = %d\n",
$res->[0];
}
};

$@ and die "Could not get data because: '$@\n";

$db->disconnect;

Server: 4.0.14 running on AlphaServer ES45, Tru64 5.1B

Client: perl 5.8.0
$DBI::VERSION = 1.37
$DBD::mysql::VERSION = 2.9002
Clients running on both Linux and Alpha machines.

Here are all the timeout variables on the instance in question:

>show variables like '%timeout';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 267820 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 267820 |
+--------------------------+--------+
8 rows in set (0.02 sec)

so there shouldn't be a timeout after 8 hours of activity...

and packet size is nice and large:

>show variables like '%packet';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| max_allowed_packet | 16776192 |
+--------------------+----------+

If the same client connects to a MySQL 3.x instance, the timeout does
not occur, and everything works. Unsurprisingly, the users are
beginning to be reluctant to move to MySQL 4...

Does anyone have any other views as to what on earth is going on here?

Thanks...

Tim

--
Dr Tim Cutts
Informatics Systems Group
Wellcome Trust Sanger Institute
Hinxton, Cambridge, CB10 1SA, UK

--
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: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 17:32:44 von Jochen Wiedmann

Tim Cutts wrote:

> where we are seeing the same problem (dropped connections after 8 hours):

Most probably you are not observing *dropped* connections but are using
a current version of DBD::mysql, which is not *reconnecting* automatically,
if the *server* drops a connection after 8 hours. Beginning with 2.9002
you need to enable the mysql_auto_reconnect flag. See the drivers
documentation for details.

Jochen

--
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: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 17:32:44 von Jochen Wiedmann

Re: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 17:41:37 von Tim Cutts

On 17 Feb 2004, at 16:32, Jochen Wiedmann wrote:

> Tim Cutts wrote:
>
>> where we are seeing the same problem (dropped connections after 8
>> hours):
>
> Most probably you are not observing *dropped* connections but are using
> a current version of DBD::mysql, which is not *reconnecting*
> automatically,
> if the *server* drops a connection after 8 hours. Beginning with 2.9002
> you need to enable the mysql_auto_reconnect flag. See the drivers
> documentation for details.

Yes, I realise that, but *why* is the server dropping the connection
after 8 hours? Our settings of the wait_timeout and
interactive_timeout should enable connections to live for about a month
(and indeed on MySQL 3 servers, that is the case). I'm slightly scared
of the auto_reconnect because of the issues with lost locks.

Tim

PS. Don't ask why we need connections that live this long... the
programmers insist on it. I, the mere sysadmin, have to live with it.

--
Dr Tim Cutts
Informatics Systems Group
Wellcome Trust Sanger Institute
Hinxton, Cambridge, CB10 1SA, UK

--
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: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 17:41:37 von Tim Cutts

Re: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 18:16:20 von Rudy Lippan

On Tue, 17 Feb 2004, Tim Cutts wrote:

> Subject: Re: Odd timeouts talking to MySQL 4.0, but not 3.x
>
>
> On 17 Feb 2004, at 16:32, Jochen Wiedmann wrote:
>
> > Tim Cutts wrote:
> >
> >> where we are seeing the same problem (dropped connections after 8
> >> hours):
> >
> > Most probably you are not observing *dropped* connections but are using
> > a current version of DBD::mysql, which is not *reconnecting*
> > automatically,
> > if the *server* drops a connection after 8 hours. Beginning with 2.9002

2.9002 has a memory leek in bind_param()/execute(), so you might want to
upgrade to 2.9003 if you are going to be keeping connexions around for a
long time.

> > you need to enable the mysql_auto_reconnect flag. See the drivers
> > documentation for details.
>
> Yes, I realise that, but *why* is the server dropping the connection
> after 8 hours? Our settings of the wait_timeout and
> interactive_timeout should enable connections to live for about a month
> (and indeed on MySQL 3 servers, that is the case). I'm slightly scared

If using the same version of DBD::mysql, 4.x drops after 8 hours, which
happens to be the default timeout for mysql servers, and 3.x does not, I'd
be inclined to look to the server as the source of the problem.

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: Odd timeouts talking to MySQL 4.0, but not 3.x

am 17.02.2004 18:16:20 von Rudy Lippan

Re: Odd timeouts talking to MySQL 4.0, but not 3.x

am 24.02.2004 13:53:14 von Tim Cutts

On 17 Feb 2004, at 16:32, Jochen Wiedmann wrote:

> Tim Cutts wrote:
>
>> where we are seeing the same problem (dropped connections after 8
>> hours):
>
> Most probably you are not observing *dropped* connections but are using
> a current version of DBD::mysql, which is not *reconnecting*
> automatically,
> if the *server* drops a connection after 8 hours. Beginning with 2.9002
> you need to enable the mysql_auto_reconnect flag. See the drivers
> documentation for details.

The problem is now resolved, and it was a server configuration issue,
albeit quite a difficult one to spot.

The wait_timeout variable was not being set in the server's my.cnf,
although interactive_timeout was.

SHOW VARIABLES LIKE '%timeout';

shows session variable values, and lists the value of wait_timeout as
30 days, the same as our setting of interactive_timeout.

However, using SHOW GLOBAL VARIABLES revealed what the global setting
for wait_timeout was, and it was this that was being obeyed by the
server.

Thanks for your help, chaps, and I'm sorry for troubling you when it
wasn't actually a perl problem in the end...

Tim

--
Dr Tim Cutts
Informatics Systems Group
Wellcome Trust Sanger Institute
Hinxton, Cambridge, CB10 1SA, UK

--
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: Odd timeouts talking to MySQL 4.0, but not 3.x

am 24.02.2004 13:53:14 von Tim Cutts

On 17 Feb 2004, at 16:32, Jochen Wiedmann wrote:

> Tim Cutts wrote:
>
>> where we are seeing the same problem (dropped connections after 8
>> hours):
>
> Most probably you are not observing *dropped* connections but are using
> a current version of DBD::mysql, which is not *reconnecting*
> automatically,
> if the *server* drops a connection after 8 hours. Beginning with 2.9002
> you need to enable the mysql_auto_reconnect flag. See the drivers
> documentation for details.

The problem is now resolved, and it was a server configuration issue,
albeit quite a difficult one to spot.

The wait_timeout variable was not being set in the server's my.cnf,
although interactive_timeout was.

SHOW VARIABLES LIKE '%timeout';

shows session variable values, and lists the value of wait_timeout as
30 days, the same as our setting of interactive_timeout.

However, using SHOW GLOBAL VARIABLES revealed what the global setting
for wait_timeout was, and it was this that was being obeyed by the
server.

Thanks for your help, chaps, and I'm sorry for troubling you when it
wasn't actually a perl problem in the end...

Tim

--
Dr Tim Cutts
Informatics Systems Group
Wellcome Trust Sanger Institute
Hinxton, Cambridge, CB10 1SA, UK

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