Leaking prepared statements

[benoist]

Currently prepared statements are only closed when the connection is closed. However when connections live very long, and lots of unique queries are created, the statements_cache grows. Mysql has a limit of how many prepared statements can be kept globally. Not clearing old prepared statements has lead to the following error:

Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)

This is treated as a normal mysql error and the connection is kept open, however every new prepared statement will fail so the connection is basically broken.

I can think of 2 solutions

• Set a maximum in the statements cache and clear old prepared statements
• Close the the connection when this error occurs.

[bcardiff]

Some thoughts:

We could allow prepared_statements parameter to be not just true, false but the maximum of allowed prepared statements.

Given that, the connections will have a maximum prepared statements. Upon creating a new one a previous prepared statement could might be need to be deallocated. But only the once created by the PoolPreparedStatement should be deallocated. Otherwise a prepared statement created explicitly over a connection could suddenly become invalid. So we would need to track the ownership of the prepared statement.

That lead us to the possibility that the connection might not be able to deallocate any prepared statement at all. To handle this, the PoolPreparedStatement#build_statement could raise a specific exception that won't close the connection but allow the Pool#retry (used in PoolStatement#statement_with_retry ) to just try again with other connection. But that leads to the possibility of starving of resources again, eventually. Either the db server rejecting to establish new connections or the max connection of the pool been reached

So maybe is better as suggested to close the connection when that error occur. Which don't need to change the pool options. But if we add a new pool option for the max prepared statements, upon reaching it, maybe is also safer to just close that connection and start all over.

Let's wait for some further discussion over this topic before defining a way to go.

[benoist]

Looking at the JDBC driver documentation on the mysql website The statement caching is disabled by default. If enabled statement caching is limited to 25 statements. (https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html)

On the wiki of HikariCP "A solid high-performance JDBC connection pool at last." It advices to change these defaults to 250-500 if required. https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

[bcardiff]

A recent idea regarding this is to put a max life span for connections in the pool. This will decrease the chance of reaching this issue and recycle resources more frequently.

[didactic-drunk]

Knowing how often a statement is reused could be helpful. Perhaps keep statistics on reuse and optionally log prepared statement usage when the connection is closed.