Mailing List Archive

Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup")
Quoting Perrin Harkins <perrin@elem.com>:
> On Mon, Jun 30, 2008 at 4:54 AM, Tobias Kremer <list@funkreich.de> wrote:
> > We never fork and I thought that Apache::DBI takes care of checking if a
> > connection went stale by utilizing DBI's/DBD::mysql's ping() method?
> It does, but it can't stop you from doing things like putting a
> database handle in a global during startup and trying to use it later.

Ok, I narrowed it down to the database connection initiated during server
startup. As soon as I remove it the errors vanish completely. But I don't
understand why this is causing a problem because Apache::DBI is supposed to not
cache connections made during server startup - it even correctly issues a
warning.

Here are some snippets to illustrate what I'm doing:

-----------------------------------
ApacheHandler.pm
-----------------------------------
use Apache::DBI;
{
package HTML::Mason::Commands;
use vars qw/ $thefoo /;
}
my $foo = My::Foo->new();
sub handler {
$HTML::Mason::Commands::thefoo = $foo;
}

-----------------------------------
My/Foo.pm
-----------------------------------
sub new {
my $dbh = My::Database::dbh();
my $result = $dbh->selectall_arrayref( ... );
# create an object of e.g. My::Bar initialized with row data
# and store it in $self. $dbh is never stored somewhere!
$dbh->disconnect();
}

-----------------------------------
My/Database.pm
-----------------------------------
use DBI;
sub dbh { DBI->connect( ... ) }


Any ideas? Thanks a lot!

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Mon, Jun 30, 2008 at 9:28 AM, Tobias Kremer <list@funkreich.de> wrote:
> Ok, I narrowed it down to the database connection initiated during server
> startup. As soon as I remove it the errors vanish completely.

Good, that's major progress.

> Here are some snippets to illustrate what I'm doing:

I don't see anything in this code, but you're not really showing us
much here. I think you'll need to try commenting out parts of it
until you find which part breaks it. I'd start with that
selectall_arrayref that you store.

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Perrin Harkins <perrin@elem.com>:
> I don't see anything in this code, but you're not really showing us
> much here. I think you'll need to try commenting out parts of it
> until you find which part breaks it. I'd start with that
> selectall_arrayref that you store.

I can reproduce the problem with the following minimal setup:

package LostHandler;
use Apache::DBI;
use DBI;
use LostDatabase;
use LostFoo;
use vars qw( $dbh $thefoo );
my $foo = LostFoo->new();
sub handler {
$thefoo = $foo;
$dbh = LostDatabase::dbh();
$dbh->do( "SELECT 1" );
return 200;
}
1;

-----------------------

package LostFoo;
use LostDatabase;
sub new {
my $self = {};
my $dbh = LostDatabase::dbh();
$dbh->do( "SELECT 2" );
$dbh->disconnect();
bless $self, shift;
}
1;

-----------------------

package LostDatabase;
sub dbh { DBI->connect('dbi:mysql:test','','') }
1;

Hammering this with "ab -n 1000 -c 30" gives me the "Lost connection" and/or
"DBD driver has not implemented the AutoCommit attribute" error after the first
couple of requests. Removing the lines:

my $foo = LostFoo->new();
$thefoo = $foo;

makes the error disappear completely.

I've had this problem on many systems and never really bothered because it only
shows up on our rather busy system after the server gets restarted but somehow
it gives me headaches to not know where it is coming from ...

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Tobias Kremer wrote:

> use vars qw( $dbh $thefoo );

Why are you storing the DB handle in a global variable?

If you do that then Apache::DBI can't help you if the connection goes away.

--
Michael Peters
Plus Three, LP
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Michael Peters <mpeters@plusthree.com>:
> Tobias Kremer wrote:
> > use vars qw( $dbh $thefoo );
> Why are you storing the DB handle in a global variable?
> If you do that then Apache::DBI can't help you if the connection goes away.

To make this variable available to all Mason components. Theoretically, this
shouldn't be a problem because I'm (re-)connecting on every request in the
handler() subroutine not once during startup. Actually it works perfectly as
soon as you remove the one-time connection made during startup (LostFoo) which
just preloads some data from the database. According to the docs Apache::DBI
should automatically avoid caching this connection.

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Mon, Jun 30, 2008 at 11:02 AM, Tobias Kremer <list@funkreich.de> wrote:
> According to the docs Apache::DBI
> should automatically avoid caching this connection.

It's not Apache::DBI that's caching it -- you're caching it. Don't
put a database handle in a global before you fork. It will stay, and
there's nothing Apache::DBI can do about it.

You are probably not reusing this connection, but you don't need to.
It will sit there, open, and eventually time out, which will cause the
error messages you see and also trigger cleanup on the server side.
The server side cleanup may hurt other open connections from the same
process.

