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