# MysqlDBNode reuses the same connection for all access

**URL:** <https://discourse.nodered.org/t/mysqldbnode-reuses-the-same-connection-for-all-access/4856>\
**Category:** Feature Requests\
**Created:** [14 November 2018 08:47 UTC](https://discourse.nodered.org/t/mysqldbnode-reuses-the-same-connection-for-all-access/4856 "2018-11-14T08:47:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![svrist](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/svrist/32/3738_2.png) [@svrist](https://discourse.nodered.org/u/svrist)\
**Post date:** [14 November 2018 08:47 UTC](https://discourse.nodered.org/t/mysqldbnode-reuses-the-same-connection-for-all-access/4856/1 "2018-11-14T08:47:24Z")

</div>

We have a node-red flow with multiple API endpoints GET /api/this, GET /api/that etc. that all access the database. Most often simple "SELECT something FROM table"

We observed that if one of those endpoints did a query that took a bit longer (seconds) - all other endpoints accessing the MySQL would hang until that slow request finished.

Looking at the mysql `SHOW PROCESSLIST` it shows only a single connection from node-red

```auto
+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| 9 | nodered | localhost:37780 | te | Sleep | 18 | | NULL | 0.000 |
+-----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+

```

And when the slow query is running that connection is then serving that query.

Looking at the node-red-node-mysql I looks like a mysql connection pool with allowed 25 connections is setup - but then only one connection is collected and used in perpetuity

I tried patching the node\_modules/ installed version of node-red-node-mysql/68-mysql.js to use the `pool` to do `query` instead of `connection` as the mysqljs/mysql documentation mentions `pool.query` as a short hand for `getConnection, query, release`:

```auto

diff --git a/storage/mysql/68-mysql.js b/storage/mysql/68-mysql.js
index a3362fa..0628f7f 100644
--- a/storage/mysql/68-mysql.js
+++ b/storage/mysql/68-mysql.js
@@ -121,7 +121,7 @@ module.exports = function(RED) {
                     if (typeof msg.topic === 'string') {
                         //console.log("query:",msg.topic);
                         var bind = Array.isArray(msg.payload) ? msg.payload : [];
- node.mydbConfig.connection.query(msg.topic, bind, function(err, rows) {
+ node.mydbConfig.pool.query(msg.topic, bind, function(err, rows) {
                             if (err) {
                                 node.error(err,msg);
                                 node.status({fill:"red",shape:"ring",text:"Error"});

```

Running with that I see multiple connections, but not neccesarily all 25. I figure it's smart about only allocation new when needed.

```auto
+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+
| 94 | nodered | xxx.xx.xx.xx:48062 | beta | Sleep | 199 | | NULL | 0.000 |
| 95 | nodered | xxx.xx.xx.xx:48064 | beta | Sleep | 48 | | NULL | 0.000 |
| 99 | nodered | xxx.xx.xx.xx:48164 | beta | Sleep | 18 | | NULL | 0.000 |
| 100 | nodered | xxx.xx.xx.xx:48200 | beta | Sleep | 78 | | NULL | 0.000 |
+-----+-------------+--------------------+------+---------+------+--------------------------+------------------+----------+

```

And it alleviates the hanging API endpoints when running concurrently with slow queries.

For now I'll run with `patch-package` but would you consider accepting this patch?

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [14 November 2018 08:53 UTC](https://discourse.nodered.org/t/mysqldbnode-reuses-the-same-connection-for-all-access/4856/2 "2018-11-14T08:53:32Z")

</div>

A PR would be welcome.

---

<div class="post-metadata">

**Author:** ![svrist](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/svrist/32/3738_2.png) [@svrist](https://discourse.nodered.org/u/svrist)\
**Post date:** [14 November 2018 15:59 UTC](https://discourse.nodered.org/t/mysqldbnode-reuses-the-same-connection-for-all-access/4856/3 "2018-11-14T15:59:30Z")

</div>

Cool! [https://github.com/node-red/node-red-nodes/pull/504](https://github.com/node-red/node-red-nodes/pull/504)