Error with MYSQL 2 - Status - crash NODE-RED

[allacmc]

I'm using NODE MYSQL2 as a connection to my database.

I put it in a subflow.

I added an OUTPUT Status.

But when you have this option, it completely crashes NODE-RED and I need to manually restart.

[{"id":"4639c7fae2510b5a","type":"subflow","name":"PrepBD","info":"","category":"","in":[{"x":80,"y":100,"wires":[{"id":"8cc0b55b7d0743c0"}]}],"out":[{"x":1020,"y":100,"wires":[{"id":"4135b832b386a610","port":0}]}],"env":[],"meta":{},"color":"#DDAA99","status":{"x":1020,"y":180,"wires":[{"id":"4135b832b386a610","port":0}]}},{"id":"8cc0b55b7d0743c0","type":"change","z":"4639c7fae2510b5a","name":"copy flow/global to msg","rules":[{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.ip","pt":"msg","to":"IP_vGlobalBD","tot":"global"},{"t":"set","p":"payload.porta","pt":"msg","to":"Porta_vGlobalBD","tot":"global"},{"t":"set","p":"payload.usuario","pt":"msg","to":"Usuario_vGlobalBD","tot":"global"},{"t":"set","p":"payload.senha","pt":"msg","to":"Senha_vGlobalBD","tot":"global"},{"t":"set","p":"payload.nomeBD","pt":"msg","to":"NomeBD_vGlobalBD","tot":"global"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":100,"wires":[["effa65099dd2a525"]]},{"id":"4135b832b386a610","type":"mysql2","z":"4639c7fae2510b5a","name":"","server":"","bind":"","topic":"","x":760,"y":100,"wires":[[]]},{"id":"effa65099dd2a525","type":"function","z":"4639c7fae2510b5a","name":"prep_MQTT_Conn","func":"\nconst ip = msg.payload.ip\nconst porta = msg.payload.porta\nconst usuario = msg.payload.usuario\nconst senha = msg.payload.senha\nconst nomeBD = msg.payload.nomeBD\n\nmsg.server = {\n    \"host\": ip,\n    \"port\": porta,\n    \"username\": usuario,\n    \"password\": senha,\n    \"db\": nomeBD\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":100,"wires":[["4135b832b386a610"]]},{"id":"eb96b8b79efaea17","type":"change","z":"4639c7fae2510b5a","name":"","rules":[{"t":"set","p":"server","pt":"msg","to":"{'host': $globalContext('IP_vGlobalBD'),\t 'port': $globalContext('Porta_vGlobalBD'),\t 'username': $globalContext('Usuario_vGlobalBD'),\t 'password': $globalContext('Senha_vGlobalBD'),\t 'bd': \"TelemetriaEnervision\"\t }","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":260,"wires":[[]]},{"id":"4a50ea77c5d0ed5b","type":"comment","z":"4639c7fae2510b5a","name":"Quem sabe futuro usar esse Change","info":"Não consegui fazer funcionar com o mysql2, ele diz que o nome do banco de dados não está selecionado.","x":320,"y":220,"wires":[]}]

Jan 26 16:23:49 DietPi node-red[1957]: at Object.connect (/mnt/dietpi_userdata/node-red/node_modules/node-red-contrib-mysql2/nodes/utils.js:5:12)
Jan 26 16:23:49 DietPi node-red[1957]: at MySql2._inputCallback (/mnt/dietpi_userdata/node-red/node_modules/node-red-contrib-mysql2/nodes/mysql2.js:52:50)
Jan 26 16:23:49 DietPi node-red[1957]: at /mnt/dietpi_userdata/node-red/node_modules/@node-red/runtime/lib/nodes/Node.js:210:26
Jan 26 16:23:49 DietPi node-red[1957]: at Object.trigger (/mnt/dietpi_userdata/node-red/node_modules/@node-red/util/lib/hooks.js:166:13)
Jan 26 16:23:49 DietPi node-red[1957]: at Node._emitInput (/mnt/dietpi_userdata/node-red/node_modules/@node-red/runtime/lib/nodes/Node.js:202:11)
Jan 26 16:23:49 DietPi systemd[1]: node-red.service: Main process exited, code=exited, status=1/FAILURE
Jan 26 16:23:49 DietPi systemd[1]: node-red.service: Failed with result 'exit-code'.
Jan 26 16:23:49 DietPi systemd[1]: node-red.service: Consumed 15.428s CPU time.

Unfortunately this NODE should not have this behavior.

[Steve-Mcl]

Why are you using that node and not the one supported by the core team (node-red-node-mysql)

As for this issue, no idea since that node doesnt have link to its repository so I cannot see exactly why it crashes node-red.

What I can say is any node that does not correctly handle its errors (especially in promises/async code) will crash node-red and we encourage users to raise an issue on the repository asking the developer to properly handle errors.

[allacmc]

I'm using this node because I can insert the database connection data via global variables.

I have an application where this is important: An external professional who is not a programmer. Fill in data such as IP, User, password, port and database.

The current official MYSQL by the NODE-Red team does not allow these modifications dynamically.

Do you have any suggestions for using a database connector that does this dynamically and has stability?

[Steve-Mcl]

If it needs to be set once, for 1 database, without modifying flows, then simply use ENV VARS. Then instruct the external person the ENV VARS to set before launching node-red

Using ENV VARs for text fields.

DOCS: Using environment variables : Node-RED

If the connection needs to be changed at runtime, then you will need to fork node-red-node-mysql (or another repo) and add runtime setting feature yourself.

[allacmc]

Populating the database property once will partially solve my problem.

I don't have the knowledge to modify the current node to allow receiving these settings dynamically, so I don't even try to fork.

[bakman2]

You could try it like this instead (suggested this a couple of days ago):

It requires to have external modules to be enabled in settings.
Then you can make it dynamic.

[allacmc]

I think the concept of the visual language is to use as little code as possible.

In that case your suggestion is very good.

But it also involves typing a lot of code.

Ideally, there should be a mysql NODE that allows dynamically changing connections using the change node.

Or maybe you can do that and I haven't thought of an elegant solution.

[bakman2]

But it also involves typing a lot of code.

You type (copy) it once, you can feed the data via msg.payload.host, msg.payload.user etc....
See documentation from which you can copy the code.

replace the host: "192.168.1.4 with host:msg.payload.host etc and you can reuse it (ie: dynamic).

I don't see the issue, or do you want everything to be served on a plate?

The reason (i assume) that it is not dynamic is because of database security and encryption of the password, which will be completely omitted with this and your "solution".

[allacmc]

You are completely right.

That way I can with only one node... Assigned in subflow to act as a function.

Thank you very much.

I think you found an elegant solution.