Tracing SQL queries done via DBI (mod_perl)

[Ignoramus8495]

I would like to have a log of all SQL queries that my scripts do via
DBI. (80k+ of perl code, mod_perl)

I have one entry point (module) whose job is to give the database
handle to everyone. So I have control over the handle.

There is a trace() method on DBI, however, it does not properly print
the statements that were prepare'd a long time ago and are repeatedly
execute'd.

So... What am I missing?

Can I somehow track all statements that are executed?

thanks

i

 

[Sherm Pendley]

I would like to have a log of all SQL queries that my scripts do via
DBI. (80k+ of perl code, mod_perl)

I have one entry point (module) whose job is to give the database
handle to everyone. So I have control over the handle.

There is a trace() method on DBI, however, it does not properly print
the statements that were prepare'd a long time ago and are repeatedly
execute'd.

So... What am I missing?

Does this help?

<http://perl.apache.org/docs/1.0/guide/databases.html#Debugging_code_which_deploys_DBI>

sherm--

 

[A. Sinan Unur]

Does this help?

<http://perl.apache.org/docs/1.0/guide/databases.html#Debugging_code_which_deploys_DBI>

I doubt it:

http://search.cpan.org/~timb/DBI-1.50/DBI.pm

Trace Flags

Trace flags are used to enable tracing of specific activities
within the DBI and drivers. The DBI defines some trace flags
and drivers can define others. DBI trace flag names begin with
a capital letter and driver specific names begin with a lowercase
letter, as usual.

Curently the DBI only defines two trace flags:

ALL - turn on all DBI and driver flags (not recommended)
SQL - trace SQL statements executed (not yet implemented)

Sinan
--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

 

[Ignoramus8495]

Does this help?

<http://perl.apache.org/docs/1.0/guide/databases.html#Debugging_code_which_deploys_DBI>

I believe that it does not print SQL statements that were prepared a
long time ago, and are now being executed. It only says "executing",
but does not say what.

It is basically a glorified way of calling $dbh->trace( 3 ).

I could possibly make some complicated analysis tool matching
addresses of statement handles with previous output from prepare, but
that would be a pain with obvious problems (several processes could
have identical addresses occupied by different objects)

Example:

#!/usr/bin/perl

use Algebra::SqlAccess;

dbh->trace( 3 );

my $sth = dbh->prepare( "select count(*) from sessions" );

for( my $i = 0; $i < 3; $i++ ) {
$sth->execute;
$sth->finish;
}


manifold::~/tmp==>perl dbitest.pl
DBI::db=HASH(0x9ad30bc) trace level set to 0x0/3 (DBI @ 0x0/0) in
DBI 1.50-ithread (pid 29911)
-> prepare for DBD::mysql::db (DBI::db=HASH(0x9a8a150)~0x9ad30bc
'select count(*) from sessions') thr#9855008
dbih_setup_handle(DBI::st=HASH(0x9ad3284)=>DBI::st=HASH(0x9ad3404), DBD::mysql::st, 9ad3290, Null!)
dbih_make_com(DBI::db=HASH(0x9ad30bc), 9ada688, DBD::mysql::st,
248, 0) thr#9855008
dbd_st_prepare calling count_params (counting params emulation)
<- prepare= DBI::st=HASH(0x9ad3284) at dbitest.pl line 7
-> execute for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
-> dbd_st_execute for 09ad344c
---> parse_params with statement select count(*) from sessions num
params 0
mysql_st_internal_execute
<- dbd_st_execute returning imp_sth->row_num 1
<- execute= 1 at dbitest.pl line 10
-> finish for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
<- finish= 1 at dbitest.pl line 11
-> execute for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
-> dbd_st_execute for 09ad344c
---> parse_params with statement select count(*) from sessions num
params 0
mysql_st_internal_execute
<- dbd_st_execute returning imp_sth->row_num 1
<- execute= 1 at dbitest.pl line 10
-> finish for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
<- finish= 1 at dbitest.pl line 11
-> execute for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
-> dbd_st_execute for 09ad344c
---> parse_params with statement select count(*) from sessions num
params 0
mysql_st_internal_execute
<- dbd_st_execute returning imp_sth->row_num 1
<- execute= 1 at dbitest.pl line 10
-> finish for DBD::mysql::st (DBI::st=HASH(0x9ad3284)~0x9ad3404)
thr#9855008
<- finish= 1 at dbitest.pl line 11
-> DESTROY for DBD::mysql::st (DBI::st=HASH(0x9ad3404)~INNER)
thr#9855008
<- DESTROY= undef
! -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x9ad30bc)~INNER)
thr#9855008
&imp_dbh->mysql: 9ada6dc
! <- DESTROY= undef during global destruction

i

 

[Sherm Pendley]

I doubt it:

http://search.cpan.org/~timb/DBI-1.50/DBI.pm

Trace Flags

Trace flags are used to enable tracing of specific activities
within the DBI and drivers. The DBI defines some trace flags
and drivers can define others. DBI trace flag names begin with
a capital letter and driver specific names begin with a lowercase
letter, as usual.

Curently the DBI only defines two trace flags:

ALL - turn on all DBI and driver flags (not recommended)
SQL - trace SQL statements executed (not yet implemented)

Yes, but Apache:BI - mentioned in the link I gave - extends the basic
trace functionality to five different trace levels. I've not used it, but
my thinking is that, being specific to mod_perl, it may work better in
that environment than the "vanilla" DBI trace() function.

If nothing else, it wouldn't hurt to try it.

sherm--

 

[Dr.Ruud]

Ignoramus8495 schreef:

I would like to have a log of all SQL queries that my scripts do via
DBI. (80k+ of perl code, mod_perl)

I have one entry point (module) whose job is to give the database
handle to everyone. So I have control over the handle.

There is a trace() method on DBI, however, it does not properly print
the statements that were prepare'd a long time ago and are repeatedly
execute'd.

So... What am I missing?

Can I somehow track all statements that are executed?

There was a thread on this in news://nntp.perl.org//perl.dbi.users
recently.

That is the mailing list <dbi-users(AT)perl.org>, contact
<dbi-users-help(AT)perl.org> for help.

 

[xhoster]

Does it also give out statement handles or just database handles?

If it only gives out database handles, then each CGI submission would have
to prepare the statement handles itself. So you shouldn't have cases where
you are executing things prepared a long time ago.

I could possibly make some complicated analysis tool matching
addresses of statement handles with previous output from prepare, but
that would be a pain with obvious problems (several processes could
have identical addresses occupied by different objects)

Does the trace output go through your logging methods? If so, have your
logger add a pid stamp to the output.

(if you set the trace level above 9, apparently this is automatically done,
but then you will get a lot more output)

Xho