MySQL fails after an hour or so

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
Screen Shot 03-26-21 at 07.40 AM
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.

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.

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).

Dont assume anything Dave :wink:

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.

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.

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.

Is the database remote?

We have also seen this issue node-red-node-mysql throw uncaught exception · Issue #763 · node-red/node-red-nodes · GitHub 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.

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

I found this isssue 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.). 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."

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.

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.

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.

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

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

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

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.

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??

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