Can't create more than max prepared stmtount statements(current value: 16382)

[maoweifan]

I used execute() extensively, which resulted in this error. How should I resolve it and switch to using query() instead?

After restarting the MySQL server, the number became 0 and continued to increase over time, eventually leading to this error.

So what is the best solution?

I found some information that seems to be closed, but I see that Com stmt close is only 0. Is this an error? Under what circumstances does it need to be closed?

[sidorares]

double check all variability in your queries is in parameters. Common mistake is to have pert of the query interpolated from a variable, and part from the parameter which triggers preparation for the query each time. Prepared statement is only re-used when query is exactly the same between the calls.

Th example below prepares and immediately executes 10000 statements, even when semantically they are all the same ( the difference is only in the comment ).

for (let i=0; i < 10000; ++i) {
   await.connection.execute(`select 1+? as result -- we are adding 1 to $[i} here `);
}

[maoweifan]

I roughly understand what you mean Only identical parameters will reuse prepared ones If there are a large number of non repeating parameters, it will cause this error

If there is a large amount of non repetition, should we change it to query() here

perhaps

Is there a method provided to close prepared

[maoweifan]

I roughly understand what you mean Only identical parameters will reuse prepared ones If there are a large number of non repeating parameters, it will cause this error

If there is a large amount of non repetition, should we change it to query() here

perhaps

Is there a method provided to close prepared

@sidorares

[sidorares]

Is there a method provided to close prepared

yes, when you in "manual" mode, e.g instead of execute(sql, params) you use connection.prepare(sql), statement.execute(params), statement.close()

see https://sidorares.github.io/node-mysql2/docs/documentation/prepared-statements#manual-prepare--execute