Ghost icon indicating copy to clipboard operation
Ghost copied to clipboard

ECONNRESET when connecting to Azure MySQL for Database

Open massix opened this issue 1 year ago • 8 comments

Issue Summary

Hello there, we have been using ghost version 4.37-alpine deployed in an AKS (Azure Kubernetes Service) in our Azure's Environment for a while now. Ghost itself connects to an Azure MySQL Database version 8 and everything is fine and has been running for a while now (more than an year).

We would like to migrate to Ghost version 5 but we are facing a major issue, which seems to be related to knex-migrator (so, hierarchically speaking to knex and mysql2, but we don't know who's at fault here :D). Basically, it seems like knex is actually able to connect to the database, but when releasing the connection it throws an exception, hence everything fails.

We tried all the solutions provided in different topics (setting the pool_min to 0, increasing all the timeouts in the MySQL settings, ..) but nothing seems to work.

Here is an excerpt of what we're doing:

❯ kubectl run --rm -ti --context=myba-dev --image=ghost:5-alpine --command=true fake-ghost -- /bin/bash
If you don't see a command prompt, try pressing enter.
bash-5.1# export database__connection__password=[REDACTED]
bash-5.1# export database__connection__pool__min=0
bash-5.1# export database__client=mysql
bash-5.1# export database__connection__host=mysql.internal.myba.local
bash-5.1# export database__connection__database=ghost
bash-5.1# export database__pool__min=0
bash-5.1# export database__connection__user=myba@[REDACTED]
bash-5.1#
bash-5.1# cd current
bash-5.1# ./node_modules/knex-migrator/bin/knex-migrator i
node:events:505
      throw er; // Unhandled 'error' event
      ^

Error: read ECONNRESET
    at TCP.onStreamRead (node:internal/stream_base_commons:217:20)
Emitted 'error' event on Connection instance at:
    at Connection._notifyError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:236:12)
    at Connection._handleFatalError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:167:10)
    at Connection._handleNetworkError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:180:10)
    at Socket.emit (node:events:527:28)
    at emitErrorNT (node:internal/streams/destroy:157:8)
    at emitErrorCloseNT (node:internal/streams/destroy:122:3)
    at processTicksAndRejections (node:internal/process/task_queues:83:21) {
  errno: -104,
  code: 'ECONNRESET',
  syscall: 'read',
  fatal: true
}
bash-5.1#
exit
Session ended, resume using 'kubectl attach fake-ghost -c fake-ghost -i -t' command when the pod is running

Steps to Reproduce

  1. Create an AKS
  2. Create an Azure MYSQL Database, using GP_Gen5_4 as SKU (4 cores, General Purpose), charset utf8mb4 and collation utf8mb4_0900_ai_ci
  3. Deploy ghost on the AKS using the right environment variables
  4. Ghost will fail

Ghost Version

5.2.3

Node.js Version

16.15.1

How did you install Ghost?

Kubernetes, Docker

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

node:events:505
      throw er; // Unhandled 'error' event
      ^

Error: read ECONNRESET
    at TCP.onStreamRead (node:internal/stream_base_commons:217:20)
Emitted 'error' event on Connection instance at:
    at Connection._notifyError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:236:12)
    at Connection._handleFatalError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:167:10)
    at Connection._handleNetworkError (/var/lib/ghost/versions/5.2.3/node_modules/mysql2/lib/connection.js:180:10)
    at Socket.emit (node:events:527:28)
    at emitErrorNT (node:internal/streams/destroy:157:8)
    at emitErrorCloseNT (node:internal/streams/destroy:122:3)
    at processTicksAndRejections (node:internal/process/task_queues:83:21) {
  errno: -104,
  code: 'ECONNRESET',
  syscall: 'read',
  fatal: true
}

Code of Conduct

  • [X] I agree to be friendly and polite to people in this repository

massix avatar Jun 29 '22 14:06 massix

I'm running into the exact same issue and was about to open a ticket when I found this one. I also tried enabling debug flags with the env variable DEBUG=* and by adding debug: true under connection in the json settings. I didn't find anything interesting this way.

The bit that crashes is under boot.js:

async function initDatabase({config}) {
    const DatabaseStateManager = require('./server/data/db/state-manager');
    const dbStateManager = new DatabaseStateManager({knexMigratorFilePath: config.get('paths:appRoot')});
    await dbStateManager.makeReady();

    const databaseInfo = require('./server/data/db/info');
    await databaseInfo.init();
}

In my case it is crashing at databaseInfo.init(). What I found interesting is that if we comment the previous 3 lines, databaseInfo.init() will run fine and it will crash the exact same way with a ECONNRESET a little later in the code

ben-clearhead avatar Jun 29 '22 23:06 ben-clearhead

This issue is related to the new library (mysql2) used into Ghost since the version 4.38 to communicate with mysql. https://github.com/TryGhost/Ghost/pull/14258.

The latest version (2.3.3) of the library mysql2 still have an issue with Azure Database Single Server. This issue is now fixed in the main branch of the library but not yet released (see https://github.com/sidorares/node-mysql2/pull/1438).

As workaround you can modify the file lib/connection.js of the library to fix this issue or use Azure Database Flexible Server.

sed -i "s/err.errno === 'ECONNRESET'/err.code === 'ECONNRESET'/g" /var/lib/ghost/current/node_modules/mysql2/lib/connection.js

devnied avatar Jul 03 '22 21:07 devnied

@devnied Thanks for the reference - I've pinged on that PR to see if we can get it released 🙂

daniellockyer avatar Jul 04 '22 13:07 daniellockyer

I just upgraded to 5.2.4 and still getting connection errors. It's interesting because the knex-migrator actually connects and upgrades, but also sends an error at the end of the process.

As you can see in the image, Ghost launches, but several ECONNRESET show up when the site is trying to load. I'm not using AKS however, but an (Windows-based) App Service and MysQL 8.

image

juanramollino avatar Jul 06 '22 19:07 juanramollino

I've pinged upstream again to see if we can get this resolved.

ErisDS avatar Jul 26 '22 15:07 ErisDS

Thanks, @ErisDS. Happy to test out whatever we can. I tried debugging Knex and even mysql2 but it's becoming very complex. That said, I'm happy to guinea pig theories.

juanramollino avatar Jul 29 '22 15:07 juanramollino

Running into same issue, in our case ghost is hosted in a VM and db is Azure MySQL for Database.

aysark avatar Aug 05 '22 23:08 aysark

I'm going to temporarily lock this thread. The issue is a bug in the mysql2 package which is fixed but hasn't been released. There's nothing we can do until mysql2 do a release - so adding comments here achieves nothing other than filling our inboxes with things we can't action.

If you want more information, it's the mysql2 team you need to speak to.

Ghost's renovate configuration means that as soon as the release happens, we'll know about it. At that point we will update, and then unlock and close this issue as completed.

ErisDS avatar Aug 08 '22 11:08 ErisDS

Hi all! 👋🏻 With thanks to upstream, the issue in mysql2 was fixed and a new release was made, so I've just pushed the update to Ghost and it'll be live in the next release 🙂

daniellockyer avatar Jan 12 '23 09:01 daniellockyer