Database connection is lost after a brief period of time

[nielsdevos]

I have a running node red application on bluemix using dashDB as a database and I notice that after a brief period of time (from 1 week to 2 months), the queries do not return the correct information. The same data is send to the node-red flow but there is no response from the dashdb nodes. They always return an empty array.
Only by restarting all the flow of the application, the connections are restored again and everything works as it should.

[mmnemati]

I have this exact same problem. After a few weeks, when I submit a query in Node-red it returns "[no object]". The way I got it working was to move any node in the flow slightly around and re-deploy the flow. After the deployment, my queries return expected results. It's quite strange. I opened up a trouble ticket with IBM Bluemix Help. They pointed me to this git for support as well.

[tverilytt]

Hi!
We are experiencing this too, using DashDB in Bluemix. There has been two DashDB connection losses during summer period. We suspect it might be due to updates centrally on the Bluemix DashDB service, and that this breaks / invalidates Node-RED connections to the DashDB database. Redeploy seems to resolve this, and we have created a separate Node-RED flow, which uses the Node-RED Admin API to trigger a redeploy. We are now investigating how to best make use of ut (manual trigger, at certain time intervals). One possible workaround might be to let the flow poll DashDB at desired time intervals (e.g. do a simple READ operation of a knnown table record), and do a Node-RED redeploy if no data is returned...

Also, my pull request for the DashDB output node might be helpful to detect problems / errors when trying to store / read data...
#15

[NilsGregersen]

Hi,

we are facing the same issue in a productive environment. This needs to be fixed. The dashDB nodes should establish a new connection when the flow is running and not only once on deploy.

@tverilytt How did you perform the redeploy? I am trying to do this but it is still not working.

We are running Node-RED on IBM Bluemix. I have this code in a function node before a HTTP node:

#######################################

msg.method="POST";

msg.url = "yourURL/auth/token";

msg.payload = {'client_id': 'node-red-admin',

          'grant_type': 'password',

          'scope': '*',

          'username': 'yourusername',

          'password': 'yourpassword'}

msg.headers = {

'Accept': '*/*',

"Content-type" : "application/json",
"Node-RED-Deployment-Type":"full"

}
return msg;

###########################################

I get this return:

Error
Cannot POST

Can anyone help me out?

Thanks!

[tverilytt]

@NilsGregersen Sorry for late response - I only tested my PR modification locally. We are running in Bluemix as well, so want to try solve this by e.g. trying the PR version.
One can use local Node-RED nodes, by putting them into the "nodes" directory in the project.

Not sure why you are getting POST error, you have some more details, or have you resolved it?!

Cheers
-jo

[NilsGregersen]

@tverilytt Hi thank you for your reply. Well in the meantime I had an email conversation with one of the developers of the dashDB node. There is quite a chance, that he will update the node, to fix this issue. I will keep you informed here.

Cheers
Nils

[tverilytt]

@NilsGregersen That sounds very interesing indeed. It would be much better if they update these DashDB nodes, instead of parting off with own pull request branch.

Cheers
-jo

[tverilytt]

@NilsGregersen Can you send me a direct message to jo.torsmyr@gmail.com ?!

Cheers
-jo

[NilsGregersen]

@tverilytt sure I can do that.

Cheers!

[smchamberlin]

Hi guys, I looked into this issue today, and discovered the following behavior in the dependent ibm_db module:

If the connection is dropped for any reason (I'm able to simulate this with "db2 force application all" on my DB2 server), then the next subsequent attempt to query from that connection will give an appropriate SQL30081N error. However, any attempts after that will erroneously return an empty result set. I feel this is misleading, as it is impossible to distinguish at this point whether the connection is actually working, with an empty result set from the query, or if the connection is still broken and requires a reconnect.

I have field a defect against the ibm_db repository:
ibmdb/node-ibm_db#327

I could add logic to retry on the first 30081 but I don't think this will address the problem, at least not fully. That would only work if the connection is back up right away, like a network hiccup. More likely the connection could be down for a while - like an outage or maintenance window or network issue - and so if we cannot recover right away, empty results will be returned. Those empty results are indistinguishable from actual empty results. We don't want to just assume empty results are always dropped connections, because that has its own repercussions too, as an empty result could be valid if there is no data returned from the query.

Open to your suggestions. I could add an optional flag to always connect and disconnect with every incoming msg, but that's not ideal either due to performance. But it would have one benefit - if you had 10 nodes, then it wouldn't open 10 connections at deploy. It would only use and then close the connection for each node when it needs it.

[smchamberlin]

Hi guys,

Actually I have a solution and I have tested it and seems to work well. Basically when the connection was dropped for any reason it didn't try to reconnect. Now I have added logic to go into "jail mode" when the first communication error is received, which causes it to try to reconnect on any incoming msg until a new connection is successfully established. The fix is in this commit:

a75904c

Please note that this fix is only for the query node. I just tried the same scenario with the output node and unfortunately it is similar. If the connection is dropped on the server, first a 30081 error will come in but any more insert attempts will be erroneously reported as "successful". I can put in a similar fix for the output node and will close out this issue when I finish that.

[kasarol]

I have this same problem. When this happens, I get "dashDB: Insert successful!" log entries even when nothing is actually stored, while reading seemingly works. This only gets fixed when I restart the entire application, at which time I may have lost hours or even days worth of data! Any fix for this? This is complete showstopper for my project.

[j-zhou2]

Is this issue going to be fixed in a short term? Base on the discussion thread, it has been opened for a very long time.

[smchamberlin]

I believe this is fixed now - please try with the latest code.

The nodes are now lazy connects which connect if required on an incoming msg event. I validated scenarios where if the DB goes down for some reason, on the first unsuccessful attempt to insert or query data, it will flag the incident so that next time a msg object comes in, it will attempt the re-connect again first. As long as the DB stays down, a reconnect attempt will keep happening for every incoming request until connection is again established. This fix applies now to both nodes.

Fix is in this commit: f89b399