knex: Can't add new command when connection is in closed state

Hi, just today we’ve observed (by placing a few good ol’ console.logs) that the factory.validate function which knex.js provides to generic-pool is never called, which might explain how already-dead connections are still being dispensed from the resource pool. Supposedly, generic-pool will validate resources before dispensing, and if they seem broken, create and dispense a new one (in this case, connection) as a replacement. However, this only happens if the right options are passed to generic-pool: https://github.com/coopernurse/node-pool/blob/master/lib/Pool.js#L222

As seen in the code above, the pool will only actually run the validator function if the option testOnBorrow has been enabled. It is false by default, and knex.js does not seem to override it when creating the pool.

@rkaw92 I am testing this workaround since yesterday morning and it seems to work fine. Before that I had to restart API processes at least once a day, otherwise database connection will eventually close without reconnection.

@rkaw92 sounds good, now we just need a test case and make knex to enable that setting always. I will do that if I can get first one other bug fixed that I’m working on.

I found that this was being caused due to importing a large file. I fixed it by setting “max_allowed_packet=32505856”

Yeah, you can pass other pool options, too.

@leebenson One thing to try without code modification is to pass testOnBorrow to knex, like so:

const db = require('knex')({
  client: '...',
  connection: '...',
  pool: { testOnBorrow: true }
})

This should tell you if it’s caused by the connection recycling bug. Of course, it’s just a temporary workaround, but it would be helpful if we can see whether it’s this or another sneaky one.

If successful, at the time when the connection fails, you’re going to see this printed to console.log:

Knex:warning - Connection Error: Connection ended unexpectedly

And the next thing should be the pool allocating a fresh connection for you.