MySQL Prepared Statement Count Error

Ever encountered this error?

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

I have. I came across it after deploying an update to one of our Node.js apps, specifically a worker with bulk data creation and updates. So I'm writing this as a note for my future self.

Here's the gist of it:

Some Sequelize operations create Prepared Statements (e.g. operations with Op.in etc.).
"A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency." - W3Schools
These prepared statements are reused over time, with variables replaced (if done correctly) so they are cached by the MySQL server.
There's a limit to the number of prepared statements that can be stored in the cache (16382 by default).
When this limit is exceeded, all connections to the Database fail and your app will become unresponsive.

Unfortunately, as I used Sequelize and the default number of cached prepared statements is 16000 (per connection!!!), my app crashed - all processes couldn't connect to the Database and I was shown the error above.

You can prevent this by setting the maxPreparedStatements value of your Sequelize config's dialectOptions property to a value where

max_prepared_stmt_count >= number of connections * value

e.g. 100, in the SequelizeOptions shown below

import { Options } from 'sequelize/types';

const config: Options = {
  username: process.env.DB_USER!,
  password: process.env.DB_PASSWORD!,
  database: process.env.DB_DATABASE!,
  host: process.env.DB_HOST!,
  port: +process.env.DB_PORT! || 3306,
  dialect: 'mysql',
  logging: false,
  pool: {
    max: 20,
    min: 0,
    acquire: 60000,
    idle: 10000,
  },
  dialectOptions: {
    bigNumberStrings: true,
    /* here */
    maxPreparedStatements: 100,
  },
}

Assuming we have 5 Node.js processes using the config above, we'll have

totalCachedStatements = 5 * 100 * 20
totalCachedStatements = 10000

and since it's below 16382, it's gonna be some smooth sailing for our application.

PS: Another option to solve this was to increase the value of the max_prepared_stmt_count on the MySQL server but I didn't want to do that as you might not have access to a privileged user to run the commands.

For more info, you can read