# Node-red-node-mysql 0.0.19 "ER\_HANDSHAKE\_ERROR: Bad handshake"

**URL:** <https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098>\
**Category:** General\
**Created:** [9 January 2020 08:00 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098 "2020-01-09T08:00:45Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [9 January 2020 08:00 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/1 "2020-01-09T08:00:45Z")

</div>

Hi,  
the endpoint for connecting mysql (v5.x) produces this error: "Error: ER\_HANDSHAKE\_ERROR: Bad handshake". Login from the cli of this CentOS 7 Linux machine to the mysql server is working.

What can I do please?

Node Red v1.0.0  
node-red-node-mysql 0.0.19  
nodejs.x86\_64 2:10.18.0-1

Thanks, Frank

---

<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:** [9 January 2020 08:49 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/2 "2020-01-09T08:49:54Z")

</div>

maybe one of these will give you a clue ?

> <https://dba.stackexchange.com/questions/241660/mysql-bad-handshake-after-entering-password>

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [9 January 2020 09:02 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/3 "2020-01-09T09:02:28Z")

</div>

No, I can connect to the Mysql server from several devices and applications without any problem, also from the CLI where node red is installed. Only within the flow by using the connector I get the handshake error.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 January 2020 11:08 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/4 "2020-01-09T11:08:38Z")

</div>

What version of MySQL?  
Why are you on NR 1.0.0? The latest version is 1.0.3

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [9 January 2020 14:31 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/5 "2020-01-09T14:31:05Z")

</div>

MySQL Server 5.0.77

Node read not yet updatet because since this implementation of MySQL Access, the system is running stable without any issues. Node red now updated to 1.0.3 and problem is still present.

Thanks, Frank

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 January 2020 16:04 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/6 "2020-01-09T16:04:13Z")

</div>

