Re: Intercepting critical SQL inside DBI.pm

If the solution outlined in the SO answer doesn't work out, here is an out-of-the-box idea: write a small module in C that wraps the low level MySQL/MariaDB library routine that actually sends the query to the database, and use LD_PRELOAD to enable/force it.

This is obviously an incomplete solution, because you won't have any information about the Perl call stack that produced the query, but if you have adequate logging in the Perl side of the application, you might be able to tie the query to a location in the code based on timestamps.

On the other hand, with this method you don't have do modify any application or library code on your production servers, you just have to put the .so on the server and modify the starter script/environment. Furthermore, you are guaranteed to catch the query just before it is sent to the DB server.

Comment on Re: Intercepting critical SQL inside DBI.pm

Replies are listed 'Best First'.
Re^2: Intercepting critical SQL inside DBI.pm by LanX (Saint) on Sep 16, 2022 at 13:12 UTC
I'm reluctant to dabble with C, and this wouldn't survive many maintenance cycles. And I really need the callers dumped, correlating timestamps is not really an option. 3 more options come to mind DBI's trace documents a way to tie the filehandle and intercept messages. Probably I can also access the original callers. `perlrun` and `perldebug` list several of Perl's trace options, I know from previous experiments that arguments are listed too in the stacktrace. writing a proxy object for DBI using AUTOLOAD may be more generic than sub-classing. I could dynamically decide what's intercepted, and if another object is returned I'd wrap it into just another proxy. I ~~'m sure~~ hope a search on CPAN will show previous attempts to do this. Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]
Re^3: Intercepting critical SQL inside DBI.pm by Corion (Patriarch) on Sep 16, 2022 at 15:39 UTC
Writing a proxy object for DBI is not enough as you have to write proxies for the database handle and database statement as well. A very simple approach is to enable the tracing in DBI, by calling `DBI->trace('2\|SQL\|2', 'c:/Temp/DBI-Trace.txt')` or setting `$ENV{DBI_TRACE}='2\|SQL\|2'`. This still has the drawback of writing to the filehandle. YOu could then use a tied filehandle maybe to analyze the lines written to it and use `caller()` repeatedly to identify the place where the statement was created from (by looking for the first non-DBD, non-DBI package in the calltree). The DBI documentation claims that this is only implemented by some DBDs, but maybe MySQL/MariaDB have that feature...	[reply] [d/l] [select]
Re^4: Intercepting critical SQL inside DBI.pm by LanX (Saint) on Sep 16, 2022 at 16:18 UTC
> as you have to write proxies for the database handle and database statement as well. I think you've missed this part > > and if another object is returned I'd wrap it into just another proxy. > YOu could then use a tied filehandle maybe to analyze the lines written to it and use caller() repeatedly to identify the place where the statement was created from ehm, yes ... probably you've missed this part > > DBI's trace documents a way to tie the filehandle and intercept messages. Probably I can also access the original callers. I was referring to DBI#Tracing-to-Layered-Filehandles But it depends on the actual implementation of DBI::trace, if I can really access the Perl callers. > A very simple approach is to enable the tracing in DBI, Personally I wouldn't call a tied filehandle a "very simple approach". YMMV... Cheers Rolf _{(addicted to the Perl Programming Language :) Wikisyntax for the Monastery}	[reply]