So, never put a database handle in a global during startup.

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Tobias Kremer wrote:
> Quoting Michael Peters <mpeters@plusthree.com>:
>> Why are you storing the DB handle in a global variable?
>> If you do that then Apache::DBI can't help you if the connection goes away.
>
> To make this variable available to all Mason components.

Then use a method to do this, not a global variable. Have that method call
DBI->connect every time you want to get the handle. If Apache::DBI has already
made the connection it will return a cached handle for you. No need to try
caching it on your own. It will only cause problems because it means you're not
using Apache::DBI's cache.

> Theoretically, this
> shouldn't be a problem because I'm (re-)connecting on every request in the
> handler() subroutine not once during startup.

You're trying to do too much work. Let Apache::DBI worry about connecting,
reconnecting, caching, etc.

> Actually it works perfectly as
> soon as you remove the one-time connection made during startup (LostFoo) which
> just preloads some data from the database.

Except for all those timeout errors you mention :)

> According to the docs Apache::DBI
> should automatically avoid caching this connection.

Apache::DBI doesn't store the cached handle, but your application does. That's
the problem. Apache::DBI can't get rid of that handle if you're storing it
someplace and then trying to use it later.

--
Michael Peters
Plus Three, LP
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On 30.06.2008, at 17:10, Perrin Harkins wrote:
> It's not Apache::DBI that's caching it -- you're caching it. Don't
> put a database handle in a global before you fork. It will stay, and
> there's nothing Apache::DBI can do about it.

Could you please show me the exact line in my example in which I put
the database handle in a
global during startup? To me it looks like I'm correctly _declaring_ a
global variable ($dbh) on
server startup, but the connect happens in the handler() subroutine.
There's even an example in
the Mason docs that recommends this approach:
http://masonhq.com/?FAQ:ServerConfiguration#h-how_do_i_connect_to_a_database_from_mason_

And this all works great - without _any_ errors at all! It's not until
I start adding my preloading
stuff which fetches some stuff from the database during startup with a
supposedly independent not-cached
handle which should have got nothing to do with my global $dbh that
the errors crop up.

Thank you very much! :)

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Mon, Jun 30, 2008 at 1:40 PM, Tobias Kremer <list@funkreich.de> wrote:
> Could you please show me the exact line in my example in which I put the
> database handle in a
> global during startup?

On a closer look, you're not. You are keeping around your $foo
closure variable in handler(), as well as putting it in a global.
It's not obvious why that causes this problem. If you want to
determine whether Apache::DBI is malfunctioning for you in some way,
I'd suggest just removing it and seeing if the problem goes away.
Your test app should work fine without it.

> There's even an example in
> the Mason docs that recommends this approach

I know, but it's still a bad idea, IMO. The Mason docs advise a
similar approach for using sessions and people are constantly having
problems with their session object not getting cleaned up.

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Perrin Harkins <perrin@elem.com>:
> On a closer look, you're not. You are keeping around your $foo
> closure variable in handler(), as well as putting it in a global.
> It's not obvious why that causes this problem. If you want to
> determine whether Apache::DBI is malfunctioning for you in some way,
> I'd suggest just removing it and seeing if the problem goes away.
> Your test app should work fine without it.

Removing Apache::DBI makes the errors go away. Using two different connection
strings for my initial connection during startup and the subsequent connections
in the handler() method also works. So everything looks like the connection made
during startup is indeed re-used somehow, although Apache::DBI correctly reports
that it won't cache it. Maybe now's the time to file a bug report ...

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Tue, Jul 1, 2008 at 3:42 AM, Tobias Kremer <list@funkreich.de> wrote:
> Removing Apache::DBI makes the errors go away.

Ok. First, check that you're on the latest version. Then, turn on
the debug flag and see if it thinks it is reusing the startup
connection or not.

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Tue, Jul 1, 2008 at 9:56 AM, Tobias Kremer <list@funkreich.de> wrote:
> Yes, I'm using the latest 1.07 release. I already had the debug flag on and it's
> correctly telling me that it's "skipping connection during server startup".

Yes, but what does it tell you on the first connection AFTER startup?
It should say whether it's making a new connection or not.

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Perrin Harkins <perrin@elem.com>:
> Ok. First, check that you're on the latest version. Then, turn on
> the debug flag and see if it thinks it is reusing the startup
> connection or not.

Yes, I'm using the latest 1.07 release. I already had the debug flag on and it's
correctly telling me that it's "skipping connection during server startup".

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Perrin Harkins <perrin@elem.com>:
> Yes, but what does it tell you on the first connection AFTER startup?
> It should say whether it's making a new connection or not.

Here's the complete debug output which suggests that the connection during
startup is reused for the first request.

