Node-mysql2: max_prepared_stmt_count error, do I need to increase maxPreparedStatements or is something wrong with it?

[terencechow]

I'm getting the below error from mysql:

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

I've increased the value to the max (1 million) for now.

I'm thinking even though it has increased for now, no matter what my prepared statement count will eventually hit 1M.

What I don't understand is that I see a default value for maxPreparedStatements for 16000. Wouldn't that prevent this issue from happening? Is it because I have 3 replica servers connected to my mysql instance? If so, if I increase my max_prepared_stmt_count statements to something > 16000 * 3, like for example 50000, will that prevent this problem from reoccuring?

Also how do I clear this cache in the meantime if I hit this 1M mark?

My node/mysql2 code is as follows:

import mysql from 'mysql2/promise'
const pool = mysql.createPool({ host, port, connectionLimit: 100 })
export default pool

I then use the pool everywhere with prepared statements a la:

pool.execute('SELECT * FROM ... etc etc')

[sidorares]

this usually means that you use execute() with variable part injected inline instead using placeholder + parameter

compare conn.execute('select ' + (+new Date()) + ' as timestamp') with conn.execute('select ? as timestamp', [+new Date()]). First one prepares + executes new statement each time, second is only prepared once and just executed every next time ( but not prepared )

[sidorares]

so the solution for you 1) try to find this "variable" part and move it to be parameter of the statement or 2) don't use prepared statements and just pool.query('SELECT * FROM ... etc etc')

[terencechow]

I've reviewed the code. I know I have some code to fix where I use variable lists, i.e. SELECT * FROM table WHERE id in (?, ?, ?) <-- where the 3 question marks are determined dynamically by a list passed in from the front end. I will fix that, but I don't think that is the issue since none of my logs suggest anyone passed in a list of > 10-20 question marks.

If I have 3 servers that each have a 100 connection pool with the default limit at 16,000 max prepared statements. Does that mean I potentially have 3 * 100 * 16000 prepared statements? If that's the case, setting the maxPreparedStatements to 1000 and setting max_prepared_stmt_count to 350,000 should protect me from this in the future?

Or alternatively is it 16,000 maxPreparedStatements per server, i.e. 3 * 16,000 prepared statements. In this case setting my max_prepared_stmt_count to 50k should protect me from this in the future, yes?

[sidorares]

according to docs - This variable limits the total number of prepared statements in the server.
I think that means that limit is shared between all connections to a server

I still don't see how you might need (or generate) that many statements, does this mean several hundreds range of '?'s in the list?

Prepared statements are scoped by connection and cannot be shared between them, so number of PSs server have to create is number of connections X number of unique sqls

You can control lifetime of PS more manually with prepare/execute/unprepare - see https://github.com/sidorares/node-mysql2/blob/master/documentation/Prepared-Statements.md

[terencechow]

Yea I saw that link but I didn't want to have to close mine out on my own. I much rather prefer an 'automatic' solution. Also since I use the promise library, getting the statement to close isn't as easy. (i.e. there is no fn to close a prepared statement on pool that I can find...)

So what you're saying is my connectionLimit of 100 on 3 servers each implies I actually have 300 x number of unique prepared statements... I think it's reasonable for me to have less than 1000 maxPreparedStatements so if I set maxPreparedStatements to 1000, then 3 servers * 100 connections * 1000 max statements = 300,000 max prepared statements. Then if I set max_prepared_stmt_count to 500,000 then there should be no way I get this error again correct?

I guess I need to clarify if maxPreparedStatements is per connection or per server?

Btw, Thanks so much for your quick responses on this I really appreciate it!

[sidorares]

I guess I need to clarify if maxPreparedStatements is per connection or per server?

yeah, I'd guess it's per server and most likely preparing same sql from 2 separate connections results in increasing prepared statements counter by 2

[terencechow]

Ok after some testing I have figured a possible solution. Some preliminary analysis here, but I'll leave this for future users.

maxPreparedStatements is not per server, it is per connection.
max_prepared_stmt_count is across all connections.

Hence if I have a max_prepared_stmt_count of 20 and I have 1 server with a connectionLimit of 4, then having maxPreparedStatements of 5 fails. However having a maxPreparedStatements of 4 does not fail. This is because the maxPreparedStatements become 20 and 16 respectively. Somehow 20 causes an error on the next prepare statement but 16 causes the LRU to refresh without any errors. Might be a minor bug somewhere where the value is checking <= instead of just < ? (It might be that I make multiple concurrent requests and they concurrently prepare statements as well, hence a race condition causes an error...)

This is testable by creating a route that prepares a bunch of statements.

With maxPreparedStatements of 4 and connectionLimit of 4, show global status like '%Prepared_stmt_count%'; gives a maximum of 16 after a number of requests to that route

With maxPreparedStatements of 5 and connectionLimit of 4, show global status like '%Prepared_stmt_count%'; gives a maximum of 20, but the next call to prepare a query gives me an error.

[sidorares]

Thanks for the update @terencechow . Sorry, I completely forgot about maxPreparedStatements option and thought you were referring to max_prepared_stmt_count. Yes, maxPreparedStatements is per connection ( connection does not know much about other connections state ) and max_prepared_stmt_count is for all active connections to a server ( statements automatically cleaned up when connection is closed / killed / etc )

If you can dig into that race condition or off by one error and make reproducible test case please open separate issue, closing this for now.

[danielecr]

I do not have a reproducible test case, but I had this problem on one service and I solved it by lowering the maxPreparedStatements to 20, and connection pool to 1.
My requirement was very specific, maybe, because trying to make parallel query execution of the same query cause a big lock on the table, so it make no sense to have more than one connection running per time, but having pool warrant me against connection lost for long idle time.
I also have php code where I use prepared statement (real, not emulated), and I used a stupid solution for the reuse of prepared statement, indexing it by:

$prepared[` md5($sqlStatement) ] = mysql_prepare($sqlStatement);

and reusing the prepared staff every time.

I expected something similar with pool connection, like connId + "." + md5(sql) so I have a look at the code, I find it is used LRU cache (that is very cool staff with dispose, nice to know), and that every connection has its own lru cache (so connId was a bad expectation), but the key is so complex, I expected to read unique association between the sql prepare statement (I mean https://dev.mysql.com/doc/refman/8.0/en/prepare.html ) and the string, but I find there is an options where sql (string?) is just one part and nestTable is preparsed and used.

I do not know what exactly is needed for and why parsing is needed, but I know my code generate and use at most 5 prepared statements, and it is not possible to exeeds the max_prepared_stmt_count with 5 prepared and 20 connections. (If I recall correctly, I changed that code because it breaks everything)

What happens if I replace statementKey(options) code with just return options.sql?