ECONNCLOSED: Connection is closed - Concurrent SQL connections result in first succeeding and all other failing

[tenaka30]

Recently forced to move from age old node-red install version (think 0.something) and node-red-contrib-mssql.

Flows imported fine but with node-red-contrib-mssql no longer maintained (deleted?) we have moved to node-red-contrib-mssql-plus. Now we see where concurrent table reads/writes work in the older Node they fail in mssql-plus.

A few of our flows for example read from multiple tables in a single DB, then join the results for an external report or email.

[Steve-Mcl]

Can you provide a demo flow and / or screenshots explaining what the issues are?

The old MSSQL node and this may not be 100% compatible (due to bug fixes, underlying library updates and new features) so it may be you simply need to tweak a setting.

What I can say is multiple queries are definitely supported so perhaps there is a bug (or misconfiguration).

Please provide additional feedback.

[tenaka30]

Can you provide a demo flow and / or screenshots explaining what the issues are?

The old MSSQL node and this may not be 100% compatible (due to bug fixes, underlying library updates and new features) so it may be you simply need to tweak a setting.

What I can say is multiple queries are definitely supported so perhaps there is a bug (or misconfiguration).

Please provide additional feedback.

[{"id":"784170023a11454b","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"4d48aaf9728a5811","type":"inject","z":"784170023a11454b","name":"@05:00","repeat":"","crontab":"00 07 * * *","once":false,"onceDelay":0.1,"topic":"Daily Run Checks","payload":"","payloadType":"date","x":180,"y":120,"wires":[["eb4a2357f94d224b","a786faa77c32f097","4bb633028149da39","abf8269cfa853f91","26101851ad62f23c"]]},{"id":"c0356583bcc4617f","type":"switch","z":"784170023a11454b","name":"","property":"success","propertyType":"msg","rules":[{"t":"true"},{"t":"false"}],"checkall":"true","repair":false,"outputs":2,"x":1050,"y":260,"wires":[["03ab201fe6853682","dcde8ee79316fe1a"],["39291161e4fb751e","dcde8ee79316fe1a"]]},{"id":"03ab201fe6853682","type":"debug","z":"784170023a11454b","name":"Successful","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1310,"y":220,"wires":[]},{"id":"39291161e4fb751e","type":"debug","z":"784170023a11454b","name":"Failure","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1320,"y":260,"wires":[]},{"id":"dcde8ee79316fe1a","type":"link out","z":"784170023a11454b","name":"Send Email","links":["fe99ab25.88c32"],"x":1195,"y":320,"wires":[]},{"id":"eb4a2357f94d224b","type":"MSSQL","z":"784170023a11454b","mssqlCN":"3bf7ec3604f3d684","name":"ERS AAGR Referral Table - Get Result","outField":"results","returnType":"0","throwErrors":"0","query":"SELECT 'AAGR' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_AAGR]\nWHERE [BP_CreationDate] > DateADD(hh, -14, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":630,"y":120,"wires":[["f3769af70beca21a"]]},{"id":"a786faa77c32f097","type":"MSSQL","z":"784170023a11454b","mssqlCN":"3bf7ec3604f3d684","name":"ERS AFB Referral Table - Get Results","outField":"results","returnType":"0","throwErrors":"0","query":"SELECT 'AFB' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_AFB]\nWHERE [BP_CreationDate] > DateADD(hh, -14, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":630,"y":160,"wires":[["f3769af70beca21a"]]},{"id":"f3769af70beca21a","type":"join","z":"784170023a11454b","name":"Join Results","mode":"custom","build":"array","property":"results","propertyType":"msg","key":"counts","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"30","count":"5","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1030,"y":140,"wires":[["37fc09ca2237a7dd"]]},{"id":"4bb633028149da39","type":"MSSQL","z":"784170023a11454b","mssqlCN":"3bf7ec3604f3d684","name":"ERS ASI Referral Table - Get Results","outField":"results","returnType":"0","throwErrors":"0","query":"SELECT 'ASI' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_ASI]\nWHERE [BP_CreationDate] > DateADD(hh, -14, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":630,"y":200,"wires":[["f3769af70beca21a"]]},{"id":"abf8269cfa853f91","type":"MSSQL","z":"784170023a11454b","mssqlCN":"3bf7ec3604f3d684","name":"ERS RFR Referral Table - Get Results","outField":"results","returnType":"0","throwErrors":"0","query":"SELECT 'RFR' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_RFR]\nWHERE [BP_CreationDate] > DateADD(hh, -14, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":630,"y":240,"wires":[["f3769af70beca21a"]]},{"id":"26101851ad62f23c","type":"MSSQL","z":"784170023a11454b","mssqlCN":"3bf7ec3604f3d684","name":"ERS DA Referral Table - Get Results","outField":"results","returnType":"0","throwErrors":"0","query":"SELECT 'DA' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_DA]\nWHERE [BP_CreationDate] > DateADD(hh, -14, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n  ","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":630,"y":280,"wires":[["f3769af70beca21a"]]},{"id":"37fc09ca2237a7dd","type":"function","z":"784170023a11454b","name":"Build Report","func":"if (msg.results.length == 5)\n{\n    var success = true;\n    var emailBody = \"\";\n    \n    var source = \"\";\n    var status = 0;\n    var error = \"\";\n    \n    msg.results.forEach(function(data) {\n        \n        source = data[0][\"source\"];\n        status = data[0][\"status\"];\n        error = data[0][\"error\"];\n        \n        emailBody += source;\n        \n        switch(status)\n        {\n                case 0:\n                    emailBody += \" has not yet run.\";\n                    success = false;\n                    break;\n                case 1:\n                    emailBody += \" is still running or has become stuck.\";\n                    success = false;\n                    break;\n                case 9:\n                    emailBody += \" has failed with error: \" + error + \".\";\n                    success = false;\n                    break;\n                case 10:\n                    emailBody += \" has completed successfully.\";\n                    break;\n        }\n        \n        emailBody += \"\\n\";\n        \n    });\n    \n    msg.topic = \"BluePrism results: ERS Referral Table Upload\";\n    msg.payload = emailBody;\n    msg.success = success;\n    \n    \n} else {\n    msg.topic = \"NodeRed Error: ERS Referral Table HealthCheck\";\n    msg.payload = \"An error occured when trying to execute this health check.\\nPlease check manually.\\n\" + msg.results;\n    msg.success = false;\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":1030,"y":200,"wires":[["c0356583bcc4617f"]]},{"id":"3bf7ec3604f3d684","type":"MSSQL-CN","tdsVersion":"7_4","name":"SERVERNAME","server":"SERVERNAME","port":"1433","encyption":true,"trustServerCertificate":true,"database":"BluePrism-Control","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

This is the simplest example with the server names removed, no other alterations. It seems to succeed top to bottom, if I disconnect the top SQL query the next one functions and so on.

Edit: Sorry, I assumed selecting code block would avoid wall of text. - EDIT2: Fixed code block

[tenaka30]

Can you provide a demo flow and / or screenshots explaining what the issues are?

The old MSSQL node and this may not be 100% compatible (due to bug fixes, underlying library updates and new features) so it may be you simply need to tweak a setting.

What I can say is multiple queries are definitely supported so perhaps there is a bug (or misconfiguration).

Please provide additional feedback.

Not sure what screenshots would be most helpful but this is the flow sample above whne ran.

[tenaka30]

I was mistaken, the older server is still available and I can now see we were not using the older node-red-contrib-mssql but an old version of node-red-contrib-mssql-plus v0.4.0 and node-red was 0.20.5

We have been using these versions for about 4 years I think.

[Steve-Mcl]

@tenaka30

Sorry, I assumed selecting code block would avoid wall of text.

---

So, you are trying to run concurrent queries - while this could/should work (I will look into this later), it is not the ideal composition.

There are 2 approaches i would take on this

1. Put all queries into 1 request
2. chain the requests (sending results to msg.result1, msg.result2, etc)

Here is an example of doing multiple queries in one

Settings

Results

flow...

[{"id":"dc455102b2fd2f32","type":"inject","z":"078626267cda591e","name":"@05:00","repeat":"","crontab":"00 07 * * *","once":false,"onceDelay":0.1,"topic":"Daily Run Checks","payload":"","payloadType":"date","x":360,"y":120,"wires":[["c7723db44b1ed55a"]]},{"id":"1b389503599c6764","type":"switch","z":"078626267cda591e","name":"","property":"success","propertyType":"msg","rules":[{"t":"true"},{"t":"false"}],"checkall":"true","repair":false,"outputs":2,"x":1050,"y":120,"wires":[["e5bc5c51a3956973"],["34783c195a9f1a59"]]},{"id":"e5bc5c51a3956973","type":"debug","z":"078626267cda591e","name":"Successful","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1250,"y":100,"wires":[]},{"id":"34783c195a9f1a59","type":"debug","z":"078626267cda591e","name":"Failure","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1230,"y":160,"wires":[]},{"id":"c7723db44b1ed55a","type":"MSSQL","z":"078626267cda591e","mssqlCN":"3bf7ec3604f3d684","name":"Get ERS AAGR, AFB, ASI, RFR, DA","outField":"results","returnType":"1","throwErrors":"0","query":"SELECT 'AAGR' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_AAGR]\nWHERE [BP_CreationDate] > DateADD(hh, @hours, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n\nSELECT 'AFB' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_AFB]\nWHERE [BP_CreationDate] > DateADD(hh, @hours, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n\nSELECT 'ASI' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_ASI]\nWHERE [BP_CreationDate] > DateADD(hh, @hours, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n\nSELECT 'RFR' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_RFR]\nWHERE [BP_CreationDate] > DateADD(hh, @hours, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n\nSELECT 'DA' AS source, [BP_Status] AS status, [BP_ErrorDescription] AS error\nFROM [dbo].[Queue_BPContinuous_ERS_DA]\nWHERE [BP_CreationDate] > DateADD(hh, @hours, Current_TimeStamp)\nORDER BY [BP_CreationDate] DESC\n ","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"hours","type":"int","valueType":"num","value":"-14"}],"x":630,"y":120,"wires":[["03040111b74a646b"]]},{"id":"03040111b74a646b","type":"function","z":"078626267cda591e","name":"Build Report","func":"if (msg.error) {\n    msg.topic = \"NodeRed Error: ERS Referral Table HealthCheck\";\n    msg.payload = \"A database error occured\\n\" + msg.error;\n    msg.success = false;\n    return msg;\n}\n\nif (!msg.results || !msg.results.recordsets || msg.results.recordsets.length !== 5) {\n    msg.topic = \"NodeRed Error: ERS Referral Table HealthCheck\";\n    msg.payload = \"The database did not return 5 record sets\";\n    msg.success = false;\n    return msg;\n}\n\nvar success = true;\nvar emailBody = \"\";\nvar source = \"\";\nvar status = 0;\nvar error = \"\";\n\ntry {\n    msg.results.recordsets.forEach(function (data) {\n\n        source = data[0][\"source\"];\n        status = data[0][\"status\"];\n        error = data[0][\"error\"];\n\n        emailBody += source;\n\n        switch (status) {\n            case 0:\n                emailBody += \" has not yet run.\";\n                success = false;\n                break;\n            case 1:\n                emailBody += \" is still running or has become stuck.\";\n                success = false;\n                break;\n            case 9:\n                emailBody += \" has failed with error: \" + error + \".\";\n                success = false;\n                break;\n            case 10:\n                emailBody += \" has completed successfully.\";\n                break;\n        }\n\n        emailBody += \"\\n\";\n\n    });\n\n    msg.topic = \"BluePrism results: ERS Referral Table Upload\";\n    msg.payload = emailBody;\n    msg.success = success;\n} catch (error) {\n    msg.topic = \"NodeRed Error: ERS Referral Table HealthCheck\";\n    msg.payload = \"An error occured when trying to execute this health check.\\nPlease check manually.\\n\" + error.message;\n    msg.success = false;\n};\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":120,"wires":[["1b389503599c6764"]]},{"id":"3bf7ec3604f3d684","type":"MSSQL-CN","tdsVersion":"7_4","name":"SQLSERVERNAME","server":"SQLSERVERNAME","port":"1433","encyption":true,"trustServerCertificate":true,"database":"BluePrism-Control","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

[tenaka30]

Hmm, that nice-ified it but still massive scroll space. EDIT: Scrap that, i realised it was because it had inherited formatting at some point. Thanks for that.

So, you are trying to run concurrent queries - while this could/should work (I will look into this later), it is not the ideal composition.

There are 2 approaches i would take on this

1. Put all queries into 1 request

2. chain the requests (sending results to `msg.result1`,  `msg.result2`, etc)

As I mentioned the older version of this node seem to have no problems with it but it was an incredibly old version so we have definitely lost something along the way. I had another thought after a bit that with the rebuild we have also brought the node-js install up to date as well so wondered if it was a possible change within the platform that has trickled down.

I've already started on putting the requests in sequence as needs-must and we rely on the reporting.

The original reasoning against putting them all in one request was that we wanted to enable or disable certain tables easily but I do prefer it for it's tidyness myself.

Thanks.

[flight2k]

Hi, I have the same issue with the lastest version. But i can't but all my query in the same request.

[Imi3est]

I had the same issue with node-red-contrib-mssql. I still use it but was thinking to replace it to this new version because of this bug, but I see that it is still exists in this version. I have a subflow to save different types of measured data to database. I use it multiple times in my flows so sometimes it is called from diffrent places in almost same time. If that occures I get an error and data is not stored. Catch node can't catch the error so I can't repeat the db call. It is easy to reproduce: Just put an inject node and link output into two paralell sql nodes. (In my case it is a stored proc call) I think it is a bug and it should be fixed because it is problematic to use the node multiple times in flows. Finally I found a workaround: Instead of subflow I use the node-red-contrib-component nodes and I limiting messages with delay node (with message rate). With subflow I can't limit messages globally because subflow nodes works as they would be copies for every instance. This is why I moved to component nodes. This is how it looks like: