Thanks for the input. I tried the same as you mentioned.
Is there any difference, using "EXEC" command in query mode? Executing stored procedure in query mode.
Yes. Under the hood, the driver uses 2 different types of operation.
Query mode is just that - dynamic (unprepared) query.
For stored procs, use "execute procedure" mode for best performance and security.
We are using query mode to execute stored procedure.
And it has been called 4 times in a second.
Did you try execute procedure mode? Did it work?
Why on earth are you polling the database every 250ms? That suggests your whole design is not ideal. For example, if you are wanting close to real-time values, then you should probably be advertising these via something like MQTT instead of polling a database table.
We are having "Asset Status" in database. Whenever user scans the his ID card (RFID), Node RED will get the "Asset Status" from database and proceed to our business logic.
We are not getting this much frequency (4 scans in a second) every time. But sometimes we are getting these much. It mays leads to this "ETIMEOUT" error. Is there any possibility like this?
If so, what to keep
- Connect Timeout 2. Request Timeout 3 Cancel Timeout 4. Max Pool Size in MSSQL database configuration node?
I have asked / suggested at least 2 times now...
Yes. Execute Procedure mode is working. I am afraid ETIMEOUT error will happen again. Anyway will try that way.
Hi @Steve-Mcl
We are facing again the same issue while using "Execute Procedure" mode as below,
[Name : RequestError]
[Code : ETIMEOUT]
[Message : Failed to cancel request in 5000ms]
We kept our configuration as 5000 ms in Cancel Timeout.
Can you add a rate limit just before the SQL node? This will evenly space out calls to database.
Add a delay node set to rate limit. Enter 5 per 1 second. Then when a burst of database activity occurs, it will space them out by 200ms. If there is no burst this will not cause a delay.
Also, there still may be a coding error and I need info to assess..
We applied this delay node. Will wait for the results.
Before we applying delay node, we faced the following issue for the first time.
[Name : ConnectionError] [Code : ESOCKET]
[Message : Failed to connect to localhost:1433 - Could not connect (sequence)]
My flow is kind of following,
[{"id":"c8825fbe.6e5c8","type":"MSSQL","z":"9adb9ed8.a2f7e","mssqlCN":"1a363e26.a71eb2","name":"SP13","outField":"payload","returnType":0,"throwErrors":1,"query":"[WBS].[dbo].[GetStatus]","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"WBID","type":"VarChar(100)","valueType":"msg","value":"WBID"}],"x":1010,"y":140,"wires":[["85d24c33.522aa"]]},{"id":"1b0a6037.a1f0c","type":"delay","z":"9adb9ed8.a2f7e","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":915,"y":140,"wires":[["c8825fbe.6e5c8"]],"l":false},{"id":"61b5a979.125f08","type":"function","z":"9adb9ed8.a2f7e","name":"SP13-WBStatus","func":"msg.WBID = global.get(\"sWBID\");\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":855,"y":140,"wires":[["1b0a6037.a1f0c"]],"l":false},{"id":"38e5835c.04ef3c","type":"function","z":"9adb9ed8.a2f7e","name":"SP13-WBStatus","func":"msg.WBID = global.get(\"sWBID\");\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":855,"y":220,"wires":[["cbc96dd7.c5aba"]],"l":false},{"id":"cbc96dd7.c5aba","type":"delay","z":"9adb9ed8.a2f7e","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":915,"y":220,"wires":[["8454a929.973458"]],"l":false},{"id":"8454a929.973458","type":"MSSQL","z":"9adb9ed8.a2f7e","mssqlCN":"1a363e26.a71eb2","name":"SP13","outField":"payload","returnType":0,"throwErrors":1,"query":"[WBS].[dbo].[GetStatus]","modeOpt":"","modeOptType":"execute","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"WBID","type":"VarChar(100)","valueType":"msg","value":"WBID"}],"x":1010,"y":220,"wires":[["85d24c33.522aa"]]},{"id":"6d34f38e.96cbbc","type":"inject","z":"9adb9ed8.a2f7e","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"Source1","x":730,"y":140,"wires":[["61b5a979.125f08"]]},{"id":"ef0af1db.d2f2f","type":"inject","z":"9adb9ed8.a2f7e","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":false,"onceDelay":0.1,"topic":"Source2","x":730,"y":220,"wires":[["38e5835c.04ef3c"]]},{"id":"85d24c33.522aa","type":"debug","z":"9adb9ed8.a2f7e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1190,"y":180,"wires":[]},{"id":"1a363e26.a71eb2","type":"MSSQL-CN","tdsVersion":"7_4","name":"UWBS","server":"localhost","port":"1433","encyption":false,"database":"","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"1000","parseJSON":false,"enableArithAbort":true}]
I am getting these errors in very random. and every time I am facing with same stored procedure.
ok, let me know.
Can you reduce your pool from 1000 to something sensible like 10 or 20
Regarding your flow, i see you grab a value from global context...
msg.WBID = global.get("sWBID");
you have NO checks to see if this WBID value is valid (using context can land you with concurrency issues - you should REALLY be passing the WBID value in from your RFID flow sequence directly) You should add some code to check that msg.WBID is a sensible value & if not, then call node.error(WBID value '${msg.WBID}' is not valid,msg); the return out of the function to avoid the msg being passed to the SQL node?
Already the pool size is 10 only.
And msg.WBID whatever value it have like NULL or ''(empty string), stored procedure will return results.
When I imported the flow you posted, pool was set to 1000.
What happens if WBID is undefined or too long?
If WBID is having undefined value also giving response within a milliseconds.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
