Prepared statement does not work for Symbol operators

[SnosMe]

What are you doing?

class User extends Model {}
User.init({
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING },
  failed_logins: { type: DataTypes.INTEGER }
}, {
  sequelize,
  tableName: 'users'
});

What do you expect to happen?

await User.update({
  failed_logins: 0
}, {
  where: {
    id: 1,
    failed_logins: { [Op.eq]: 4 }
  }
});

Mysql general_log shows

# Actual
Prepare | UPDATE `users` SET `failed_logins`=? WHERE `id` = ? AND `failed_logins` = 4

But it is expected that failed_logins = ?

# Expected
Prepare | UPDATE `users` SET `failed_logins`=? WHERE `id` = ? AND `failed_logins` = ?

Environment

Dialect:

• [ ] mysql
• [ ] postgres
• [ ] sqlite
• [ ] mssql
• [x] any Dialect mysql2 version: 1.6.5 Database version: 8.0.14 Sequelize version: 5.8.6 Node Version: v10.15.0 OS: Win10

[SnosMe]

And a few questions?

1. In what situations are prepared statements expected to work? At the moment, they do not work at all for Model.findAll() and works buggy for Model.update(). Did not try other methods.
2. Can I opt out using prepared statements for single query? Or at least for all queries, something like new Sequelize({ usePrepared: false }) ?

[papb]

Hello, I did not understand.

At the moment, they do not work at all for Model.findAll() and works buggy for Model.update().

Can you clarify this with a MVCE?

Also, by opting out of prepared statements, what would you expect to happen instead?

[SnosMe]

class Post extends Model {}
Post.init({
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  data: { type: DataTypes.STRING },
  year: { type: DataTypes.INTEGER }
}, {
  sequelize,
  tableName: 'post'
})

Test cases (Sequelize v5.10.2):

1. update, using where "field: cond"

await Post.update({ data: 'lorem' }, {
  where: { year: 2018 }
})

2. update, using advanced syntax

await Post.update({ data: 'lorem' }, {
  where: { year: { [Op.eq]: 2018 } }
})

This is the most sneaky. This will create a new plan for every distinct where condition. If a connection is reused for a long time, bad things will happen, see #10832

3. findAll

await Post.findAll({
  where: { year: 2018 }
})

I just discovered that it is "by design" for all SELECT related https://github.com/sequelize/sequelize/issues/3495#issuecomment-402028403

4. increment

await Post.increment('year', {
  where: { year: 2018 }
})

Does not use PREPARE EXECUTE workflow, although this is a kind of update and must use it internally.

---

Also, by opting out of prepared statements, what would you expect to happen instead?

I expect that the query is executed in the default “text” mode, all substitutions are performed on the client side, as in case 3, 4. +1 https://github.com/sequelize/sequelize/issues/3495#issuecomment-461118720 for opting out of prepared statements

It is very strange that maintainers merged this feature in the release. It works 50/50% (cases where it should- and should not- work are not mentioned in docs), have bugs (partially covered with tests?). And with this alpha implementation, you are forced to be an alpha tester, because there is no way to opt out.

[ralexandr]

Is there any updates on this? Met same issue

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[katreniak]

The substituted parameter values inside prepared statements cause prepared statements to leak inside db. To release them, the db connection has to be closed.

The db connections are typically closed on connection options.pool.idle timeout. But when the workload is able to keep all options.pool.max connections busy for long enough time, eventually the (in case of mysql) sysvar_max_prepared_stmt_count limit is reached and all subsequent db requests are denied with Can't create more than max_prepared_stmt_count statements.

Is there a way to prevent this problem?

[ephys]

That issue about prepared statements not being cleared is being tracked here: https://github.com/sequelize/sequelize/issues/10832, I'll post a status update over there

[mahelbir]

Any updates?

[ephys]

This was fixed in Sequelize 7 (pr), but it is very unlikely we'll be able to backport the fix

[severinolorillajr]

@ephys quick confirmation.

We are at 6.24.X as well. And maxed out the prepared statements on PROD. Need to keep rebooting our engines to clear this out. Is this also an issue with raw queries? Do we have known issues related to this? We don't have much Op.x on our usage at all.

[katreniak]

Our workaround is to limit the number prepared statements per connection. The prepared statements are seldom measurable in a bigger application. We did not measure any difference.

  options: {
    dialect: 'mysql',
    dialectOptions: {
      maxPreparedStatements: 2, // countermeasure for https://github.com/sequelize/sequelize/issues/10832
    },
  }