Invalid data type on Bulk Insert crashes Node-Red

[thomassorensen2650]

If a error occurs on a bulk insert (e.g. if you try to insert a invalid date into a DateTime Column) then the Node's error handling will not be triggered and the Node-Red Runtime will crash.

Here is the error message

16 Aug 08:56:38 - [red] Uncaught Exception:
16 Aug 08:56:38 - TypeError: Invalid date value passed to bulk rows
    at Table._makeBulk (/data/node_modules/mssql/lib/table.js:69:21)
    at Immediate.<anonymous> (/data/node_modules/mssql/lib/tedious/request.js:216:13)
    at processImmediate (internal/timers.js:464:21)
npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! node-red-docker@2.0.3 start: `node $NODE_OPTIONS node_modules/node-red/red.js $FLOWS "--userDir" "/data"`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the node-red-docker@2.0.3 start script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     /data/.npm/_logs/2021-08-16T17_56_38_410Z-debug.log

[Steve-Mcl]

Hi, thanks for reporting this. I have not seen this myself. If you have a demo flow that consistently reproduces this problem I would appreciate it if you would post it. Please include a table create script and some sample data.

[thomassorensen2650]

Hi Steve,

Thanks for looking into this - I have attached a simple flow and SQL Create statement below.

Flow: [{"id":"a4de19184d88146f","type":"MSSQL","z":"a0245855.19cb18","mssqlCN":"ab87b111a6adf030","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"Test","modeOpt":"","modeOptType":"bulk","queryOpt":"","queryOptType":"editor","paramsOpt":"columns","paramsOptType":"msg","rows":"rows","rowsType":"msg","params":[],"x":720,"y":140,"wires":[[]]},{"id":"3815e2971d92336b","type":"inject","z":"a0245855.19cb18","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":360,"y":140,"wires":[["413f8c905d074b77"]]},{"id":"413f8c905d074b77","type":"function","z":"a0245855.19cb18","name":"","func":"msg.columns = [\n { name: \"test\", type: \"DateTime\", options: { nullable: false } }\n]\nmsg.rows = [[\"Yesterday\"]];\nmsg.payload = [[\"Yesterday\"]];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":530,"y":140,"wires":[["a4de19184d88146f"]]},{"id":"ab87b111a6adf030","type":"MSSQL-CN","tdsVersion":"7_4","name":"Space Planning DB","server":"test","port":"1433","encyption":true,"trustServerCertificate":true,"database":"Space Planning","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

SQL Create

CREATE TABLE Test(
    test DateTime
)

[Steve-Mcl]

Hi, I have tried to handle the error but cannot due to upstream issue.

I have posted it here: https://github.com/tediousjs/node-mssql/issues/1296

If the guys over there address this, the problem should disappear.

[Steve-Mcl]

@thomassorensen2650 the underlying library has fixed this in v7.2.1

If you want to try it without waiting for an update then simply uninstall MSSQL-PLUS then reinstall it. It will pick up the updated package.