3

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 am using node-mysql2 but I'm thinking that no matter what my prepared statement count will eventually hit 1M.

So my question is how am I supposed to properly recycle these statements so that they don't constantly hit 1M every few weeks?

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')

I'm guessing holding 100 connections and reusing them is causing the prepared statements to eventually hit the max. I see an undocumented feature in node/mysql2 that says maxPreparedStatements that I can add to the config. Will this prevent the issue in the future? Or will I still have to "clear prepared statements" in mysql every now and then (If this is the case what is the command to clear it)?

Note I have 3 replica servers that connect to mysql

Terence Chow
  • 10,755
  • 24
  • 78
  • 141
  • Are you unpreparing the statements? https://github.com/sidorares/node-mysql2/blob/master/documentation/Prepared-Statements.md – flip Jan 05 '18 at 15:20
  • @flip how do I close it if I am using the promise library? My code: `const [results] = await pool.execute(query, values)` – Terence Chow Jan 05 '18 at 15:31
  • Also why would I need to close my statements if maxPreparedStatements defaults to 16000? – Terence Chow Jan 05 '18 at 15:42
  • 1
    I'm not sure how the library works in respect to the pooling and preparing so I'm unable to answer I'm afraid. Here's a link that explains details around prepared statements https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks That should answer why you use prepared statements, and also why you don't need prepared statements 'active' at all times. I find that they improve performance more when I'm looping and inserting data into a table more than once within that loop. i.e. Into a table between a M:M relationship. – flip Jan 05 '18 at 15:50
  • I'm having this issue while using mysql workbench and attempting to add new row using the GUI and not writing SQL statement – Kahn Sparkle May 05 '20 at 13:27

1 Answers1

1

Hopefully you found a response already.

I don't see this with prepared statement, even though I saw it when I was using requests that where different each time a single parameters changed (building the sql request string in JS...).

Are you sure you're using prepared statements ?

Your requests should looks like :

pool.execute('SELECT * FROM ... where id = ? etc etc', [id, ...])

I'm testing https://www.npmjs.com/package/mysql2 v2.2.5.

Make all your requests this way. Maybe activate mysql log on a dev server to check the requests being made.

Nice mysql debug requests to do in mysql cli :

show variables where `variable_name` like  '%stmt%';
show status where `variable_name` like  '%stmt%';

The important values are Prepared_stmt_count status and max_prepared_stmt_count config.

FredG
  • 712
  • 7
  • 10