# \[Feedback wanted\] Update to mysql node

**URL:** <https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936>\
**Category:** General\
**Created:** [18 November 2021 11:27 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936 "2021-11-18T11:27:01Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [18 November 2021 11:27 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/1 "2021-11-18T11:27:01Z")

</div>

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](https://github.com/node-red/node-red-nodes/issues/853) - 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](https://github.com/sidorares/node-mysql2/tree/master/documentation#known-incompatibilities-with-node-mysql) - so we would like feedback if they actually hit anyone, and how we can mitigate, before pushing this out more widely.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [18 November 2021 11:40 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/2 "2021-11-18T11:40:49Z")

</div>

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/](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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/3/e384fb7442ec36d0fab599394c2d904dbfbe615e.gif)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [18 November 2021 11:46 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/3 "2021-11-18T11:46:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [18 November 2021 11:50 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/4 "2021-11-18T11:50:27Z")

</div>

Upgrade MySQL - working like it suppose to.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [18 November 2021 11:54 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/5 "2021-11-18T11:54:33Z")

</div>

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.

 ![Screen Shot 11-18-21 at 11.53 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/5/8576d35835870a720d446bd52fc118a2f8891c1a.gif)

---

<div class="post-metadata">

**Author:** ![ls819011](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ls819011/32/51342_2.png) [@ls819011](https://discourse.nodered.org/u/ls819011)\
**Post date:** [20 November 2021 17:54 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/6 "2021-11-20T17:54:57Z")

</div>

Upgrading from 0.3.0 to 1.0.0-beta-2 resolved [a memory leak issue](https://discourse.nodered.org/t/node-red-node-mysql-0-3-0-is-causing-a-memory-leak/53999) 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.  
 ![изображение](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/a/1a1ed0d8bb4fb2991d06750aee806a711670ae88.png)  
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.

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [20 November 2021 17:59 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/7 "2021-11-20T17:59:41Z")

</div>

> [@ls819011](#):
>
> I got another issue - numbers stored in database as DECIMAL (x, y) are now returning in SQL query results as strings

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [20 November 2021 18:05 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/8 "2021-11-20T18:05:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ls819011](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ls819011/32/51342_2.png) [@ls819011](https://discourse.nodered.org/u/ls819011)\
**Post date:** [20 November 2021 19:01 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/9 "2021-11-20T19:01:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![knolleary](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/knolleary/32/3_2.png) [@knolleary](https://discourse.nodered.org/u/knolleary)\
**Post date:** [20 November 2021 19:10 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/10 "2021-11-20T19:10:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [20 November 2021 21:50 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/11 "2021-11-20T21:50:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ls819011](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ls819011/32/51342_2.png) [@ls819011](https://discourse.nodered.org/u/ls819011)\
**Post date:** [21 November 2021 07:52 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/12 "2021-11-21T07:52:29Z")

</div>

Looks better now:  
 ![изображение](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/5/45f67fe17e468798681abd317f37954d2a6610f0.png)

Thanks a lot.

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [22 November 2021 13:39 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/13 "2021-11-22T13:39:39Z")

</div>

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

Were say you pass in -

```auto
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

```

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 November 2021 15:39 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/14 "2021-11-22T15:39:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ls819011](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ls819011/32/51342_2.png) [@ls819011](https://discourse.nodered.org/u/ls819011)\
**Post date:** [22 November 2021 16:35 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/15 "2021-11-22T16:35:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [22 November 2021 17:21 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/16 "2021-11-22T17:21:46Z")

</div>

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](https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html) -

```auto
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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 January 2022 17:22 UTC](https://discourse.nodered.org/t/feedback-wanted-update-to-mysql-node/53936/17 "2022-01-21T17:22:43Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.