Knex timeout on Heroku Hobby (MySQL)

[jeremyrajan]

Describe the bug
Constantly getting Knex timeout error, on Heroku. This was happening on and off with all my strapi deployments on Heroku, which is either using MySQL or Postgres. I have googled quite a fair bit, but still, can't able to figure out what's happening.
Full log:

[2020-10-06T14:35:50.827Z] error KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
Oct 06 07:35:51 cms app/web.1     at Client_MySQL.acquireConnection (/app/node_modules/knex/lib/client.js:349:26)
Oct 06 07:35:51 cms app/web.1     at runNextTicks (internal/process/task_queues.js:58:5)
Oct 06 07:35:51 cms app/web.1     at listOnTimeout (internal/timers.js:523:9)
Oct 06 07:35:51 cms app/web.1     at processTimers (internal/timers.js:497:7)
Oct 06 07:35:51 cms app/web.1     at async Promise.all (index 0)
Oct 06 07:35:51 cms app/web.1     at async createOrUpdateTable (/app/node_modules/strapi-connector-bookshelf/lib/build-database-schema.js:172:26)
Oct 06 07:35:51 cms app/web.1     at async module.exports (/app/node_modules/strapi-connector-bookshelf/lib/build-database-schema.js:402:9)
Oct 06 07:35:51 cms app/web.1     at async /app/node_modules/strapi-connector-bookshelf/lib/mount-models.js:618:7
Oct 06 07:35:51 cms app/web.1     at async Promise.all (index 1)
Oct 06 07:35:51 cms app/web.1     at async Promise.all (index 1)
Oct 06 07:35:51 cms app/web.1     at async Promise.all (index 0)
Oct 06 07:35:51 cms app/web.1     at async Object.initialize (/app/node_modules/strapi-database/lib/connector-registry.js:30:9)
Oct 06 07:35:51 cms app/web.1     at async DatabaseManager.initialize (/app/node_modules/strapi-database/lib/database-manager.js:36:5)
Oct 06 07:35:51 cms app/web.1     at async Strapi.load (/app/node_modules/strapi/lib/Strapi.js:308:5)
Oct 06 07:35:51 cms app/web.1     at async Strapi.start (/app/node_modules/strapi/lib/Strapi.js:190:9)

Steps to reproduce the behavior
npm start on Heroku. I have updated my database config as follows:

{
      defaultConnection: "default",
      connections: {
        default: {
          connector: "bookshelf",
          settings: {
            client: "mysql",
            host: env("DATABASE_HOST", process.env.DATABASE_HOST),
            port: env.int("DATABASE_PORT", process.env.DATABASE_PORT),
            database: env("DATABASE_NAME", process.env.DATABASE_NAME),
            username: env("DATABASE_USERNAME", process.env.DATABASE_USERNAME),
            password: env("DATABASE_PASSWORD", process.env.DATABASE_PASSWORD),
            schema: "public",
            ssl: true,
          },
          options: {
            debug: false,
            pool: {
              min: 2,
              max: 10,
              createTimeoutMillis: 30000,
              acquireTimeoutMillis: 30000,
              idleTimeoutMillis: 30000,
              reapIntervalMillis: 30000,
              createRetryIntervalMillis: 30000,
              propagateCreateError: false,
            },
          },
        },
      },
    };

System

• Node.js version: 14.13.0
• NPM version: 6.14.8
• Strapi version: Strapi: 3.1.7
• Database: mysql": "^2.18.1"
• "knex": "^0.21.6",
• Operating system: Linux

Additional context
This issue is not there initially with a fresh deployment, but over time I see this issue happening. Not really any pattern, there is not a lot of data as well.

Any help will be really appreciated 👍
Cheers,
Jeremy

Reference Issue in Knex: knex/knex#2820

[mmeinzer]

Running into the same issue with Knex (using Objection on top of Knex FWIW).

I'm no where near my database row limit and Heroku shows 12 connections with the same pooling settings as above.

[derrickmehaffy]

There isn't much I feel we can really add to this, the Hobby PG add-on for Heroku spins up the PG server with 256mb of RAM.

PostgreSQL themselves state that 2GB is the minimum recommended RAM

[derrickmehaffy]

As no new information has been provided, marking as closed.

[derrickmehaffy]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/heroku-error-knextimeouterror-knex-timeout-acquiring-a-connection-the-pool-is-probably-full-are-you-missing-a-transacting-trx-call/2618/2