Node-red-node-mysql 0.0.19 "ER_HANDSHAKE_ERROR: Bad handshake"

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

maybe one of these will give you a clue ?

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.

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

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

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

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.

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

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

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

grafik

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

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

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

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

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?

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.

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.

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.

@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

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

charset : "latin1_swedish_ci",
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:

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 :sleepy: :sleepy: :sleepy:

I have several (most) databases with latin1_swedish_ci
Screen Shot 2020-01-11 at 6.58.24 AM
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.

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

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

Thanks and all the best, Frank