API call goes into 500 randomly

mvoltolini · 13 December 2023 16:21

Good evening, everyone,

I am struggling with a problem related to a Node-Red stream (I inherited it from another developer) installed locally on a Windows 11 pro PC, 16GB RAM, i7 and gigabit ethernet card.
The stream receives via get a numeric parameter and via the mssql-plus component executes a query on a local SQL Server Express 2019 DB.

In completely random mode this stream goes to 500 for no apparent reason. The same stream is in use correctly on another 30 or so PCs.

Before trying to get help I tried everything I could think of: reinstalling node-red, redoing the flow from scratch, comparing configurations between PCs, checking connectivity, checking Ethernet cabling but to no avail.

Could you give me some input related to other aspects to go check?

thank you all very much for your time and support

marcus-j-davies · 13 December 2023 16:30

Hi @mvoltolini

What do you mean by "a Node-Red stream"
a stream is usually a constant delivery of packets via a network or some other medium, that can be processed by Node RED.

And what is SQL's place in this "stream", SQL doesnt stream, its usually a Req/Res type of protocol.

The fact you mentioned 500, suggests this is a HTTP API? so are you polling a HTTP API perhaps.

I think some clearer understanding may be needed.

I inherited it from another developer

I assume you mean you inherited the setup, and the steam is not from the developer, which is more confusing

Unless I'm missing something? (entirely possible of course)

Steve-Mcl · 13 December 2023 17:30

I assume by "stream" you mean "flow" (thats what we call a collection of connected "nodes")

If I replace every "stream" word with "flow", it makes a little more sense)

In completely random mode this stream goes to 500 for no apparent reason"

Reading between the lines, you are saying "Sometimes, something returns an HTTP Error 500"

Since error 500 is a server response it might be you are doing a POST with "bad data" from the database?

It could be your "flow" is actually a "HTTP-in -> HTTP-response" (Endpoint) and your flow is failing and respongding with a 500 error (by design)?

Too many unknowns and really difficult to tell - however, to help you help yourself, you should add debug nodes throughout your "flow"s (streams) to see what is happening.

In short, try to clarify what the "somethings" are and try to include more details so that we can help you.

mvoltolini · 14 December 2023 08:30

Thank you all for your time. I will try to explain better what is going on using the correct terms.

The node-red flow is triggered with an API GET call. When the call is successful it executes a query on the SQL Server Express 2019 DB installed locally on the PC that returns values to the software that made the call. Quite randomly, however, the flow returns an error 500.
As suggested I have tried adding a debug so that I have a text file however I can't find anything abnormal inside it. It almost seems like node-red goes to sleep for a few seconds. Sometimes it happens 5/6 consecutive times other times it happens two hours apart.
Error 500 is generated by a flow that starts with catch:all and to which an HTTP(500) node is attached and which from what I understand is started in case of an anomaly.

The system logs don't report me any error related to node-red service or sql server express 2019 services and honestly I can't think of what I could check to solve the problem.

marcus-j-davies · 14 December 2023 08:40

Hi @mvoltolini

Thanks for the explanation.

To me - This is SQL, usually a query timeout, which will be caught by the catch node, retuning 500.
I think it will be worth if we can see a copy of your flow export?

If there are credentials in places, or you want to censor IP address's in the export, please do so.

