2

I have web app running on NodeJS + MySQL. Initially the web app works fine,but all of a sudden the MySQL connection gets refused with following error being thrown:

ECONNREFUSED 127.0.0.1:3306

Simply restarting the server with pm2 reload solves the issue temporarily.But again after a long span of time,the above error creeps in.

The configuration in NodeJS for making MySQL connection is as following:

"sqlconn": {
        "connectionLimit": 10,
        "host": "127.0.0.1",
        "user": "root",
        "password": "XYZ",
        "database": "test",
        "port": 3306,
        "multipleStatements": true
}

Any idea on how to resolve this issue?

NOTE: I am using a digital ocean droplet with RAM size 512MB

Ayan
  • 8,192
  • 4
  • 46
  • 51

2 Answers2

1

To check what was going wrong,I opened the MySQL log file:

/var/log/mysql/error.log

The logs read something like following:

InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
InnoDB: Cannot allocate memory for the buffer pool
InnoDB: Plugin initialization aborted with error Generic error
Plugin 'InnoDB' init function returned error.
Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Failed to initialize plugins.

So the reason was that Mysql can't restart because it's out of memory.

To resolve the memory issue,this answer can be followed : https://stackoverflow.com/a/32932601/3994271

The possible solutions as discussed in the above link are :

  • Increasing RAM size , or
  • configuring a swapfile

I decided to go with configuring a swapfile.

The following link provides details on configuring a swap file: https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-14-04

Ayan
  • 8,192
  • 4
  • 46
  • 51
0

You must be not closing the mysql connections while doing the query. If you keep the connections open, it would give up after sometime.

Also there is a bug in node-mysql. You can use mysql pool.enter link description here

kawadhiya21
  • 2,458
  • 21
  • 34
  • all connections are being closed.I am doubting if my firewall is blocking the connection by any chance. – Ayan Nov 24 '17 at 04:07
  • No, else it wouldnt have worked in the first place at all. – kawadhiya21 Nov 24 '17 at 04:08
  • I guess I do remember this now. I too faced this. Please use mysql connection pool. This would solve the issue. – kawadhiya21 Nov 24 '17 at 04:11
  • It was not due to not using pool.The reson was low memory.I have added the solution as an answer.Also using pool is a bit risky since the pool is initiated only on server start,and once mysql connection drops by any chance,then all the mysql queries would fail.So either separate mysql connections should be used,or we should implement mechanism that watches if the mysql connection pool drops ,and tries to reconnect. – Ayan Nov 24 '17 at 08:29
  • Thanks for the answer. – kawadhiya21 Nov 24 '17 at 11:42