While writing our TCP stream reassembly and MySQL protocol reverse-engineering algorithms, a few finer points of the MySQL protocol and internals came up. None of this is new information, but you may not have stumbled upon it before.
Prepared Statement IDs
Prepared statements are generated by the
COM_STMT_PREPARE protocol command, with the statement text as an argument. The server prepares the statement, and assuming all goes well, returns a statement ID. This is a number that the client needs to remember for future executions of the prepared statement. The ID increments with each new prepared statement.
Statement Scope
The statement is scoped to the connection that created it. It’s not visible or valid for any other connection. Statement IDs are private to the connection too, so server-wide you will not have uniqueness. Every connection can have its own statement numbered 32, and the server keeps track of them separately. When a connection closes, the prepared statements are cleaned up and discarded along with the resources they hold.
However, the limit on the total number of prepared statements that have been created but not closed is global, not per-connection. That means that you can run the server out of prepared statements if a lot of connections “leak” prepared statements. The variable is called
max_prepared_stmt_count. You can see the number of prepared statements created, globally, by looking at
prepared_stmt_count.
Closing Statements
The wire protocol for closing a prepared statement is different from all the other commands, as far as I know. It does not respond. You send the server a
COM_STMT_CLOSE, and there is literally no response. Not in the protocol, not at the TCP level, nothing. At first I thought, oh, of course there will be some kind of ACK that the packets were even received, surely? Nope.
If there are any other protocol commands that do this type of thing, post something in the comments!
Changes in Prepared Statements
Prepared statements haven’t changed in a long time, but in MySQL 5.7, a few changes are coming. First, the server will prepare and optimize them differently. See
Guilhem’s blog post on that for details.
Secondly, the
PERFORMANCE_SCHEMA doesn’t expose prepared statements as much as desired in current versions. That will be improved in 5.7 as well. I think you can find information on that in the 5.7 release notes.
Finally, the server also supports a strange server-side way to access prepared statements through SQL commands. I can think of a lot of things about MySQL that are confusing to explain, but this one has always defied my best efforts. (I mean, not to explain as in defending it – I see the purpose – but just to help someone see what it is and how it’s different from prepared statements; it’s SO confusing).
Todd Farmer has some details on that. I also tried (but I think I failed) to elucidate it in High Performance MySQL.
On the Topic of Prepared Statements…
With Database Performance Monitor (DPM), you get full visibility into every prepared statement action. We capture and measure preparing, executing, and closing the statements, and we show these separately in our user interface. Note the Action column in the Top Queries user interface, for example. You won’t find this level of detail in sources like the slow query log, where prepared statements are only logged when they’re executed, and they appear to be normal query executions (they aren’t marked as prepared statement executions in any way).
This view, ranking by Count, is also extremely useful for diagnosing which prepared statements are not closed and may represent a prepared statement “leak” in your application, which can cause the server to exceed the configured limit. This actually happens! You can see it in the graphs view as well – here’s an example from a leak we experienced once when we wrote a buggy “leak” into one of our API servers and the prepared statement count climbed steadily.
