Knex / MySQL Timeout Issue Postmortem

gistfile1.md

For several weeks our production deployment (Express, Bookshelf/Knex -> MySQL) was randomly having queries fail resulting in 500 errors and server crashes.

The first issue is that the Knex library was using Pool2 slightly incorrectly, and when a ETIMEDOUT error occurred, it tried to release the pooled connection twice. This would create an exception which would crash express (unless you have a top level error handler defined in your express setup)

In this issue (myndzi/pool2#12 (comment)) filed on pool2 by the author of knex (tgriesser) the author of pool2 (myndzi) points out the error handling issue and created a fork of knex with the fix git+https://github.com/myndzi/knex#double-release

After installing the fix on my server, whenever ETIMEDOUT occured an error would be generated in the expected part of the code so that I could decide to retry the query or fail gracefully. Removing the ETIMEDOUT entirely would be the best solution however. Myndzi helpfully pointed out that the problem was likely that my Google Cloud SQL server was timing out idle connections in the pool after 10 minutes, and that while pool2 has a facility to ping a connection to test if it is alive, by default this ping operation is a noop. Setting a useful ping command (like SELECT 1) in the knex config would be the suggested fix if you wanted to keep database connections in a pool. If you simply wanted to re-create connections each time you could set the pool min value to 0, so that it didn't try to keep them around.

To recap, if you're using Knex 0.8.x and above with a SQL backend you should do the following:

Patch Knex

Use git+https://github.com/myndzi/knex#double-release until knex integrates that fix.

Set your knexfile to something like:

  client: 'mysql',
  pool: {
    min: 0
  }
  ...
}

Alternatively, set your knexfile to something like:

  client: 'mysql',
  pool: {
    min: 2,
    max: 10,
    ping: function (conn, cb) { conn.query('SELECT 1', cb); }  }
  ...
}

this has been driving me crazy! I thought it was something about the aws lambda environment or something wacky - thanks!

accroding to changelog
of the knex, it's now using tarn as the pooling util, so the ping patch would not work anymore.

Setting the pool.min to 0 sort of worked for me, but not really. I decided to try adding the db version to the config and it fixed it! I can't believe it was that simple. Hope this helps anyone else who runs into this issue in the future.

production: {
    client: 'mysql',
    version: '10.3.25',
    ...
}