2

When I enable the MySQL general query log, prepared statement executions are logged like this:

Execute SELECT col1, col2 FROM table1 WHERE col1 = ?

I've seen other information on the Internet that I think shows people seeing the replaced values for prepared statement arguments, but I can find no information about this at all.

I need to see the values being used in queries, and can't do it on the client side because it's compiled code (though I did try to tell it to connect on port 3306, but ngrep shows no traffic so I suppose it's using a socket if available and I don't know how easy it would be to reconstruct prepared queries with a packet sniff anyway).

If I set long_query_time = 0 will the slow query log show the values I'm after? What other possibilities are there?

1

Setting long_query_time = 0 and slow_query_log = 1, restarting mysql shows queries with their actual argument values in the slow query log.

I won't accept this answer pending a better way to get the general query log to show the argument values.

  • You may have found the only workaround for this. +1. It seems the general log stores what the client sends while the slow query log stores what the server has just done. Some client libraries "emulate" prepared statements, doing the ? substitution before sending to the server, which is not a true prepared statement and is generally terrible imho... but would explain the general log being more in line with what you are looking for, in examples you've seen. In real client-side prepared statements, the parameters are sent in a binary form after the statement is prepared, not together with it. – Michael - sqlbot Nov 14 '16 at 13:48
  • Capturing them on the wire may be initially tedious but ultimately straightforward. The wire protocol is reasonably documented, though a bit difficult to wade through until you become familiar with it. See dev.mysql.com/doc/internals/en/prepared-statements.html. Wireshark has some built in and extensible ability to decode protocols. You could prevent use of the socket file by renaming the socket file while MySQL is running. MySQL won't notice (iirc) and the client won't know to look for the new name. – Michael - sqlbot Nov 14 '16 at 13:52
  • Thanks for the feedback. I'll note, however, that I corresponded with someone using the same software I was trying to debug who saw argument values in his general log, so there does appear to be variation on the MySQL side as well, giving me hope that there could be some way without using the slow query log. – chell Nov 15 '16 at 1:36
  • Interesting. I think you're right to hold out for another answer. Hopefully it will be forthcoming. – Michael - sqlbot Nov 15 '16 at 1:45
0

Do you need to track queries in dev or prod? If dev only you can take a look at Lottip. It shows prepared statements with it's params.

  • Welcome to ServerFault! The first question of your Answer should be a comment. The remainder may provide helpful information, but link only answers are strongly discouraged. If the link breaks in the future, the usefulness will be gone. – Cory Knutson Jul 10 '17 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.