How to log SQL query execution in DBI

arkturuz has asked for the wisdom of the Perl Monks concerning the following question:

Greetings Perl Monks!

I have a web application with at least 100 SQL queries (never counted them), scattered through various modules for various purposes. I connect to a MySQL database via DBI module.

I would like to log every query execution into a file, ie. to write in the file something like:

execution_timestamp, full_query
[download]

The problem is that every query is prepared and executed wheen needed in the program, so I should go through all the sources and add a function to write the log for each succesfull query execution, which is bad (tm).

So, is there a way of enabling that kind of logging in DBI (or MySQL itself) on a global execution level?

Thnx for answers!

Comment on How to log SQL query execution in DBI Download Code

Replies are listed 'Best First'.
Re: How to log SQL query execution in DBI by Zaxo (Archbishop) on Nov 28, 2005 at 16:21 UTC
See the "TRACING" section of the DBI docs. You'll probably have to set up a redirected STDERR to make it log to a file. After Compline, Zaxo	[reply]
Re^2: How to log SQL query execution in DBI by Fletch (Bishop) on Nov 28, 2005 at 16:29 UTC
`Trace Output Initially trace output is written to "STDERR". Both the "$h->t +race" and "DBI->trace" methods take an optional $trace_filename param +eter. If specified, and can be opened in append mode, then all trace out +put (currently including that from other handles) is redirected to +that file. A warning is generated if the file can't be opened.` [download]	[reply] [d/l]
Re: How to log SQL query execution in DBI by tirwhan (Abbot) on Nov 28, 2005 at 16:27 UTC
To do this from mysql, add `log = /path/to/logfile` [download] to your mysql configuration file. Note that this slows down your queries a lot. If you just want to log long-running queries you can put `log-slow-queries=/path/to/logfile long_query_time=1` [download] Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. -- Brian W. Kernighan	[reply] [d/l] [select]
Re^2: How to log SQL query execution in DBI by arkturuz (Curate) on Nov 28, 2005 at 16:50 UTC
Wow! I did not expect answers so fast :-) I read the DBI man page but I thought thar trace is not the thing I need. Now, I will try it. Thanks for answers!	[reply]
Re: How to log SQL query execution in DBI by Fletch (Bishop) on Nov 28, 2005 at 16:27 UTC
Look at the docs for DBI for info on the `trace()` method; that may be close to the info you're trying to get.	[reply] [d/l]