> [@digidax](#):
>
> this implementation of MySQL Access,

Is this a Mysql server or a MySQL Access server? i.e. are you accessing MySQL directly or via an Access database.

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [10 January 2020 06:01 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/7 "2020-01-10T06:01:33Z")

</div>

It's a real server, directly connected to port 3066 TCP across the network.  
I mean that this node red flow uses influxdb for data storing and now for the first time a MySQL connection to a server. That's why nod red was not updated before.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [10 January 2020 10:05 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/8 "2020-01-10T10:05:15Z")

</div>

Have you check the MySQL server log to see if anything is showing up there?

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [10 January 2020 12:06 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/9 "2020-01-10T12:06:37Z")

</div>

Have checked the mysql server log files (serverlog and errorlog), no abnormal acivities seen.  
The only errors are displayed in node red flow:

```auto
10.1.2020, 13:05:51node: 108de519.5af1db
msg : error
"Error: ER_HANDSHAKE_ERROR: Bad handshake"

```

![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/c/5c9819a1b40cba0f74bb850b77c93203b5581bf2.png)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [10 January 2020 12:15 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/10 "2020-01-10T12:15:55Z")

</div>

You could edit settings.js and change the logging level. But I don't know if that will provide any more information.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [10 January 2020 12:21 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/11 "2020-01-10T12:21:45Z")

</div>

@dceejay Dave, is there a minimum level of MySQL that node-red-node-mysql supports?

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [10 January 2020 12:36 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/12 "2020-01-10T12:36:42Z")

</div>

changing loglevel from "info" to "debug" shows no more informations still than posted.

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [10 January 2020 12:44 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/13 "2020-01-10T12:44:40Z")

</div>

here is the TCP dump with the related MySQL Frames, captured on the server:

```auto
Frame 4: 128 bytes on wire (1024 bits), 128 bytes captured (1024 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 1, Ack: 1, Len: 60
MySQL Protocol
    Packet Length: 56
    Packet Number: 0
    Server Greeting
        Protocol: 10
        Version: 5.0.77-log
        Thread ID: 3555
        Salt: YB{gp"PO
        Server Capabilities: 0xa22c
        Server Language: latin1 COLLATE latin1_swedish_ci (8)
        Server Status: 0x0002
        Extended Server Capabilities: 0x0000
        Authentication Plugin Length: 0
        Unused: 00000000000000000000
        Salt: "}F$!wW/$cGxI

Frame 6: 146 bytes on wire (1168 bits), 146 bytes captured (1168 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 51504, Dst Port: 3306, Seq: 1, Ack: 61, Len: 78
MySQL Protocol
    Packet Length: 74
    Packet Number: 1
    Login Request
        Client Capabilities: 0xf3cf
        Extended Client Capabilities: 0x0007
        MAX Packet: 0
        Charset: utf8 COLLATE utf8_general_ci (33)
        Username: xxxxxxxxxxxx
        Password: xxxxxxxxxxxxxxxx
        Schema: test

Frame 8: 73 bytes on wire (584 bits), 73 bytes captured (584 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 61, Ack: 79, Len: 5
MySQL Protocol
    Packet Length: 1
    Packet Number: 2
    EOF marker: 254
        [Expert Info (Warning/Protocol): EOF Marker found while connection in wrong state.]
            [EOF Marker found while connection in wrong state.]
            [Severity level: Warning]
            [Group: Protocol]

Frame 9: 80 bytes on wire (640 bits), 80 bytes captured (640 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 51504, Dst Port: 3306, Seq: 79, Ack: 66, Len: 12
MySQL Protocol
    Packet Length: 8
    Packet Number: 3
    Request Command Unknown (67)
        Command: Unknown (67)
        Payload: 4e5a5355474f4b
            [Expert Info (Warning/Protocol): Unknown/invalid command code]
                [Unknown/invalid command code]
                [Severity level: Warning]
                [Group: Protocol]

Frame 10: 94 bytes on wire (752 bits), 94 bytes captured (752 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.188, Dst: 192.168.130.172
Transmission Control Protocol, Src Port: 3306, Dst Port: 51504, Seq: 66, Ack: 91, Len: 26
MySQL Protocol
    Packet Length: 22
    Packet Number: 4
    Error Code: 1043
    SQL state: 08S01
    Error message: Bad handshake

```

Server Language is **latin1 COLLATE latin1\_swedish\_ci (8)**  
Client Charset during login: **Charset: utf8 COLLATE utf8\_general\_ci (33)**

Can this be the problem?

---

<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:** [10 January 2020 13:40 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/14 "2020-01-10T13:40:33Z")

</div>

no idea - we rely on the node mysql library (currently we point at ^2.17.1) so whatever that supports or doesn't support. I know there is/was a problem with super new mysql servers that had to be set to use "old authentication" methods as the library didn't handle latest - but I don't recall this being the error that produced.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [10 January 2020 16:41 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/15 "2020-01-10T16:41:26Z")

</div>

> [@digidax](#):
>
> Can this be the problem?

could be, only way I can think of testing it would be for you to create another DB with `utf8 COLLATE utf8_general_ci (33)` collation and test against it.

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [11 January 2020 11:14 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/16 "2020-01-11T11:14:50Z")

</div>

Have test it but the server deilver on initial connection " Server Language is **latin1 COLLATE latin1\_swedish\_ci (8)**" so I want not to change the connection settings because a lot of other applications are connecting to it. The charset of the database is in this moment / status not important.

I will connect the maintainer of the underlayed mysql library. Thanks for any help.

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [11 January 2020 11:28 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/17 "2020-01-11T11:28:08Z")

</div>

@dceejay: can you please add an additional connection option: "charset" in node's config tab:  
[https://www.npmjs.com/package/mysql/v/2.17.1](https://www.npmjs.com/package/mysql/v/2.17.1)

 ![grafik](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/f/1f761011de161708d7a3bf7ab2cf81724e888226.png)

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [11 January 2020 12:15 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/18 "2020-01-11T12:15:22Z")

</div>

Ok, some news: I have add in "node-red-node-mysql" after line 41:

```auto
charset : "latin1_swedish_ci",

```

```auto
function doConnect() {
            node.connecting = true;
            node.emit("state","connecting");
            if (!node.pool) {
                node.pool = mysqldb.createPool({
                    host : node.host,
                    port : node.port,
                    user : node.credentials.user,
                    password : node.credentials.password,
                    database : node.dbname,
                    timezone : node.tz,
                    insecureAuth: true,
                    charset : "latin1_swedish_ci",
                    multipleStatements: true,
                    connectionLimit: 25
                });
            }

```

Now the TCP dump shows:

```auto
Frame 6: 146 bytes on wire (1168 bits), 146 bytes captured (1168 bits)
Linux cooked capture
Internet Protocol Version 4, Src: 192.168.130.172, Dst: 192.168.130.188
Transmission Control Protocol, Src Port: 40412, Dst Port: 3306, Seq: 1, Ack: 61, Len: 78
MySQL Protocol
    Packet Length: 74
    Packet Number: 1
    Login Request
        Client Capabilities: 0xf3cf
        Extended Client Capabilities: 0x0007
        MAX Packet: 0
        Charset: latin1 COLLATE latin1_swedish_ci (8)
        Username: xxxxxxxxxxxx
        Password: xxxxxxxxxx
        Schema: celebrate1

```

But the "Bad Handshake Error" still occurs 😪 😪 😪

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [11 January 2020 12:24 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/19 "2020-01-11T12:24:45Z")

</div>

I have several (most) databases with **latin1\_swedish\_ci**  
 ![Screen Shot 2020-01-11 at 6.58.24 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/f/df9e039be477be875f71024562c0fc43ade375a0.png)  
and they all work fine. I've tested against both a local (on the same Mac running NR: Server version: 5.6.19 - MySQL Community Server) and a remote DB (running on my host: Server version: 10.2.30-MariaDB-log - MariaDB Server) and they run fine.

---

<div class="post-metadata">

**Author:** ![digidax](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@digidax](https://discourse.nodered.org/u/digidax)\
**Post date:** [11 January 2020 12:24 UTC](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098/20 "2020-01-11T12:24:48Z")

</div>

I can not send an issue on github or google groups for the

> **[mysql](https://www.npmjs.com/package/mysql/v/2.17.1)**
>
> A node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed.

Has anyone here the possibility to point the developer of the mysql lib here to this thread please?

Thanks and all the best, Frank

[Next page](https://discourse.nodered.org/t/node-red-node-mysql-0-0-19-er-handshake-error-bad-handshake/20098.md?page=2)