On server start:
----------------
20097 Apache::DBI skipping connection during server startup, read the docu !!
20097 Apache::DBI push PerlCleanupHandler
20097 Apache::DBI need ping: yes
20097 Apache::DBI new connect to 'foo:bar...'
20097 Apache::DBI disconnect (overloaded)

On first request:
----------------
20099 Apache::DBI need ping: yes
20099 Apache::DBI already connected to 'foo:bar...'
20099 Apache::DBI PerlCleanupHandler

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Tue, Jul 1, 2008 at 10:08 AM, Tobias Kremer <list@funkreich.de> wrote:
> On server start:
> ----------------
> 20097 Apache::DBI skipping connection during server startup, read the docu !!
> 20097 Apache::DBI push PerlCleanupHandler
> 20097 Apache::DBI need ping: yes
> 20097 Apache::DBI new connect to 'foo:bar...'
> 20097 Apache::DBI disconnect (overloaded)

That looks like two different connect attempts. The line following
the "skipping" part is a return, and then it must come back in later.

How are you loading this? With a PerlModule call? Can you try
loading it from a Perl section like this?

<Perl>
use MyModule;
</Perl>

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Perrin Harkins <perrin@elem.com>:
> How are you loading this? With a PerlModule call? Can you try
> loading it from a Perl section like this?
> <Perl>
> use MyModule;
> </Perl>

Wow, it seems that this fixes the problem! At least with my minimal application.

Here's the debug output which looks quite promising IMHO:

On server start:
----------------
8816 Apache::DBI skipping connection during server startup, read the docu !!
[rest is gone!]

On first request:
-----------------
8818 Apache::DBI push PerlCleanupHandler
8818 Apache::DBI need ping: yes
8818 Apache::DBI new connect to 'foo:bar'
8818 Apache::DBI PerlCleanupHandler

This comes up a couple of times for every new process and afterwards
changes to "already connected" which is exactly the behaviour I expected in the
first place.

Do you have any idea what is going on? I'll check out how our real system
behaves with this change.

Thanks a lot, Perrin!

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
Quoting Tobias Kremer <list@funkreich.de>:
> Quoting Perrin Harkins <perrin@elem.com>:
> > How are you loading this? With a PerlModule call? Can you try
> > loading it from a Perl section like this?
> > <Perl>
> > use MyModule;
> > </Perl>
> Wow, it seems that this fixes the problem!
> Do you have any idea what is going on? I'll check out how our real system
> behaves with this change.

No more errors there either! :)

I don't know anything about the internals but to me the mod_perl source looks
like PerlModule is using "require" instead of "use" to load modules. I guess
that is making the difference?

Whatever the cause is, I think it should be documented somewhere. I'd happily
provide a doc-patch for whatever documentation is suitable :)

--Tobias
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
On Wed, Jul 2, 2008 at 5:12 AM, Tobias Kremer <list@funkreich.de> wrote:
> No more errors there either! :)

Great!

> I don't know anything about the internals but to me the mod_perl source looks
> like PerlModule is using "require" instead of "use" to load modules. I guess
> that is making the difference?

I don't know the details, but there is something about the way
PerlModule works in mod_perl 1 that causes it to load the module again
when apache restarts at startup (it runs yours conf file twice when
you start, as documented). Using an explicit use() puts an entry in
%INC and fixes the issue. This should happen with require() as well,
so I don't know what the problem is, but I've been told that mod_perl
2 doesn't have this problem.

There seems to be an additional bug in either Apache::DBI or mod_perl
1, since $Apache::ServerStarting == 1 seems not to be true the second
time through. Can you have your Perl section print out the values of
$Apache::ServerStarting and $Apache::ServerReStarting?

- Perrin
Re: Lost connection to MySQL server during query (was "Segfault when connecting during Apache startup") [ In reply to ]
> I don't know the details, but there is something about the way
> PerlModule works in mod_perl 1 that causes it to load the module again
> when apache restarts at startup (it runs yours conf file twice when
> you start, as documented). Using an explicit use() puts an entry in
> %INC and fixes the issue. This should happen with require() as well,
> so I don't know what the problem is, but I've been told that mod_perl
> 2 doesn't have this problem.

So, due to this being the mod_perl list there must be somebody here who knows
what's going on deep down in the guts of the beast :)

> There seems to be an additional bug in either Apache::DBI or mod_perl
> 1, since $Apache::ServerStarting == 1 seems not to be true the second
> time through. Can you have your Perl section print out the values of
> $Apache::ServerStarting and $Apache::ServerReStarting?

This gives me:

Apache::ServerStarting = 1
Apache::ServerReStarting = 0

once(!) on server startup - no matter if I "use" my handler or load it via
PerlModule.

--Tobias