Update broke MySQL, old versions of Node-red-nodes have been removed. Why and where are they?

General
1

I run a global aircraft tracking website that gets around 90,000 hits a day. Its all built on Node-RED.

Some months back I did an update around the 1.3.0 point and it broke all my MySQL insert statements.
At that time I did a npm install node-red-nodes@0.6.0 and got back up and running in a few minutes.
Today I figured all that would be behind me and so did an update from around the 1.3.0 to 2.0.6.
Once again, all MySQL commands are broken.
This time the wind back did not work:

npm install node-red-nodes@0.6.0
npm ERR! code ETARGET
npm ERR! notarget No matching version found for node-red-nodes@0.6.0.
npm ERR! notarget In most cases you or one of your dependencies are requesting
npm ERR! notarget a package version that doesn't exist.

npm ERR! A complete log of this run can be found in:
npm ERR!     C:\Users\tbg\AppData\Local\npm-cache\_logs\2021-09-06T19_36_49_563Z-debug.log

Why were these old versions removed?
Where did they go?
How can I walk it back?

The site is currently down.
Thanks for your help.

2

I think you'd be better off trying to understand what is wrong with the SQL inserts tbh.

As for breaking you running site, it would be definitely worth trying to get a Dev environment up and running to test before you do any changes.

Also, a full backup of node-red folder would permit you to very quickly back out any changes.

So, what errors are you getting with mySQL inserts?

3

Good point on the dev site... Been doing the same thing for 7 years and never had an issue so it simply never occurred to me.

The MySQL error from the debug tab is thus:
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '20:11:33Z 06-09-2021', `flight` = '.', `tail` = '.N59DB', `callsign` = ':',...' at line 1

4

Can you capture the SQL statement that causes that error and post it in a reply?

Also, do you have a mySQL client application where you can test the query directly against the database?

5

Its hard to run Node-RED at the moment, I get around 500,000 messages a day and they all go into the MySQL, so when I run it, the debug tab is just a blur or red errors and the whole thing is a bit slugish.

I use HeidiSQL and it works great. No errors at all.

6

You should be able to get the error from the catch node attached to a debug with show complete message set.

Or you could switch off all debugs and use a switch node to filter to something specific then to a debug node (to minimise debug messages)

7

No debugs are on. Its the MySQL node itself that is throwing the error in the debug tab.
Pretty sure there is no check box to tell it to mute its errors?
I cant catch any of my debugs in the high speed stream of error messages, so am running blind.

8

Can't you pick up details from the console or log?

I suspect the time stamp you insert into the database is no good. What is the field type for that date time string I see in your error message?

9

The console shows the exact same error as the debug tab.

The types are as follows:
(They have been the same for 5+ years).

image

10

Have you added a catch node? Does it catch the error? If so, you could send that to a file node or log it in global context. You should see the msg (and more importantly, the topic containing the generated SQL statement). Once you have that you can test it in a client application.

11

Managed to get a catch node in...

Here is the topic. (Just now testing it again in Heidi).

INSERT INTO clreport ( utctime, flight, tail, callsign, description, icao , aes, link, filter, source, sat, airforce, gndstn) VALUES('22:09:34Z 06-09-2021',':','A39003','undefined','.','.','7CF866','https://globe.adsbexchange.com/?icao=7CF866','Mil','C-Band','C-98W','Australia','OAKODYA') ON DUPLICATE KEY UPDATE utctime = ('22:09:34Z 06-09-2021'), gndstn = ('OAKODYA'), callsign = ('undefined')

Update. Heidi was happy with that exact insert. No errors thrown, table was updated.

12

Was that SQL definitely captured from an error?

Also, are all SQL inserts/updates/selects in node red failing? (Are any inserts/updates working at all?)

13

Its hard to stop the debug tab scroll when the catch node fires.

Looking at Heidi, some inserts are working.

14

If some work, it suggests rouge data.

For example, I see in your earlier post there are undefined values.

You might want to try prepared statements instead of building SQL strings.

15

The catch node is only catching good inserts. Never the error.
The only error I get is the MyQSL node error I posted before.

16

That is odd. The catch node should only catch when node.error(?,msg) is explicitly called by the node - perhaps a bug?

I'd still recommend changing to prepared statements. They are safer against SQL injection and can handle single quotes in the data etc.

Ps, which mySQL node are you using?

17

If you are using node-red-node-mysql, you can install any version you wish

There are 32 versions available...

Just enter npm install node-red-node-mysql@x.y.z where x.y.z is one of the listed versions

18

I am using this node:

There is some more information in the 'stack' payload I had not noticed in the catch node:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '22:23:25Z 06-09-2021', `flight` = ':', `tail` = '.P4BFL', `callsign` = 'P4BFL...' at line 1
    at Query.Sequence._packetToError (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (C:\Users\tbg\.node-red\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (C:\Users\tbg\.node-red\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<...
19

Unfortunately if you can't capture the SQL statement is very difficult to tell. However that does look like an UPDATE not an INSERT. Do you do any UPDATEs?

20

Ps, there is info in the readme of that link you posted that shows you how to do Prepared statements.

Gtg unfortunately. Good luck.

1 Like