4 Things To Know About MySQL Prepared Statements

Posted by Baron Schwartz on Jul 10, 2014 3:21:00 AM

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 VividCortex, 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).

Prepared_Statements

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. Without the VividCortex tools, it’s nearly impossible to find what statements are being prepared but never closed.

MySQL_Prepared_Statements

Subscribe to Email Updates

Posts by Topic

see all