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.
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:
/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
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).
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.
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.
