knex: Error: Connection reset after several hours of idle time

Environment

Knex version: 0.19.0 Database + version: Mysql 5.1.73 OS: Debian

Knex config

This is my knex configuration

knex({
    client: 'mysql',
    connection: {  ...  },
  })

Bug

After a few hours of idle time in the application, we get a lot of read ECONNRESET. I wouldn’t mind having this issue, if it was either handled by knex or tarn, but the error bubbles up to our application. This is the stack we get:

 Error: read ECONNRESET
  at TCP.onStreamRead (internal/stream_base_commons.js:111:27)
  at Protocol._enqueue (/usr/app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
  at Connection.query (/usr/app/node_modules/mysql/lib/Connection.js:201:25)
  at /usr/app/node_modules/knex/lib/dialects/mysql/index.js:130:18
  at Promise._execute (/usr/app/node_modules/bluebird/js/release/debuggability.js:313:9)
  at Promise._resolveFromExecutor (/usr/app/node_modules/bluebird/js/release/promise.js:488:18)
  at new Promise (/usr/app/node_modules/bluebird/js/release/promise.js:79:10)
  at Client_MySQL._query (/usr/app/node_modules/knex/lib/dialects/mysql/index.js:124:12)
  at Client_MySQL.query (/usr/app/node_modules/knex/lib/client.js:158:17)
  at Runner.query (/usr/app/node_modules/knex/lib/runner.js:135:36)
  at /usr/app/node_modules/knex/lib/runner.js:39:23
  at tryCatcher (/usr/app/node_modules/bluebird/js/release/util.js:16:23)
  at /usr/app/node_modules/bluebird/js/release/using.js:185:26
  at tryCatcher (/usr/app/node_modules/bluebird/js/release/util.js:16:23)
  at Promise._settlePromiseFromHandler (/usr/app/node_modules/bluebird/js/release/promise.js:517:31)
  at Promise._settlePromise (/usr/app/node_modules/bluebird/js/release/promise.js:574:18)
  at Promise._settlePromise0 (/usr/app/node_modules/bluebird/js/release/promise.js:619:10)

I know there are already a few issues opened (https://github.com/tgriesser/knex/issues/3037 for instance), but as @elhigu sugggested, I opened a new issue.

Apart from this issue, is there any plan for knex to support validating a request in order to keep a healthy pool ?

If I understand the code correctly, there is already a validateConnection function that gets passed to Tarn.js. However, this seems to always validate the connection, no matter what

In client.js, line 302 (on master branch):


  validateConnection(connection) {
    return true;
  },

About this issue

Original URL
State: open
Created 5 years ago
Comments: 33 (18 by maintainers)

Most upvoted comments

@manjufy the issue with knex is not that it is losing connection — this is networking, such issues are always present and it is normal to have them. Issue with knex that it doesn’t handle this and doesn’t reconnect when needed. If it manages the pool — it should manage it fully. If connection is stale/lost - it should verify it and reconnect.

Also it uses tarn under the hood: https://knexjs.org/guide/#pool

And uses it with default settings with validate: ()=>true and pool: { min: 2, max: 10 }

And the guide says that this is a bad pool limit config due to problems with stale connections (exactly the issue) and recommended to be min:0. SO WHY THE HELL do you use bad default config instead of recommended??

And the same goes for validate: ()=>true — ok, tarn is a generic library, so they set there a mock for library users to override with proper validation function. But knex — is the user of a library, they own/know all connection settings — why validation function is not set properly to detect lost connections?

I have already yesterday changed pool limits to 0—30 and will monitor if this helps, but even iv it will — this should be the default setting and a proper validation function also must be there

terion-name on Nov 19, 2022

@elhigu I don’t mind having this extra query sent to server. How can I add the validation query to validation function ? I may have missed something from the docs, but I didn’t find any examples.

bdahon on Sep 25, 2019