DBI and Apache::DBI issues

Hello mod_perlers,

I recently updated my server, which was running Debian Sarge, and
migrated from mod_perl 1 to 2, among many other upgrades. It wasn't a
smooth process, but I suppose skipping three major OS versions I
shouldn't have expected it to be.

But the issue I'm asking about today is with DBI (1.612) and
Apache::DBI (1.08). In my old configuration they worked fine, but
after the upgrade I started seeing tons of errors like this:

DBD::mysql::st execute failed: Commands out of sync; you can't run
this command now
DBD::mysql::st fetchrow_hashref failed: fetch() without execute()
Can't set DBI::st=HASH(0xba5fc640)->{NAME}: unrecognised attribute
name or invalid value
Can't call method "get" on an undefined value
DBD::mysql::st execute failed: Lost connection to MySQL server during
query
DBD driver has not implemented the AutoCommit attribute

So I removed Apache::DBI from my startup.perl, which almost fixed it.
But not quite. I would still get occasional errors like this:

DBD::mysql::st fetchrow_hashref failed: fetch() without execute()

One sort of oddball thing I'm doing is using a convenience wrapper
that looks like this:

package My::DBI;
use DBI;
sub connect {
my $class = shift;
...
DBI->connect($dsn, $user, $dbpw, @_);
}

And then elsewhere doing this:

package My::Foo;
use My::DBI;
my $dbh = My::DBI->connect;
sub new {
my $sth = $dbh->...
}

Thinking that the problem was that $dbh was having some weird scope
issue, I moved all the connect calls into the subs that use them. This
seems to have solved the second batch of errors, but when I re-enable
Apache::DBI, it starts generating tons of the first kind of errors,
particularly "Commands out of sync."

I've Googled these errors and haven't found anything that seems
applicable.

I appreciate any help. Thanks.

Max

---
Max Pinton, max@maxgraphic.com
Design your own card at http://www.designyourowncard.com/

Re: DBI and Apache::DBI issues

Hi Max,

On Sat, Feb 5, 2011 at 10:01 PM, Max Pinton wro=
te:
> But the issue I'm asking about today is with DBI (1.612) and Apache::DBI
> (1.08). In my old configuration they worked fine, but after the upgrade I
> started seeing tons of errors like this:
>
> DBD::mysql::st execute failed: Commands out of sync; you can't run this
> command now

Are you running the worker MPM? Apache::DBI will do the right thing
with prefork MPM, but I don't think it will work with threads.

> package My::Foo;
> use My::DBI;
> my $dbh =3D My::DBI->connect;
> sub new {
> =A0 =A0 =A0 =A0my $sth =3D $dbh->...
> }
>
> Thinking that the problem was that $dbh was having some weird scope issue=
, I
> moved all the connect calls into the subs that use them.

Yes, good call. The new() sub creates a closure with $dbh there.

- Perrin

Re: DBI and Apache::DBI issues

On Feb 6, 2011, at 8:20 AM, Perrin Harkins wrote:
> Are you running the worker MPM? Apache::DBI will do the right thing
> with prefork MPM, but I don't think it will work with threads.

Nope, prefork:

# apache2 -V
Server version: Apache/2.2.16 (Debian)
Server built: Nov 14 2010 18:14:55
Server's Module Magic Number: 20051115:24
Server loaded: APR 1.4.2, APR-Util 1.3.9
Compiled using: APR 1.4.2, APR-Util 1.3.9
Architecture: 32-bit
Server MPM: Prefork
threaded: no
forked: yes (variable process count)

>> Thinking that the problem was that $dbh was having some weird scope
>> issue, I
>> moved all the connect calls into the subs that use them.
>
> Yes, good call. The new() sub creates a closure with $dbh there.

Did earlier versions of Perl/mod_perl/DBI/Apache::DBI handle closures
differently? I'm just curious why it used to work. It seems like DBI
would reconnect a $dbh closure if it timed out.

Thanks, Perrin.

---
Max Pinton, max@maxgraphic.com
Design your own card at http://www.designyourowncard.com/

Re: DBI and Apache::DBI issues

