[Feedback wanted] Update to mysql node

I have just updated the node-red-node-mysql node to use the more maintained mysql2 library in response to MySQL 8.0 needs new Auth mechanism · Issue #853 · node-red/node-red-nodes · GitHub - and it is on npm as node-red-node-mysql@next so can be manually installed...

eg cd ~/.node-red && npm i node-red-node-mysql@next

If anyone has any time to give it a whirl I would really appreciate it. It claims to be nearly compatible with the basic mysql library but there are some known differences, see- node-mysql2/documentation at master · sidorares/node-mysql2 · GitHub - so we would like feedback if they actually hit anyone, and how we can mitigate, before pushing this out more widely.

1 Like

Just tried installing (well upgrading) mysql as per your instructions and get this error message.

18 Nov 11:38:58 - [info] Dashboard version 3.1.1 started at /ui
18 Nov 11:38:59 - [warn] ------------------------------------------------------
18 Nov 11:38:59 - [warn] [node-red-node-mysql/mysql] Error: Cannot find module 'mysql'
Require stack:

  • /home/pi/.node-red/node_modules/node-red-node-mysql/68-mysql.js
  • /usr/lib/node_modules/node-red/node_modules/@node-red/registry/lib/loader.js
  • /usr/lib/node_modules/node-red/node_modules/@node-red/registry/lib/index.js
  • /usr/lib/node_modules/node-red/node_modules/@node-red/runtime/lib/nodes/index.js
  • /usr/lib/node_modules/node-red/node_modules/@node-red/runtime/lib/index.js
  • /usr/lib/node_modules/node-red/lib/red.js
  • /usr/lib/node_modules/node-red/red.js
    18 Nov 11:38:59 - [warn] ------------------------------------------------------
    18 Nov 11:38:59 - [info] Settings file : /home/pi/.node-red/settings.js
    18 Nov 11:38:59 - [info] Context store : 'default' [module=memory]
    18 Nov 11:38:59 - [info] User directory : /home/pi/.node-red
    18 Nov 11:38:59 - [info] Projects directory: /home/pi/.node-red/projects
    18 Nov 11:38:59 - [info] Server now running at http://127.0.0.1:1880/
    18 Nov 11:38:59 - [info] Active project : Feb_2020
    18 Nov 11:38:59 - [info] Flows file : /home/pi/.node-red/projects/Feb_2020/flows_iot-super-server.json
    18 Nov 11:39:00 - [info] Waiting for missing types to be registered:
    18 Nov 11:39:00 - [info] - MySQLdatabase
    18 Nov 11:39:00 - [info] - mysql

This is what is in ...../node-red-node-mysql/
Screen Shot 11-18-21 at 11.44 AM

Hah... indeed... ok... re-published.

Upgrade MySQL - working like it suppose to.

Hi Dave,
Repeated the install - now working fine.
All the flows where I'm using MySQL seem to be working fine.
The issues I had in the past were that the connection got dropped after a certain period of time.
It will be interesting to see if that is fixed.
This is the workaround I've been using (all this year) to query a dB every 15-mins.

Upgrading from 0.3.0 to 1.0.0-beta-2 resolved a memory leak issue I had, but I got another issue - numbers stored in database as DECIMAL (x, y) are now returning in SQL query results as strings (see highlighted on picture below) and I was forced to add type conversion to my code.
изображение
I beleive data which are stored in database in numeric formats should be returned in SQL query results as numbers but not strings. Adding values without type conversion I was getting concatenated strings ("43.070237.640" for values highlighted on the picture above) instead of sum of numbers. Please fix.
Thanks.

If you follow the link in Dave's original post regarding incompatibilities with the new library, that is listed as one of them. The rationale behind JavaScript numbers do not have the same floating point precision.

But they do also list a config option to revert the behaviour - something we ought to do in the node to keep the behaviour consistent

Some feedback for you...
I have 20 or more instances of MySQL sending readings to a dB in London from my Wemos nodes.
Everything has been working fine for just over 36-hrs (so I'm very happy).

Ok. Thanks, but where should I add { decimalNumbers: true } ?

It is something we will have to add to the node's underlying code - it isn't a setting end users have any access to.

1 Like

I have now published beta-3 - again as node-red-node-mysql@next that has the decimalNumbers setting true.

1 Like

Looks better now:
изображение

Thanks a lot.

@dceejay Are there any plans on making this node dynamic?

Were say you pass in -

msg.payload.host = Host
msg.payload.port = Port
msg.payload.user =  User
msg.payload.password = Password
msg.payload.database = Database
msg.payload.timezone = Timezone
msg.payload.charset = Charset

H,

they can be passed in as environment variables - but not dynamically. Currently the idea is to leave the pool of connections there as long as possible for best performance. If we have to shut all the connections after each call just in case the next call is to a different server then having to re-authenticate for each call would really make performance drop. So no I don't have any plans to make it dynamic.

If someone wanted to create a PR that didn't drop performance then yes I'd be interested.

Connecting to database I'm getting a warning
Nov 22 19:22:43 ubuntu-nuc Node-RED[25253]: Ignoring invalid configuration option passed to Connection: timeout. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection
and it looks like I can't control passing of this parameter to mysql2.

I was getting another warning
Nov 21 10:45:51 ubuntu-nuc Node-RED[23231]: Ignoring invalid timezone passed to Connection: MSK. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection

but when I configured Timezone in Database properties as "+03:00" warning gone.

I think timezone can only be of the format +XX:YY (or -XX:YY) or "local" unless you have populated the tables - see MySQL :: MySQL 8.0 Reference Manual :: 5.1.15 MySQL Server Time Zone Support -

Note

Named time zones can be used only if the time zone information tables 
in the `mysql` database have been created and populated. 
Otherwise, use of a named time zone results in an error:

But indeed apparently mysql2 only handle the +/- format anyway... so yes this would be a breaking change we need to make clear, as they don't seem to want to fix it.

and yes both mysql and mysql2 migrated timeout to connectTimeout... will remove.

(edit) - Pushed a new version @next to npm

1 Like