# MySQL fails after an hour or so

**URL:** <https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225>\
**Category:** General\
**Created:** [26 March 2021 07:55 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225 "2021-03-26T07:55:02Z")\
**Posts on this page:** 19\
**Page:** 1

<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:** [26 March 2021 07:55 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/1 "2021-03-26T07:55:02Z")

</div>

I have a flow that does a dB query to obtain stored data from my weather stations.  
It does a SELECT using "node100" or "node101" to obtain the appropriate data.  
It works for some hours and then hangs up with this error message.  
 ![Screen Shot 03-26-21 at 07.41 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/1/61a58f93e1b0ecf3b18182218359f6b3018aec5a.jpeg)  
 ![Screen Shot 03-26-21 at 07.40 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/6/268d0a90b8bcaeb9b6180360b6cffb250034c3b7.jpeg)  
I have to do a 'Full Deploy' to clear the error and get the flow working again.

I've updated NR to v1.3.0-beta.1 and mysql to 0.1.6

I'm going to write a very simple flow to query the dB every 5-minutes and see if I can determine how long it works for or if it's a function of the number of accesses to the dB.

Any ideas of other things I should try would be appreciated.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [26 March 2021 08:06 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/2 "2021-03-26T08:06:48Z")

</div>

Hi Dave, in your tests, try sending 2 or more queries in quick succession - see if that is tripping up the node (i.e. does it happen due to a 2nd query while processing the first?)

Also, log to file (node-red-contrib-flogger) the whole msg going in to the query - incase something odd is happening to trip it up.

---

<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:** [26 March 2021 08:09 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/3 "2021-03-26T08:09:34Z")

</div>

Hi Steve, thanks for your swift response.

(In the original flow) I'm using a response from a Telegram in-line keyboard to select which weather station to access, so the repetition frequency is very low driven by when a user uses my Telegram bot.

I'm currently writing a very simple flow to do that and will try varying the time interval (at the moment I was going to try every 5-mins).

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [26 March 2021 08:28 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/4 "2021-03-26T08:28:05Z")

</div>

> [@dynamicdave](#):
>
> so the repetition frequency is very low driven by when a user uses my Telegram bot.

Dont assume anything Dave 😉

Definitely worth putting a flogger node to log the full msg before your mySQL node in the active flow where you are having issues - you might spot something.

---

<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:** [26 March 2021 08:49 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/5 "2021-03-26T08:49:04Z")

</div>

I've been running my test-flow with a repetition rate of every second. Nothing has failed so far and the count is over 1700.

I will now try your flogger suggestion to check the query.

Thanks for your help.

---

<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:** [26 March 2021 16:29 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/6 "2021-03-26T16:29:42Z")

</div>

Steve - quick update for you.

I tried your flogger suggestion - it did not reveal anything out-of-the-ordinary, so my next idea was to query the dB at a fixed interval. I chose every minute, and for the last 3-hours everything has worked fine.  
So later this evening I plan to change the time interval to every 15-mins and see what happens.

At the moment it sort of points to a problem in the MySQL node or with my ISP.

---

<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:** [26 March 2021 17:01 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/7 "2021-03-26T17:01:14Z")

</div>

> [@dynamicdave](#):
>
> a problem in the MySQL node or with my ISP.

Is the database remote?

---

<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:** [26 March 2021 17:40 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/8 "2021-03-26T17:40:55Z")

</div>

We have also seen this issue [node-red-node-mysql throw uncaught exception · Issue #763 · node-red/node-red-nodes · GitHub](https://github.com/node-red/node-red-nodes/issues/763) recently.  
Like you I set up a database and was writing to it once per sec and reading select \* every 15 secs - and it ran for about 3 days before getting so slow on the read it was un-useable... - clearing out the table and it worked fine... so yes - anything you can do to help flush out/pin down this problem would be much appreciated.

---

<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:** [26 March 2021 17:45 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/9 "2021-03-26T17:45:06Z")

</div>

Yes - it is with my ISP on their London servers.

---

<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:** [26 March 2021 17:47 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/10 "2021-03-26T17:47:10Z")

</div>

I found [this isssue](https://github.com/mysqljs/mysql/issues/1694) where a reply which might be relevant says  
"the error `Cannot enqueue Query after fatal error.` means you are trying to perform a query on a connection that has encountered a fatal error, and is now dead & unusable. You can identify fatal errors by checking if `err.fatal` is `true` ([GitHub - mysqljs/mysql: A pure node.js JavaScript Client implementing the MySQL protocol.](https://github.com/mysqljs/mysql#error-handling)). A fatal error is something unrecoverable, like the TCP connection got disconnected, a protocol error, or similar.  
If you are not using the pool, you'll need to implement fatal error handing in your own code. What this means is that you need to check every `err` object you get back from this module for `err.fatal` to be `true` , and if it is, you need to create a brand new connection to the MySQL server and connect again and then start using that new connection for future queries."

---

<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:** [26 March 2021 17:47 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/11 "2021-03-26T17:47:48Z")

</div>

Its been working fine all afternoon doing a 'read' at 1-minute intervals.  
An hour ago I changed the interval to every 15-mins and so far (after 1 hour) it is still working fine.

---

<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:** [26 March 2021 17:56 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/12 "2021-03-26T17:56:29Z")

</div>

Hi Colin,  
That seems to make sense for my situation as when the error happened I had to do a 'Full Deploy' in order to get my flow working again.

---

<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:** [27 March 2021 06:30 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/13 "2021-03-27T06:30:02Z")

</div>

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 14 hours doing dB queries when requested by a user.

---

<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:** [27 March 2021 06:54 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/14 "2021-03-27T06:54:17Z")

</div>

What happens if you pull the Ethernet cable long enough for the connection to drop, then plug it in again?

---

<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:** [27 March 2021 07:40 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/15 "2021-03-27T07:40:05Z")

</div>

Left it pulled out for 5-mins, when I plugged it back in everything recovered.

---

<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:** [27 March 2021 09:06 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/16 "2021-03-27T09:06:09Z")

</div>

Ok, I hoped that might trigger it. Presumably you tried just a few seconds too.

---

<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:** [29 March 2021 08:54 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/17 "2021-03-29T08:54:40Z")

</div>

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:** ![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:** [20 April 2021 08:25 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/18 "2021-04-20T08:25:04Z")

</div>

Another quick update on the MySQL problem.  
This 'keep alive' flow for my MySQL connection has been running without any problems for over 18-days.  
So maybe this has cured the issue??

 ![Screen Shot 04-20-21 at 09.19 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/0/602ef09474e08aa0ae17cd942d4c184c7d21e91f.jpeg)

---

<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:** [19 June 2021 08:25 UTC](https://discourse.nodered.org/t/mysql-fails-after-an-hour-or-so/43225/19 "2021-06-19T08:25:24Z")

</div>

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