How can I make DBI log all queries including params?

Question

I am trying to figure out what a legacy app does to our mysql databases (plural) as I need to refactor/replace a part of it completely without caring how it does it. I'm just interested in the result.

Currently I am working with DBI::Profile and DBI::ProfileDumper by adding $ENV{DBI_PROFILE} = "!Statement/DBI::ProfileDumper";. That shows me a bunch of queries when running dbiprof dbi.prof on the commandline, but it seems a little odd.

Might be I am reading the report wrong.

#####[ 3 ]###########################################################
  Count         : 4
  Total Time    : 0.018930 seconds
  Longest Time  : 0.018881 seconds
  Shortest Time : 0.000005 seconds
  Average Time  : 0.004732 seconds
  Key 1         :

INSERT INTO orders (foo, bar) VALUES (1, 2)

Why is it showing 4 as Count? It is creating two rows in the orders table, not 4. Also, it is showing the values of the first insert it is doing. For some others, it keeps the ? in the query string.

Maybe profiling is the wrong approach. I've been thinking about monkey-patching DBI to log the query, but I am not sure where to go with that. Might be I could add a logging function to prepare, and to execute to get the query strings as well as the parameters.

Is there something similar already that I can use? Surely someone wanted to do this? Or maybe another approach?

I'm open for any kind of suggestion.

possible duplicate of [How can I print the SQL query executed after Perl's DBI fills in the placeholders?](http://stackoverflow.com/questions/1687261/how-can-i-print-the-sql-query-executed-after-perls-dbi-fills-in-the-placeholder) — aidan, Sep 15 '15 at 06:03

score 3 · Accepted Answer · answered Oct 31 '13 at 10:27

3

You want to use tracing at level 2: https://metacpan.org/pod/DBI#TRACING

answered Oct 31 '13 at 10:27

Mithaldu

2,393
19
39

score 2 · Answer 2 · answered Aug 08 '18 at 18:01

2

DBI::Log is a great simple solution to this. My favourite thing about it is you can invoke it on the command line like:

perl -MDBI::Log script.pl

..and it'll output directly to STDERR, without requiring any modifications to your code.

answered Aug 08 '18 at 18:01

Dan

61,568
9
61
78

Nice one, thank you. That didn't exist when I asked this question though as it's come out in 2015. Funny enough, there is a deleted answer by another user from Sept 2015 suggesting it, too. They deleted it on their own accord although I had upvoted it. – simbabque Aug 09 '18 at 09:22

score 1 · Answer 3 · answered Nov 01 '13 at 09:53

1

You'll get more meaningful tracing which is also more flexible with DBIx::Log4perl or the slightly more modern DBIx::LogAny.

answered Nov 01 '13 at 09:53

bohica

5,932
3
23
28

Thank you for the suggestion. I'm not sure it will help me, though. I do not have a logging layer in my code and do not yet have any experience with it. Also, the examples look like I need to modify the dbhs. I don't know how many dbhs there are, though, and I don't intend to go find them in the code. If you have suggestions about that I'd appreciate them. – simbabque Nov 01 '13 at 12:00
Obviously I haven't seen your code but I'd still be surprised if you really have that many connect calls. Most code I see these days wraps DBI in some way so you might find you only have one place to change it.I cannot think of any easy around this right now. It might be worth asking that very question here or on the dbi-users mailing list or perlmonks. – bohica Nov 01 '13 at 13:21

How can I make DBI log all queries including params?

3 Answers3

Linked