Statement Handle still Active

Hi,

I've a several 100 thousand line application. I did not introduce this issue, but I need to fix it.

prepare_cached(SELECT foo FROM A where X = ?)
statement handle MyDBI::st=HASH(0xd630194) was still active at X.pm

I've tried turning on $dbh->(12, 'file.trace') and looking for all things that shared the statement handles
address. Then seeing if there was a FETCH= undef -- the end of a while loop, or finsih= 1 -- $sth->finish() was called.

That took a long time and everything seemed okay. Then I pains-takenly figured out all the 'prepare_cached' involved in
the code path. I then add $sth->finish() for kicks everywhere even when I knew I "didn't need it" per the DBI docs
about it. This didn't help.

Is there any way (preferably easy) to track this down ?

Its going to have to programatic as the amount of code involved is not manageable and I'm unable to reduce it. If I
could reduce it I could fix it :)


--
------------------------------------------------------------ ------------
Philip M. Gollucci (pgollucci@p6m7g8.com) 323.219.4708
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F

"In all that I've done wrong I know I must have done something right to
deserve a hug every morning and butterfly kisses at night."
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
<___/

Re: Statement Handle still Active

On Fri, Sep 22, 2006 at 01:37:42PM -0700, Philip M. Gollucci wrote:
> Hi,
>
> I've a several 100 thousand line application. I did not introduce this
> issue, but I need to fix it.
>
> prepare_cached(SELECT foo FROM A where X = ?)
> statement handle MyDBI::st=HASH(0xd630194) was still active at X.pm
>
> I've tried turning on $dbh->(12, 'file.trace') and looking for all things
> that shared the statement handles
> address. Then seeing if there was a FETCH= undef -- the end of a while
> loop, or finsih= 1 -- $sth->finish() was called.
>
> That took a long time and everything seemed okay. Then I pains-takenly
> figured out all the 'prepare_cached' involved in the code path. I then add
> $sth->finish() for kicks everywhere even when I knew I "didn't need it" per
> the DBI docs about it. This didn't help.
>
> Is there any way (preferably easy) to track this down ?
>
> Its going to have to programatic as the amount of code involved is not
> manageable and I'm unable to reduce it. If I could reduce it I could fix
> it :)

I'd focus on the particular SQL statement reported in the message.
I presume you'd be able to identify where that SQL statement was
in the code (or where it was generated). Then $sth->trace() just that
handle.

But the way you were doing it should have found the problem.
Get a trace then grep it for the particular handle address you're
interested in.

A more exotic approach might be to subclass the DBI and override the
execute method, for example, to store a copy of the call stack into a
private attribute of the handle. Then add an error handler that dumps
that info if it's present. Then your error message might say something
like "... was still active (last executed at file Foo.pm line N called
by Bar.pm line M)" etc etc.

Tim.

Re: Statement Handle still Active

On Fri, 2006-09-22 at 13:37 -0700, Philip M. Gollucci wrote:
> Hi,
>=20
> I've a several 100 thousand line application. I did not introduce this i=
ssue, but I need to fix it.
>=20
> prepare_cached(SELECT foo FROM A where X =3D ?)
> statement handle MyDBI::st=3DHASH(0xd630194) was still active at X.pm
>=20
> I've tried turning on $dbh->(12, 'file.trace') and looking for all things=
that shared the statement handles
> address. Then seeing if there was a FETCH=3D undef -- the end of a whil=
e loop, or finsih=3D 1 -- $sth->finish() was called.
>=20
> That took a long time and everything seemed okay. Then I pains-takenly f=
igured out all the 'prepare_cached' involved in=20
> the code path. I then add $sth->finish() for kicks everywhere even when =
I knew I "didn't need it" per the DBI docs=20
> about it. This didn't help.
>=20
> Is there any way (preferably easy) to track this down ?
>=20
> Its going to have to programatic as the amount of code involved is not ma=
nageable and I'm unable to reduce it. If I=20
> could reduce it I could fix it :)

What database and DBD are you using? There has been a bug in recent
versions of DBD::mysql (fixed in 3.0007) that was producing these
errors.

mark
--
=20





MARK ADDISON
WEB DEVELOPER

200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F=20
E MARK.ADDISON@ITN.CO.UK
WWW.ITN.CO.UK
Please Note:

=20

Any views or opinions are solely those of the author and do not necessarily=
represent=20
those of Independent Television News Limited unless specifically stated.=20
This email and any files attached are confidential and intended solely for =
the use of the individual
or entity to which they are addressed.=20
If you have received this email in error, please notify postmaster@itn.co.u=
k=20

Please note that to ensure regulatory compliance and for the protection of =
our clients and business,
we may monitor and read messages sent to and from our systems.

Thank You.