MYSQL2 with NodeJS — Prepared statement cache issue in MySQL — Can’t create more than max_prepared_stmt_count statements

We were about to encounter an issue on production for max_prepared_stmt_count exceeding the limit, and we were not able to figure out any reliable source which can help to resolve the problem since this issue can drop MYSQL availability sooner or later. Therefore, I felt a need to write in detail, what exactly happens underneath that causes an error from MYSQL and how to solve it.

MYSQL execute mechanism:

MYSQL provides execute helper which execute statements in the following pattern on the server:

1. Prepared: It prepares and caches the query in a binary format on the server. If an identical query is requested again in the same session then improved throughput time.
2. Execution: It executes a query on the server, and returns the result set
3. UnPrepared: MYSQL provides a wrapper to remove the prepared statements by removing from the cache, hence not creating a burden on the server.

Problem:

By default max connection limit in MYSQL for “Prepared_stmt_count” is somewhere around 16000. In case the number of cache statements exceeds this limit then the server will stop responding to queries and return following error:

“Can’t create more than max_prepared_stmt_count statements”

Problem root cause:

Parameterized queries are the source of an issue since they behave as a dynamic statement in SQL. Let me explain with an example:

// Statement one
conn.execute('Select name from employee where id = ' + id);

While writing a query, be cautious to not cache a query by using a string concatenation or even interpolation since each one of them will be cached separately as a different query because of a different value for the “id” parameter.

Permanent solution:

// Statement two (Parameterized query)
conn.execute('Select name from employee where id = ?', [id]);

The second statement is utilizing parameters which will be replaced after caching a query, hence for different ids, the same query will be utilized.

Temporary solutions:

1. If precaution was not taken while writing queries, somehow an error appears on production, then temporarily increase the limit of max_prepared_stmt_count to any huge number to avoid hitting max count, while looking for a permanent solution.
2. MYSQL2 package also provides “query” wrapper instead of “execute”, which does not cache a statement on the server but it forms a query on the client-side, it might have a drawback of SQL injection vulnerability.
3. MYSQL2 claims that calling “unprepare” method after using the “execute” remove a statement from the cache, it does not work all of the time for me. It releases a few times but the number of cache statement still the same after calling “unprepare”. If it works every time for you then kindly let me know.

Conclusion:

While working with MYSQL database even using a different NodeJs package, try to utilize parameterized queries and avoid cache identical queries with different param values. A cache is a functionality on the server and any other package can produce the same error.

I used MYSQL2 since its performance is outstanding but I would love to hear about your experience with any different package.