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?

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
4
  • 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. Commented Nov 14, 2016 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. Commented Nov 14, 2016 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. Commented Nov 15, 2016 at 1:36
  • Interesting. I think you're right to hold out for another answer. Hopefully it will be forthcoming. Commented Nov 15, 2016 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.

Improve this answer
1
  • 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. Commented Jul 10, 2017 at 16:02
0

The mysql general log does exactly what you want. Look for "execute" entires, not "prepare"

For instance here are two lines, first you see the prepare statement command with parameter variables, then you see execute statement with the variables substituted for their literal values.

Prepare SELECT `html_column_grid`.* FROM `html_column_grid` WHERE html_column_grid_html_id IN(?) ORDER BY `html_column_grid_ordering` Execute SELECT `html_column_grid`.* FROM `html_column_grid` WHERE html_column_grid_html_id IN('5555') ORDER BY `html_column_grid_ordering`

This is on mysql 5.7

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.