Reconnect to mysql on disconnect or error #2323

ammar08429 · 2017-11-13T13:06:04Z

If the MySQL server goes down, or it down scales or something
the previous connection of the application server with MySQL get corrupted and the server starts throwing this issue
PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR Cannot enqueue Query after fatal error
We face multiple errors in such scenarios, In the docs, I couldn't find anything regarding reconnecting the only solution that we have is restarting the service?

I would like to know that, do you have anything
like reconnectTries, reconnectInterval
like other libraries like this mongo library has

The text was updated successfully, but these errors were encountered:

elhigu · 2017-11-13T13:40:46Z

Which knex version are you using? Pool reconnecting after connection failure was improved in 0.14.

There is no special method / event for being able to handle this error.

ammar08429 · 2017-11-14T08:12:56Z

Ok ok that's great I will give it a try, we were using the last 0.12.9 version

treble-snake · 2017-11-14T09:14:55Z

@elhigu Hi!
I started getting those errors, like out of the blue, since I've updated knex from 0.13.0 to 0.14.0.
I'm using mysql: 2.15.0.
Is there a chance that something relevant to mysql reconnection was broken in 0.14.0 release?

Stack trace example:

Internal Error: { Error: Cannot enqueue Query after fatal error.
    at Protocol._validateEnqueue (/var/www/backend/node_modules/mysql/lib/protocol/Protocol.js:201:16)
    at Protocol._enqueue (/var/www/backend/node_modules/mysql/lib/protocol/Protocol.js:139:13)
    at Connection.query (/var/www/backend/node_modules/mysql/lib/Connection.js:208:25)
    at /var/www/backend/node_modules/knex/lib/dialects/mysql/index.js:156:18
    at Promise._execute (/var/www/backend/node_modules/bluebird/js/release/debuggability.js:303:9)
    at Promise._resolveFromExecutor (/var/www/backend/node_modules/bluebird/js/release/promise.js:483:18)
    at new Promise (/var/www/backend/node_modules/bluebird/js/release/promise.js:79:10)
    at Client_MySQL._query (/var/www/backend/node_modules/knex/lib/dialects/mysql/index.js:150:12)
    at Client_MySQL.query (/var/www/backend/node_modules/knex/lib/client.js:211:17)
    at Runner.<anonymous> (/var/www/backend/node_modules/knex/lib/runner.js:149:36)
    at Runner.tryCatcher (/var/www/backend/node_modules/bluebird/js/release/util.js:16:23)
    at Runner.query (/var/www/backend/node_modules/bluebird/js/release/method.js:15:34)
    at /var/www/backend/node_modules/knex/lib/runner.js:61:21
    at tryCatcher (/var/www/backend/node_modules/bluebird/js/release/util.js:16:23)
    at /var/www/backend/node_modules/bluebird/js/release/using.js:185:26
    at tryCatcher (/var/www/backend/node_modules/bluebird/js/release/util.js:16:23) code: 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR', fatal: false }

Update: Just in case, my knex settings:

  "database": {
    "client": "mysql",
    "connection": {
      "host": "127.0.0.1",
      "user": "<...>",
      "password": "<...>",
      "database": "<...>",
      "charset": "utf8"
    },
    "pool": {
      "min": 2,
      "max": 10
    },
    "migrations": {
      "tableName": "<...>",
      "directory": "<...>/migrations"
    },
    "seeds": {
      "directory": "<...>/seeds"
    }
  }

wubzz · 2017-11-14T09:53:38Z

@treble-snake Taking a shot out of the blue here, but given that this seems to be Pool issues, could you try to append evictionRunIntervalMillis: 1000 to your pool config and see if that helps? Example

knex({
    connection: {...},
    pool: {
        evictionRunIntervalMillis: 1000
    },
});

The upgrade of pool library could have more unexpected consequences than discovered in #2322.

treble-snake · 2017-11-14T10:48:44Z

@wubzz Thanks, I'll give it a try!
Though, I don't have any reproduce strategy other than "wait for it" yet :)
So I'm gonna wait for it first to confirm it's not just some random thing and then try pool settings.

Upd: I've update my previous comment with current knex settings, just in case

ammar08429 · 2017-11-14T12:05:42Z

@elhigu
I updated the version of knex, When we downscale or restart MySQL server we still get this error

Cannot enqueue Query after fatal error
 at Protocol._validateEnqueue
(/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:201:16)\\n    at Protocol._enqueue 
(/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:139:13)\\n    at Connection.query 
(/usr/src/app/node_modules/mysql/lib/Connection.js:208:25)\\n    at 
/usr/src/app/node_modules/knex/lib/dialects/mysql/index.js:156:18\\n    at Promise._execute 
(/usr/src/app/node_modules/bluebird/js/release/debuggability.js:303:9)\\n    at 
Promise._resolveFromExecutor 
(/usr/src/app/node_modules/bluebird/js/release/promise.js:483:18)\\n    at new Promise 
(/usr/src/app/node_modules/bluebird/js/release/promise.js:79:10)\\n    at Client_MySQL._query 
(/usr/src/app/node_modules/knex/lib/dialects/mysql/index.js:150:12)\\n    at Client_MySQL.query 
(/usr/src/app/node_modules/knex/lib/client.js:211:17)\\n    at Runner.<anonymous> 
(/usr/src/app/node_modules/knex/lib/runner.js:149:36)\\n    at Runner.tryCatcher 
(/usr/src/app/node_modules/bluebird/js/release/util.js:16:23)\\n    at Runner.query 
(/usr/src/app/node_modules/bluebird/js/release/method.js:15:34)\\n    at 
/usr/src/app/node_modules/knex/lib/runner.js:61:21\\n    at tryCatcher 
(/usr/src/app/node_modules/bluebird/js/release/util.js:16:23)\\n    at 
/usr/src/app/node_modules/bluebird/js/release/using.js:185:26\\n    at tryCatcher 
(/usr/src/app/node_modules/bluebird/js/release/util.js:16:23)\",\"code\":\"
PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR```

treble-snake · 2017-11-14T12:24:23Z

Btw, is it possible to retrieve connection object in order to inject log statement like this one?

ammar08429 · 2017-11-14T12:40:59Z

@elhigu
what exactly is evictionRunIntervalMillis this for?

oliviertassinari · 2017-11-15T09:47:38Z

We have noticed the same error (PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR) after upgrading from knex v0.13.0 to v0.14.0. We are using MySQL on AWS.

oliviertassinari · 2017-11-15T09:49:55Z

It seems to be the same for @NemoAlex #452 (comment).

treble-snake · 2017-11-15T12:40:38Z

I've just performed some tests and can confirm it's reproducible.

Install knex 0.14.0
Use pretty much default config (see below)
Run an app
Restart MySQL server
Got PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR on a next DB call

Same steps work OK (reconnect with no error) with knex 0.13.0.

@wubzz I've tried adding evictionRunIntervalMillis: 1000 to a pool's config, but unfortunately it doesn't help.

But I guess some other cases than MySQL server restarting can also lead to that error, 'cause I most certainly haven't restarted remote MySQL server, but got the same error there.

Knex config:

  "database": {
    "client": "mysql",
    "connection": {
      "host": "127.0.0.1",
      "user": "<...>",
      "password": "<...>",
      "database": "<...>",
      "charset": "utf8"
    },
    "pool": {
      "min": 2,
      "max": 10
      [, "evictionRunIntervalMillis": 1000] // doesn't help
    },
    "migrations": {
      "tableName": "<...>",
      "directory": "<...>/migrations"
    },
    "seeds": {
      "directory": "<...>/seeds"
    }
  }

treble-snake · 2017-11-15T12:43:29Z

@ammar08429 I'd suggest you use 0.13.0 version for now, it seems more stable

elhigu · 2017-11-15T12:58:54Z

@treble-snake I'm fixing some new pool problems to 0.14.1

ammar08429 · 2017-11-15T14:04:48Z

@elhigu Please Re-open this issue
I tried what @treble-snake did.
And i get error in 0.14.0, but in 0.13.0 it reconnects properly

elhigu · 2017-11-16T17:54:33Z

@ammar08429 I though this was a duplicate of #2321 but looks like error message is a bit different. Better to reopen to make sure that this get fixed too.

ckanthony · 2017-11-17T04:48:30Z

+1 on the same situation in 0.14.0

elhigu · 2017-11-19T16:44:46Z

Please test this with 0.14.1 and lets reopen if there are still problems.

treble-snake · 2017-11-20T13:54:08Z

Simple scenario with server's restarting seems to be fixed.

ammar08429 · 2017-11-21T21:21:59Z

Simple restart is working right now

elhigu closed this as completed Nov 13, 2017

elhigu reopened this Nov 16, 2017

elhigu mentioned this issue Nov 19, 2017

More pool tests and test on borrow default #2341

Merged

elhigu closed this as completed Nov 19, 2017

NemoAlex mentioned this issue Nov 21, 2017

Connection reset when idle #2342

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Reconnect to mysql on disconnect or error #2323

Reconnect to mysql on disconnect or error #2323

ammar08429 commented Nov 13, 2017

elhigu commented Nov 13, 2017 •

edited

ammar08429 commented Nov 14, 2017

treble-snake commented Nov 14, 2017 •

edited

wubzz commented Nov 14, 2017 •

edited

treble-snake commented Nov 14, 2017 •

edited

ammar08429 commented Nov 14, 2017 •

edited

treble-snake commented Nov 14, 2017

ammar08429 commented Nov 14, 2017

oliviertassinari commented Nov 15, 2017 •

edited

oliviertassinari commented Nov 15, 2017

treble-snake commented Nov 15, 2017 •

edited

treble-snake commented Nov 15, 2017

elhigu commented Nov 15, 2017

ammar08429 commented Nov 15, 2017

elhigu commented Nov 16, 2017

ckanthony commented Nov 17, 2017

elhigu commented Nov 19, 2017

treble-snake commented Nov 20, 2017

ammar08429 commented Nov 21, 2017

Reconnect to mysql on disconnect or error #2323

Reconnect to mysql on disconnect or error #2323

Comments

ammar08429 commented Nov 13, 2017

elhigu commented Nov 13, 2017 • edited

ammar08429 commented Nov 14, 2017

treble-snake commented Nov 14, 2017 • edited

wubzz commented Nov 14, 2017 • edited

treble-snake commented Nov 14, 2017 • edited

ammar08429 commented Nov 14, 2017 • edited

treble-snake commented Nov 14, 2017

ammar08429 commented Nov 14, 2017

oliviertassinari commented Nov 15, 2017 • edited

oliviertassinari commented Nov 15, 2017

treble-snake commented Nov 15, 2017 • edited

treble-snake commented Nov 15, 2017

elhigu commented Nov 15, 2017

ammar08429 commented Nov 15, 2017

elhigu commented Nov 16, 2017

ckanthony commented Nov 17, 2017

elhigu commented Nov 19, 2017

treble-snake commented Nov 20, 2017

ammar08429 commented Nov 21, 2017

elhigu commented Nov 13, 2017 •

edited

treble-snake commented Nov 14, 2017 •

edited

wubzz commented Nov 14, 2017 •

edited

treble-snake commented Nov 14, 2017 •

edited

ammar08429 commented Nov 14, 2017 •

edited

oliviertassinari commented Nov 15, 2017 •

edited

treble-snake commented Nov 15, 2017 •

edited