Database connection timeouts after some time and strapi crashed

[sarsonj]

Bug report

Describe the bug

I am using Strapi as dockerised app inside Caprover PAAS. What happens is that when some time of inactivity, the app crashed in the next request. It looks like that connection to database is terminated.

This happens with both mysql and posgresql backend.

The strapi crashes then.

Steps to reproduce the behavior

1. install strapi as dockerized app into Caprover PAAS
2. use one-click mysql or pgsql app

Logs:

2020-10-22T11:20:22.986874066Z [2020-10-22T11:20:22.986Z] debug GET /blog-articles?_start=0&_limit=5&_sort=Date:DESC&IsPublished=true (23 ms) 200
2020-10-22T11:20:23.181097954Z [2020-10-22T11:20:23.180Z] debug GET /blog-articles/count?IsPublished=true (15 ms) 200
2020-10-22T11:46:05.945389043Z [2020-10-22T11:46:05.945Z] error Error: read ECONNRESET
2020-10-22T11:46:05.945432223Z     at TCP.onStreamRead (internal/stream_base_commons.js:208:20)
2020-10-22T11:46:05.945438229Z     --------------------
2020-10-22T11:46:05.945442289Z     at Protocol._enqueue (/src/app/node_modules/mysql/lib/protocol/Protocol.js:145:48)
2020-10-22T11:46:05.945446377Z     at Connection.query (/src/app/node_modules/mysql/lib/Connection.js:208:25)
2020-10-22T11:46:05.945450155Z     at /src/app/node_modules/knex/lib/dialects/mysql/index.js:135:18
2020-10-22T11:46:05.945453786Z     at new Promise (<anonymous>)
2020-10-22T11:46:05.945458024Z     at Client_MySQL._query (/src/app/node_modules/knex/lib/dialects/mysql/index.js:129:12)
2020-10-22T11:46:05.945461647Z     at Client_MySQL.query (/src/app/node_modules/knex/lib/client.js:169:17)
2020-10-22T11:46:05.945465213Z     at Runner.query (/src/app/node_modules/knex/lib/runner.js:151:36)
2020-10-22T11:46:05.945468775Z     at /src/app/node_modules/knex/lib/runner.js:40:23
2020-10-22T11:46:05.945472593Z     at /src/app/node_modules/knex/lib/runner.js:277:24
2020-10-22T11:46:05.946054687Z [2020-10-22T11:46:05.945Z] debug GET /blog-articles?_start=0&_limit=5&_sort=Date:DESC&IsPublished=true (7 ms) 500
2020-10-22T11:48:21.500096306Z [2020-10-22T11:48:21.499Z] error Error: read ECONNRESET
2020-10-22T11:48:21.500134178Z     at TCP.onStreamRead (internal/stream_base_commons.js:208:20)
2020-10-22T11:48:21.500139415Z     --------------------
2020-10-22T11:48:21.500143792Z     at Protocol._enqueue (/src/app/node_modules/mysql/lib/protocol/Protocol.js:145:48)
2020-10-22T11:48:21.500149650Z     at Connection.query (/src/app/node_modules/mysql/lib/Connection.js:208:25)
2020-10-22T11:48:21.500153608Z     at /src/app/node_modules/knex/lib/dialects/mysql/index.js:135:18
2020-10-22T11:48:21.500164357Z     at new Promise (<anonymous>)
2020-10-22T11:48:21.500168736Z     at Client_MySQL._query (/src/app/node_modules/knex/lib/dialects/mysql/index.js:129:12)
2020-10-22T11:48:21.500172376Z     at Client_MySQL.query (/src/app/node_modules/knex/lib/client.js:169:17)
2020-10-22T11:48:21.500176009Z     at Runner.query (/src/app/node_modules/knex/lib/runner.js:151:36)
2020-10-22T11:48:21.500179811Z     at /src/app/node_modules/knex/lib/runner.js:40:23
2020-10-22T11:48:21.500183483Z     at /src/app/node_modules/knex/lib/runner.js:277:24

System

• Node.js version: 12 LTS
• NPM version:
• Strapi version: 3.2.4
• Database: mysql or pgsql
• Operating system: docker - using node:lts-alpine

[derrickmehaffy]

error Error: read ECONNRESET

Based on this error it leads me to believe the issue lies above Strapi, either some kind of DB proxy/load balancer or an issue with the database itself. That error means Strapi is trying to reconnect to the database and is unable to (database is killing the connection).

I'm marking this as closed as without steps to reconstruct the environment to look at directly this will be almost impossible to debug. Please do feel free to respond, though I recommend in cases like this we move the discussion over to the forum as it's highly unlikely this is an issue with the code base (thus a bug) and is more likely environmental or configuration issue.

[sarsonj]

I found solution - the problem is knex related that happens together with docker swarn. Because it is not unusual deployment, I am writing solution here.

The problem is, that docker swarn closes inactive connections in about 15 minutes. However, default knex configuration is to have connection pool with min: 2 connections always available. And those 2 connections broke.

The problem is described there: https://success.mirantis.com/article/ipvs-connection-timeout-issue

The solution is to set knex to have min: 0 always on connections in connection pool. I suggest to set default options into strapi to have this value. My config now works:

module.exports = {
  defaultConnection: 'default',
  connections: {
    default: {
      connector: 'bookshelf',
      {
        client: 'mysql',
        host: 'xxxx',
        port: xxx,
      };
      options: {
        "debug": true,
        "pool": {
          "min": 0,
          "max": 10,
          "idleTimeoutMillis": 30000,
          "createTimeoutMillis": 30000,
          "acquireTimeoutMillis": 30000
        }
      }
    }
  }
};

[derrickmehaffy]

Ah yes you are correct @Satrya2405 for some reason I though Knex's default was 0, but it is in fact 2: http://knexjs.org/#Installation-pooling

I'll add this to our documentation backlog, though I don't think it's good to provide these by default in the code-base as it really only applies to containers that kill connections.

[derrickmehaffy]

Reopening to get @alexandrebodin's thoughts on this, as our existing documentation and structure doesn't really give any suggestions on modifying the database.js within the container.

@alexandrebodin is this better in the docs or should we just apply the min: 0 globally to Bookshelf DBs?

[bykof]

We had also postgres connection problems, which caused Connection terminated unexpectedly.
Setting min: 0 has fixed it.

[NgyAnthony]

Bumping this for visibility. I initially thought it was a network issue since all my services (including DB) are behind Traefik/on Docker. If you could add that min:0 is maybe needed in the docs that would be great.

[alexandrebodin]

we coudl certainly add min:0 as default. if any one wants to make a PR at some point feel free to do it & reference this issue :)