paste the export between 3 backticks top and bottom (```)

```
Flow Export JSON
```

Steve-Mcl · 14 December 2023 09:04

Send your catch messages to a log. You can do this by setting a debug node to show the complete message and check the tick box to send to console. Or you can add logging node (node-red-contrib-flogger).

mvoltolini · 14 December 2023 09:09

i have exported this


[
    {
        "id": "56e4542743885219",
        "type": "catch",
        "z": "22a49c25aa680a2f",
        "name": "",
        "scope": null,
        "uncaught": false,
        "x": 240,
        "y": 420,
        "wires": [
            [
                "096d34efa47f9658"
            ]
        ]
    },
    {
        "id": "096d34efa47f9658",
        "type": "http response",
        "z": "22a49c25aa680a2f",
        "name": "Error500",
        "statusCode": "500",
        "headers": {},
        "x": 580,
        "y": 420,
        "wires": []
    },
    {
        "id": "02f099dd49c21171",
        "type": "http in",
        "z": "22a49c25aa680a2f",
        "name": "",
        "url": "/api/RunQuery",
        "method": "get",
        "upload": false,
        "swaggerDoc": "",
        "x": 270,
        "y": 240,
        "wires": [
            [
                "f080e9e71a0a2bd9"
            ]
        ]
    },
    {
        "id": "f080e9e71a0a2bd9",
        "type": "function",
        "z": "22a49c25aa680a2f",
        "name": "checkIdRow",
        "func": "if (msg.payload.idRow != undefined) {\n    return msg;\n}\nelse {\n    msg.payload = \"Cannot process file; missing parameter idRow.\";\n    throw Error(\"Cannot process file; missing parameter idRow.\");\n}",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 570,
        "y": 240,
        "wires": [
            [
                "8fbf35bd49e8cfbc"
            ]
        ]
    },
    {
        "id": "8fbf35bd49e8cfbc",
        "type": "MSSQL",
        "z": "22a49c25aa680a2f",
        "mssqlCN": "d49dd1a5093ffad1",
        "name": "",
        "outField": "payload",
        "returnType": 0,
        "throwErrors": 1,
        "query": "SELECT * FROM tblABC WHERE id = @idRow",
        "modeOpt": "queryMode",
        "modeOptType": "query",
        "queryOpt": "payload",
        "queryOptType": "editor",
        "paramsOpt": "queryParams",
        "paramsOptType": "none",
        "rows": "rows",
        "rowsType": "msg",
        "parseMustache": true,
        "params": [],
        "x": 840,
        "y": 240,
        "wires": [
            [
                "62af80a41557a748"
            ]
        ]
    },
    {
        "id": "62af80a41557a748",
        "type": "function",
        "z": "22a49c25aa680a2f",
        "name": "extract",
        "func": "for (var item of msg.payload) {\n    item.idRow =item.idRow[0];\n    item.BNC =item.BNC[0];\n    item.move =item.move[0];\n    item.task =item.task[0];\n}\n\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1110,
        "y": 240,
        "wires": [
            [
                "2fd7ddf177651700",
                "22f612c98c60113c"
            ]
        ]
    },
    {
        "id": "2fd7ddf177651700",
        "type": "http response",
        "z": "22a49c25aa680a2f",
        "name": "Response",
        "statusCode": "200",
        "headers": {},
        "x": 1320,
        "y": 240,
        "wires": []
    },
    {
        "id": "22f612c98c60113c",
        "type": "debug",
        "z": "22a49c25aa680a2f",
        "name": "DebugCallAPI",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1340,
        "y": 340,
        "wires": []
    },
    {
        "id": "d49dd1a5093ffad1",
        "type": "MSSQL-CN",
        "tdsVersion": "7_4",
        "name": "SSELocal",
        "server": "localhost",
        "port": "1433",
        "encyption": true,
        "trustServerCertificate": true,
        "database": "",
        "useUTC": true,
        "connectTimeout": "15000",
        "requestTimeout": "15000",
        "cancelTimeout": "5000",
        "pool": "5",
        "parseJSON": false,
        "enableArithAbort": true,
        "readOnlyIntent": false
    }
]

mvoltolini · 19 December 2023 08:15

Anyone have an idea?

marcus-j-davies · 19 December 2023 08:25

Hi @mvoltolini

I think you need to ~~catch~~ store the error that is being handled.

Something like below, should allow you to review the error in all its glory

Bring in a change node.
Attach it to the catch Node
Store a flow variable to save the error, so you have a copy of it once the error occurs.

You can then track where the error is occurring, it will be stored in a flow variable called lastError
once you suffer the 500 - you will have a copy as to why

Cheap and cheerful way (many ways of course), but this is s quick win, to identify the problem,

Example:

system · 17 February 2024 08:25

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

Topic		Replies	Views
Node jsonSQL Issues General	8	485	15 September 2020
Flows.nodered.org down? General	8	324	30 August 2020
Stop Node-Red randomly General	5	377	28 November 2021
Uncaught exception from mysql nodes General	19	628	29 April 2021
Strange log line General	4	271	7 September 2022

API call goes into 500 randomly

Related Topics