On Sun, Feb 6, 2011 at 4:44 PM, Max Pinton wrote:
> Did earlier versions of Perl/mod_perl/DBI/Apache::DBI handle closures
> differently?

No, not at all. This is a core Perl thing.

> I'm just curious why it used to work. It seems like DBI would
> reconnect a $dbh closure if it timed out.

One thing that might have changed is your database configuration.
MySQL is often configured to automatically reconnect.

That doesn't explain all of your errors though. I'm guessing that
you're opening a connection during startup and forking with it. You
need to find that. Look for things that might open a connection
during startup and move them to ChildInitHandlers instead.

- Perrin

Re: DBI and Apache::DBI issues

On Feb 8, 2011, at 7:36 AM, Perrin Harkins wrote:
>> I'm just curious why it used to work. It seems like DBI would
>> reconnect a $dbh closure if it timed out.
>
> One thing that might have changed is your database configuration.
> MySQL is often configured to automatically reconnect.

I checked $dbh->{mysql_auto_reconnect} and, as per the docs, it's 1 in
a mod_perl script and 0 otherwise. Is there another place to look? It
seems like something I'd really want to have on.

> That doesn't explain all of your errors though. I'm guessing that
> you're opening a connection during startup and forking with it. You
> need to find that. Look for things that might open a connection
> during startup and move them to ChildInitHandlers instead.

That's puzzling. I'm not forking anywhere in my scripts. I did once
try using threads for something, but abandoned it because it
segfaulted in mod_perl and it wasn't db-related anyway. My
startup.perl (minus my modules) is:

#!/usr/bin/perl
use strict;

# make sure we are in a sane environment.
$ENV{MOD_PERL} or die "not running under mod_perl!";

use ModPerl::Registry ();

# Apache::DBI must be before DBI
#use Apache::DBI ();
use DBI ();

use CGI '-no_xhtml';
CGI->compile(':all');
use GD ();
use MIME::Base64 ();
use Data::Dumper ();
use XML::Simple ();
use XML::Writer ();
use File::Temp;

1;

Does anything there look fishy?

Thanks,

Max

Re: DBI and Apache::DBI issues

On Tue, Feb 8, 2011 at 16:26, Max Pinton wrote:
> On Feb 8, 2011, at 7:36 AM, Perrin Harkins wrote:
>> That doesn't explain all of your errors though.  I'm guessing that
>> you're opening a connection during startup and forking with it.  Yo=
u
>> need to find that.  Look for things that might open a connection
>> during startup and move them to ChildInitHandlers instead.
>
> That's puzzling. I'm not forking anywhere in my scripts. I did once try
> using threads for something, but abandoned it because it segfaulted in
> mod_perl and it wasn't db-related anyway. My startup.perl (minus my modul=
es)
> is:

You could always try strace to find if there are any fork/clone calls:
strace -e trace=3Dfork,clone httpd -X

> [startup.perl]

> Does anything there look fishy?

Nothing jumps out at me.

Re: DBI and Apache::DBI issues

On Tue, Feb 8, 2011 at 6:26 PM, Max Pinton wrot=
e:
> I checked $dbh->{mysql_auto_reconnect} and, as per the docs, it's 1 in a
> mod_perl script and 0 otherwise. Is there another place to look? It seems
> like something I'd really want to have on.

Recommended practice is to turn this off. If it's on and you lose
your connection for some reason in the middle of handling a web
request, the client will try to reconnect and keep going with the rest
of the request. That's almost certainly not what you want in any
situation where data is modified.

Apache::DBI or DBI->connect_cached() are better because they only
reconnect when you tell them to, at the beginning of the requests.
However, this means that if you use Apache::DBI you MUST call
DBI->connect() at the beginning of every request and not cache the
$dbh objects yourself.


>> That doesn't explain all of your errors though. =A0I'm guessing that
>> you're opening a connection during startup and forking with it. =A0You
>> need to find that. =A0Look for things that might open a connection
>> during startup and move them to ChildInitHandlers instead.
>
> That's puzzling. I'm not forking anywhere in my scripts.

I meant apache forking child processes after startup.

> My startup.perl (minus my modules)

The problem in this case would be in your modules. One of them
probably opens a database connection for some reason. You can see it
happen by using DBI_TRACE.

- Perrin