MySql Node "Error: Pool Is closed"

[saxenaramesh]

Dear Friends,
Believe me, I have done extensive search for this issue on node-red forum and other possible sites suggested by google. Somehow, I am not able to fix it.
I am using mysql node quite frequently. For one of my project, spread across 5 flows, it has been used around 43 times for access to same Mysql Database with same credentials. I need to have various queries for my dashboards. It was working quite well for about a month.
Now, I am facing an issue. I very frequently get error for mysql node : "Error: Pool is closed". I have following questions:

1. How can I possible fix this issue?
2. Is this issue because of using mysql node too many times?

Please help on this issue.

my flow example is as below:

[{"id":"48d3532b.b9f87c","type":"function","z":"7496a53c.c9e7dc","name":"","func":"var d = new Date()\nvar z = d.getFullYear()+'-'+(d.getMonth()+1)+'-'+(d.getDate()-1);\nvar t1 = 0 + \":\" + 0 + \":\" + 0;\nvar t2 = 23 + \":\" + 59 + \":\" + 59;\nvar a = z+' '+t1;\nvar b = z+' '+t2;\nmsg.topic = 'select M1_KVAH as grid_cons from em1_live_values where time_stamp between \"'+a+'\" and \"'+b+'\" and M1_KVAH > 0 order by time_stamp desc limit 1';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":1740,"wires":[["5a1ba33f.40c04c"]]},{"id":"8d85ff65.ae86e","type":"function","z":"7496a53c.c9e7dc","name":"","func":"var str = msg.payload;\nstr = str[0]['grid_cons'];\nmsg.payload = str;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":720,"y":1740,"wires":[["c1a1083e.0bcf28"]]},{"id":"5a1ba33f.40c04c","type":"mysql","z":"7496a53c.c9e7dc","mydb":"18593ab4.649a45","name":"Query","x":510,"y":1740,"wires":[["8d85ff65.ae86e"]]},{"id":"a649c8e4.09b4f8","type":"function","z":"7496a53c.c9e7dc","name":"","func":"var d = new Date()\nvar z = d.getFullYear()+'-'+(d.getMonth()+1)+'-'+(d.getDate()-1);\nvar t1 = 0 + \":\" + 0 + \":\" + 0;\nvar t2 = 23 + \":\" + 59 + \":\" + 59;\nvar a = z+' '+t1;\nvar b = z+' '+t2;\nmsg.topic = 'select M1_KVAH from em1_live_values where time_stamp between \"'+a+'\" and \"'+b+'\" and M1_KVAH > 0 order by time_stamp limit 1';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":1800,"wires":[["cee9dc66.e9f98"]]},{"id":"65920934.1aa7c8","type":"function","z":"7496a53c.c9e7dc","name":"","func":"var str = msg.payload;\nstr = str[0]['M1_KVAH'];\nmsg.payload = str;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":720,"y":1800,"wires":[["c1a1083e.0bcf28"]]},{"id":"cee9dc66.e9f98","type":"mysql","z":"7496a53c.c9e7dc","mydb":"18593ab4.649a45","name":"query2","x":490,"y":1800,"wires":[["65920934.1aa7c8"]]},{"id":"96d99e31.f8f5e","type":"inject","z":"7496a53c.c9e7dc","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":1740,"wires":[["48d3532b.b9f87c","a649c8e4.09b4f8"]]},{"id":"c1a1083e.0bcf28","type":"debug","z":"7496a53c.c9e7dc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":910,"y":1760,"wires":[]},{"id":"18593ab4.649a45","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"Aditya_EMS","tz":"+05:30","charset":"UTF8"}]

[zenofmud]

Did you search the forum for that error msg? I found several hits. Do any of them help?

[saxenaramesh]

Yes, I actually did search with exact same error message. There are quite a few topics, but goes in various directions. I could not find anything which can solve my issue.

[zenofmud]

1. what version of Node-RED and node.js are you running (you can get this from the startup log)
2. What hardware and OS are you using?
3. How many occurances of mysql node do you have in all?
4. Have you tried reducing the number of occurances of the node?

[dynamicdave]

I had a similar error when using the MySQL node last year (2021).

[Feedback wanted] Update to mysql node

I have just updated the node-red-node-mysql node to use the more maintained mysql2 library in response to MySQL 8.0 needs new Auth mechanism · Issue #853 · node-red/node-red-nodes · GitHub - and it is on npm as node-red-node-mysql@next so can be manually installed... eg cd ~/.node-red && npm i node-red-node-mysql@next If anyone has any time to give it a whirl I would really appreciate it. It claims to be nearly compatible with the basic mysql library but there are some known differences, see-…

Are you using the latest version as @dceejay has done a lot of work improving this node, in fact there was an update a week ago.

One of my problems was the number of dB connections. I found when I reduced the number of connections the node behaved much better.

I'm also using this flow to perform a 'keep database alive' every 15-minutes.

[Feedback wanted] Update to mysql node

Upgrade MySQL - working like it suppose to.

[saxenaramesh]

Thanks for your reply.

1. I am using Node red - 1.3.5. & Node.js - 14.18.1
2. I am using Raspberry Pi & Raspbian OS
3. I have 43 mysql node occurances.
4. I tried to the best of my knowledge, still trying actually.

[saxenaramesh]

Hello Zenofmud,

Reducing the number of nodes using mysql is helping. The occurence frequency of "Error: Pool is closed" is reducing. I am wondering if we really need lot of nodes, will it help to use a different mysql schema design. for example:
INSTEAD of,
approach 1: db1 : Table1, Table2, Table3, Table4, Table5
WILL IT HELP TO USE BELOW,
approach 2: db1: Table 1, Table 2,
db2: Table 3, Table 4,
db3: Table 5.
So we will connect to different DBs rather connecting to same DB many times. this may be a substantial work to implement this, what is your opinion, will it improve the situation?

[zenofmud]

I can't really say if splitting tables into different databases will help. I would be examining if you could have multiple queries pass thru the same mysql node. You could add an extra item to the msg benig passed - like msg.table - then look at the msg coming out of the mysql node and test msg.table to determine where the data should go.

[dceejay]

Which version of the MySQL node are you using ?

[saxenaramesh]

Hello, Currently its is 0.2.1. I know there is an update available to 1.0.0. I will try to update it. Is there anything specific enhancement done during 1.0.0 for this particular error?

[dceejay]

Yes there were several fixes around the pooling and memory leaks

[saxenaramesh]

Thanks for the reply. I will upgrade to latest version of mysql node and give you feedback.

[rakgupta]

@saxenaramesh

I will upgrade to latest version of mysql node and give you feedback.

I am seeing the same thing with mySQL node v1.0.0. Were you able to solve this issue? I have 2 instances of Node-RED running (on different machines). I am not seeing this issue on the instance with mySQL node v 0.1.9. Both instances are connected to the same database.

UPDATE: Stopping and restarting Node-RED seems to have fixed it

[saxenaramesh]

Hello, I have reduced the numbers of mysql nodes used by combining multiple queries and then using the data as per requirement. This is working fine now.