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