# Database connection but not a connection?

**URL:** <https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495>\
**Category:** General\
**Created:** [22 June 2021 19:08 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495 "2021-06-22T19:08:10Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [22 June 2021 19:08 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/1 "2021-06-22T19:08:10Z")

</div>

I just notice this recently, but I randomly see the following:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/1/41108dcc6d87bf506bec5b5735949af41b217c63.png)

But when I check the node:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/1/112df2e7d8286fbe304345311538bd3a4b59f539.png)

This some type of timing thing? intermittent network issue? Seems more annoying than anything else. My database server is rock solid, no warnings or errors, non of my other applications, python scripts, etc., are reporting connection issues with the database.

I can't seem to create the issue on demand, for example restart of NR does not exhibit the behavior. Looking through the database server log, not seeing spurious disconnects, so still looking for the issue, but though someone might have seen this or suggestions. Thinking at some point may setup a watch-dog routine to watch the database connections. LOL. One more quirky thing to track down.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 June 2021 19:18 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/2 "2021-06-22T19:18:35Z")

</div>

Which database is that? There are quite a few to choose from.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [22 June 2021 19:35 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/3 "2021-06-22T19:35:38Z")

</div>

Ah, right, I should have noted... MariaDB in this case.

Server version: 10.3.27-MariaDB-0+deb10u1-log Raspbian 10

So mysql variant. It is interesting I have been using MariaDB for years, and with NR now for over a year? If memory serves. Very few issues thus far. I have one instance running on a Pi, another on an old i386 Linux system. The Pi trans-log ships deltas to the i386 system, whereas NR flows connect to the Pi instance. Checked the /var/log/mysql/error.log, no errors during the time frame of the last hiccup.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [22 June 2021 20:17 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/5 "2021-06-22T20:17:48Z")

</div>

Since the next line in the code after that is to set the status to "not yet connected" that suggests that it was a transient issue and by the time you looked at the node it had already connected.

Is node-red restarting possibly? Look in syslog or the node red log to find out.

Also you could attach a Catch node and a Status node and connect them to debug nodes set to output to the console. Then you will be able to look back through the node-red log (or syslog) to get a clue as to what is going on.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [22 June 2021 20:29 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/6 "2021-06-22T20:29:25Z")

</div>

Actually I wonder if there is a bug there. @dceejay I see in `./node_modules/node-red-node-mysql/68-mysql.js` the code

```auto
                else {
                    node.error("Database not connected",msg);
                    status = {fill:"red",shape:"ring",text:"not yet connected"};
                    if (done) { done(); }
                }

```

is there a missing `node.status(status)` before the `done()` line?

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [23 June 2021 00:34 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/7 "2021-06-23T00:34:21Z")

</div>

No evidence of NR restarting, in fact while I was reviewing the various logs, had an active session open to the editor, etc. The issue just happened to trigger... again. Since then nothing again. So it has happened 2 times in the last 36 hours, in neither case did NR restart. The node reconnects quickly whatever the cause.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [23 June 2021 06:00 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/8 "2021-06-23T06:00:02Z")

</div>

> [@Nodi.Rubrum](#):
>
> No evidence of NR restarting

Did you check in the log that it did not restart? You can't necessarily tell from the editor.  
Also, did it happen when you deployed? Again, check in the log.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [1 July 2021 01:53 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/9 "2021-07-01T01:53:24Z")

</div>

Does not seem to happen on deploy, that would have been too easy. And it has disappeared again the last few days. Thought it might a connection keepalive timeout thing, but I am not optimizing or pooling connections, maybe the mysql configuration node does such? Definitely feels like environment specific scenario... sooner or later I will catch what is going on. Pi hosting database is a Pi 4 with 8GB, so watching it, nothing obvious thus far.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [1 July 2021 05:50 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/10 "2021-07-01T05:50:26Z")

</div>

I had a similar (timing??) problem a few months ago and ended-up creating a simple 'keep alive' flow.  
I just do a SELECT query every 15-minutes. There have been some updates (one just over a week ago) to node-red-node-mysql since I wrote the code, so I don't know if my workaround is still needed.

> [@MySQL fails after an hour or so](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/17):
>
> Quick update to my test doing a SELECT query every 15-mins to see if MySQL connection remains active. My Telegram bot has been working fine for over 38 hours doing dB queries when requested by a user. I did try longer intervals (e.g. 30-mins and 45-mins) but settled on 15-mins for no real reason.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [2 July 2021 16:40 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/11 "2021-07-02T16:40:26Z")

</div>

Cool... 15 minutes is solid for a default I would say. With any SQL connection... new connections are expensive. So, you want to keep them alive longer than you would with other communication protocols in general.

Connection pooling is also important, I use one or two SQL configuration node references across many flows, I don't create many unique SQL connections, in effect to reduce the connect/disconnect cycling as much as possible. Any SQL administrator or best practices guide will scream about applications that connect/disconnect too often.

For example, using ESP(8266 or 32) devices that talk directly to a SQL database have a bad habit of doing this connect/disconnect scenario... at the least the code I have seen has. A much better solution would be for ESP devices to use MQTT to a 'consolidator' NR flow, for example, and then that flow updates the SQL database downstream.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [31 August 2021 16:40 UTC](https://discourse.nodered.org/t/database-connection-but-not-a-connection/47495/12 "2021-08-31T16:40:37Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.