Too many connections Error - MySql

[PraneetB]

Hi,

I’m using NodeJs server model with Docker and Mysql DB. The DB is within docker itself. Currently around ~50 users use this application daily and with 8-10 hours of heavy usage. It has been 2 times that the application has crashed and everyone gets this error:

How can I prevent this error from coming up again? Also we will be going from 50 to 200 users very soon. The only way I know right now to make my server live again is “restart all services” from the Wappler app.

Given this situation, I’m super anxious to leave my laptop for even a few hours thinking what if the server crashes and I am not available to restart it. Our customer will lose on time and revenue. Would love to know how this can be resolved for good!

[PraneetB]

It happened once again Below is the exact error. Can someone help point me in the right direction on this?

{“status”:“500”,“code”:“ER_CON_COUNT_ERROR”,“message”:“ER_CON_COUNT_ERROR: Too many connections”,“stack”:“Error: ER_CON_COUNT_ERROR: Too many connections\n at Handshake.Sequence._packetToError (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)\n at Handshake.ErrorPacket (/opt/node_app/node_modules/mysql/lib/protocol/sequences/Handshake.js:123:18)\n at Protocol._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:291:23)\n at Parser._parsePacket (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:433:10)\n at Parser.write (/opt/node_app/node_modules/mysql/lib/protocol/Parser.js:43:10)\n at Protocol.write (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:38:16)\n at Socket. (/opt/node_app/node_modules/mysql/lib/Connection.js:88:28)\n at Socket. (/opt/node_app/node_modules/mysql/lib/Connection.js:526:10)\n at Socket.emit (events.js:315:20)\n at addChunk (_stream_readable.js:295:12)\n --------------------\n at Protocol._enqueue (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:144:48)\n at Protocol.handshake (/opt/node_app/node_modules/mysql/lib/protocol/Protocol.js:51:23)\n at Connection.connect (/opt/node_app/node_modules/mysql/lib/Connection.js:116:18)\n at /opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:69:18\n at new Promise ()\n at Client_MySQL.acquireRawConnection (/opt/node_app/node_modules/knex/lib/dialects/mysql/index.js:64:12)\n at create (/opt/node_app/node_modules/knex/lib/client.js:291:39)”}

[sid]

Hi,

Haven’t used Docker yet, so not sure if this will help. But a quick Google of the error description returned this very old post: https://github.com/mysqljs/mysql/issues/1244
Maybe check your Docker settings, increase its resources, and take a look at that max_connections setting in MySQL too as described in the post.

[PraneetB]

Thanks Sid,

I found out there’s a setting in Knex which the nodeJS server model uses to manage DB connections through one of Patrick’s posts. It seems that the default pool in Knex is max 10 connections. Have increased it to 30 from db.json file.

“pool”: {
“min”: 2,
“max”: 30
}

@patrick Is this approach okay? or do you see any problems with it?

[patrick]

Adding it to the db.json is the best way I think

[PraneetB]

Hi @patrick @George once I add the pool variable to the file, it doesn't get saved. This is what it looks like when I'm trying to save it.

{
"name": "db",
"module": "dbconnector",
"action": "connect",
"options": {
"client": "mysql",
"connection": {
"host": "db",
"port": 3306,
"user": "db_user",
"password": "xxxx",
"database": "xxxx"
},
"pool": {
"min": 2,
"max": 50
}
}
}

Once I hit save and deploy, the pool variable totally disappears if I close Wappler and open the file again. So I'm not sure whether the max setting has been applied or not...

How do I resolve this?

[otavionestares]

I had this problem too, I configured the production settings with mySQL IP address when I changed to “localhost” (instead of IP address) the messages stopped.

[PraneetB]

Hey @otavionestares where did you configure this? I believe you’re mentioning about this post: Database Manager: Connection Error - Local Docker

Unfortunately my problem is a bit different, I am able to connec to the database, but on enough concurrent connections it gives a “max_connections” error mentioned in my OP to all users. Essentially, mysql just shuts down.

[PraneetB]

I ended up trying this and the app is working fine. Though I’m not able to see the DB in the DB manager as probably it is trying to connect to my system’s localhost. Did you face the same?

How did you solve for this?

[George]

You probably mixed up when editing the db.json file and changed the direct connection as well.

Anyway we will add the pool options on the UI so you don’t have to edit manually.

[PraneetB]

Hi @George I believe you missed the posts in between. I tried the pool setting but once I save and deploy the change, if I close Wappler and open it back, the db.json file still shows the old settings without the pool variable.

Also I increased the pool max connections to 50 and there are about 70 users who log in so I doubt they’re using up 50 concurrent connections. Users are still facing the error from time to time. So the pool setting hasn’t gotten applied.

Setting on the UI would be awesome!

[otavionestares]

Het @PraneetB!

Here is my Database Connection

To connect on Database Manager, I’m using the direct connection and using the IP Address:

Hope this helps!

[PraneetB]

Thanks a lot @otavionestares that helps. Btw, from where do you get the first screen from?

This is on clicking the Server Connect connection, is it or somewhere else?

[otavionestares]

On server connect, pick one action, select Database Connection and then Connection Options

[PraneetB]

Strange I don’t have that option. Is it a new feature? I’m on 3.2.2

[sid]

That’s most likely because you are on NodeJS.
You will have to edit the database connection settings in the DB Manager UI to get the similar connection settings UI. It’s somewhere there, but not sure.

[patrick]

I’m don’t know how knex internally works, but we create a new knex instance on each request, it is then reused for the action steps in that request.

The error comes from the database, not from knex. So you should not increase the pool in knex. If knex holds a pool per instance, then setting a high pool will have a negative effect, resulting in more connections. I think setting the pool to a minimum will have more effect.

Like:

pool: { min: 0, max: 1 }

A single request doesn’t need more then a single connection since it doesn’t do parallel actions, it should clear that connection when the request ends. Couldn’t find a destroy method on the knex instance, so I’m not sure how it handles that internally.

[PraneetB]

Thank you @patrick for the explanation of Wappler’s implementation of Knex. That makes sense and clears it a bit more.

Yes, increasing the pool hasn’t helped. The DB is still shutting down from time to time due to this error. I will try the 0-1 setting and report back on this thread.

[PraneetB]

Hi @patrick, have made this change. Also I have been monitoring the SQL server from the Wappler command line.

Found something interesting through the SHOW PROCESS LIST. there are processes which are in SLEEP and each consume one connection. Since our customer has an off day today only about 10-15 users are logging into the system. So it’s not possible for them to use 23 concurrent connections.

So there do seem to be some connections which are not ending and seem to be causing the issue.

The solutions on stackoverflow etc. are to reduce the wait_timeout variable which is default 8 hours to a low number like 1min so sleep connections are automatically shut. Shall I make this change from the command line?

Also any thoughts on what I can check to avoid these sleeping connections can be from the application itself?